Getting 2 counts() of 2 foreign keys

Hi Jim

Create 3 queries, the first 2 create the count for the tblAreas and the
tblRates and the third query joins the first 2.

qryAreaCount
SELECT tblDest.DestID, tblDest.Destination, Count(tblArea.Area) AS AreaCount
FROM tblDest LEFT JOIN tblArea ON tblDest.DestID = tblArea.DestID
GROUP BY tblDest.DestID, tblDest.Destination;

qryRateCount
SELECT tblDest.DestID, tblDest.Destination, Count(tblRate.Rate) AS RateCount
FROM tblDest LEFT JOIN tblRate ON tblDest.DestID = tblRate.DestID
GROUP BY tblDest.DestID, tblDest.Destination;

Join the first 2 queries to the tblDest to create the third query
qryAreaAndRateCount
SELECT tblDest.Destination, qryAreaCount.AreaCount, qryRateCount.RateCount
FROM (tblDest INNER JOIN qryAreaCount ON tblDest.DestID =
qryAreaCount.DestID) INNER JOIN qryRateCount ON tblDest.DestID =
qryRateCount.DestID;

Stewart


"JimJim" <omg@wired.ie> wrote in message
news:da6f2902.0406200118.173a4685@posting.google.c om...[color=blue]
> Wondering if someone here could help me out, I've tried a number of
> ways to do this, and while I have managed to get it to work, Im SURE
> im not doing the optimal way, and it seems to be causing problems with
> my form which is throwing up '#Name? ' in some fields(at seemingly
> random times...)
>
> Basicly I have 3 tables, one being the 'top' table, with only 2
> fields, a primary key (ID) and a second field Name, this table is
> called 'tblDests'
>
> The two other tables are called tblAreas and tblRates, and both have a
> field called DestID which is defined as a foreign key to the tblDests
> ID field. Both tables have a many to one relationship with the
> tblDests table (ie multiple entrys in the two 'child' tables)
>
> What I want to do is get a result set with the total number of records
> in each child tables grouped by Name from the tblDests table, so my
> result set would look like:
>
> tblDests.Name | CountOfAreas | CountOfRates
> --------------------------------------------
> India | 20 | 13
> England | 12 | 13
>
> The way I'm doing it now involes first a Union query like
> SELECT Name,Area, <fake rates> .... UNION SELECT Name ,<fake area>,
> Rates
> Then i do a totals query on top of that and get the First() from Area
> and Last() from Rates. It works how i want, but like I said, there
> surely must be a better way to do this??
>
> Its worth noting that both child tables have no 'ID' field, their
> primary key is a multiple field index so I use 'destid' in both cases
> (the foreign key)
>
> Thanks for any help
> Jim[/color]

Thanks very much that works perfectly

"Stewart Allen" <sagasu@NOT.wave.THIS.co.nz> wrote in message news:<cb3r7f$u53$1@news.wave.co.nz>...[color=blue]
> Hi Jim
>
> Create 3 queries, the first 2 create the count for the tblAreas and the
> tblRates and the third query joins the first 2.
>[/color]