Getting 2 counts() of 2 foreign keys

[JimJim]

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

[Stewart Allen]

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.Destina tion, Count(tblArea.A rea) AS AreaCount
FROM tblDest LEFT JOIN tblArea ON tblDest.DestID = tblArea.DestID
GROUP BY tblDest.DestID, tblDest.Destina tion;

qryRateCount
SELECT tblDest.DestID, tblDest.Destina tion, Count(tblRate.R ate) AS RateCount
FROM tblDest LEFT JOIN tblRate ON tblDest.DestID = tblRate.DestID
GROUP BY tblDest.DestID, tblDest.Destina tion;

Join the first 2 queries to the tblDest to create the third query
qryAreaAndRateC ount
SELECT tblDest.Destina tion, qryAreaCount.Ar eaCount, qryRateCount.Ra teCount
FROM (tblDest INNER JOIN qryAreaCount ON tblDest.DestID =
qryAreaCount.De stID) INNER JOIN qryRateCount ON tblDest.DestID =
qryRateCount.De stID;

Stewart
"JimJim" <om*@wired.ie > wrote in message
news:da******** *************** ***@posting.goo gle.com...

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

[JimJim]

Thanks very much that works perfectly

"Stewart Allen" <sa****@NOT.wav e.THIS.co.nz> wrote in message news:<cb******* ***@news.wave.c o.nz>...

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.