Whoops! You'll need to change that query slightly to pull your count out...
First line should be:
Select count(t.id) as FishCount,...
And if you have a *lot* of fish in an MS Access db, you'll probably want to change the
inner join syntax to increase performance (I did it the lazy way <grin>).
----- Original Message -----
From: Dina Hess
To: CF-Talk
Sent: Sunday, March 31, 2002 1:53 PM
Subject: Re: Help With Query
Frank,
Try this:
SELECT count(t.id), f.CommonName, s.FishSubCategory
FROM FishDB AS f, FishInv AS t, FishSubCategory AS s
WHERE f.id=t.id And f.id = s.id AND t.TankID=2
GROUP BY f.CommonName, s.FishSubCategory;
Dina
----- Original Message -----
From: Frank Mamone
To: CF-Talk
Sent: Sunday, March 31, 2002 12:39 PM
Subject: Re: Help With Query
OK. Here's an example:
FishDB Id CategoryID SubCategoryID SciName CommonName Comment
52 1 1 Anomalochromis thomasi African Butterfly Cichlid
Id CategoryID SubCategoryID SciName CommonName Comment
25 1 1 Teleogramma brichardi Brichard's Slender Cichlid
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
FishCategory Id Category Comments
1 Freshwater
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
FishSubCategory Id FishSubCategory Category Comment
1 Cichlids 1 African & American
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
FishInv Id TankId FishId
20 2 52
21 2 52
22 2 52
23 2 25
Given only the Tank ID I want:
3 African Butterfly Cichlid
1 Brichardis Slender Cichlid
Thanks
----- Original Message -----
From: "Dina Hess" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Sunday, March 31, 2002 1:25 PM
Subject: Re: Help With Query
> Frank,
>
> Illuminate me. Just out of curiousity, what type of data might you store
in the main and sub fish categories?
>
> Dina
> ----- Original Message -----
> From: Frank Mamone
> To: CF-Talk
> Sent: Sunday, March 31, 2002 12:17 PM
> Subject: Re: Help With Query
>
>
> 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
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
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
