Hi Dina, This is what I have:
1. FISHDB - Main fish DB. Each Fishs as a category, Subcategory and unique ID. I have Lookups for the Category and SubCategory. I think this aprt is fine. 2. TANKS - Stores Tanks by Owner. TankID is unique. I think this is OK. 3. FISHINV - Stroes 1 record for each fish added. Example if I add 3 Goldfish it will contain three records with essentially the same information.This may be bad. Now , I'd like to query FISHINV to output for example: 3 GoldFish 5 Yellows Labs 3 Cobalt Blues I should be able to remove any qty of a specific fish. Thanks for your answer. I'll take a closer look and see if it helps me. Frank ----- Original Message ----- From: "Dina Hess" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Sunday, March 31, 2002 12:57 PM Subject: Re: Help With Query > 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
