Frank, This definitely sounds kinda fishy. ;)
So let's see...you've got FISH and you've got a TANK. Those would each be tables in your db. In the FISH table you could include a field called TYPE to designate "goldfish" or whatever. So a sample db design would be: FISH (FishID, FishType, FishComments, ...) TANK (TankID, TankOwner, FishID, ...) Then your query to produce the same type of fish in each tank would be something like this: SELECT f.type, f.comments, t.tankowner FROM fish f inner join tank t on f.fishid = t.fishid WHERE f.type = 'goldfish' and t.tankid = 2 To count all of the fish in a particular tank: SELECT count(*) t.fishid as fish_count FROM tank t WHERE t.tankid = 2 The inner join query assumes an MS Access db. In answer to your last question, you wouldn't want to store a calculated value in a db field. Best to use SQL to perform your calculations like SUM, COUNT, etc. Hope that helps. Dina ----- Original Message ----- From: Frank Mamone To: CF-Talk Sent: Sunday, March 31, 2002 11:15 AM Subject: Help With Query I have a fish databse. One table contains a Fish Database called FishDB. The other stores the fish per tank FishINV. So if I add three of the same type of fish I get three records in FISHINV records. I did it this way so I can add and remove fish and keep details about each fish. They may have the same ID but the comments about each one maybe different I am williing to remove this feature. My design may be fundamentally wrong but what I'm trying to do is query the FISHINV and get a count of each type. This is the basic query I use: SELECT A.ID, B.CommonName FROM FISHINV AS A, FISHDB AS B WHERE TankId = 2 AND (A.FishID = B.ID); What can I add to count individual fish? Would it be better to keep a running count in a field instead ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
