QBE Count across two "drill-down" levels

[Kenny]

Hello,

I have 3 tables in my Coin database:-

1] tblRegion
pkRegion - text, primary key

Values include "Europe", "Caribbean", "Oceania", etc.

2] tblcountry
pkCountry - text, primary key
fkRegion - foreign key to tblRegion
Values include "Bermuda," "Tuvalu", etc - lots of country names

3] tblCoin

pkCoin - autonumber, primary key
fkCountry - foreign key to tblCountry
Year - e.g. 1977
Denomination - e.g. "10 cents"


I want to do the two following count variations:-

1] Group by Region, display number of countries per region AND number of
coins for entire region

2] As above, but include any region for which I have NO coins

Can I do this in QBE? Or will I have to get into SQL to do this?

Currently I can do EITHER: group by pkRegion, count pkCountry -
OR: group by pkCountry, count pkCoin -
BUT - I don't know how to extend this to achieve 1] and/or 2] above.

Can you help?

Regards,

Kenneth Ndogu.

 

[Steve Schapel]

Kenneth,

Yes, you can do this in the Query Design view. But not in one step.
You will need to make and save the two queries for countries per region
and coins per country, and then create a third query which includes both
of the original queries. You will need to use a Left Join between them,
on the region field, in order to include coin-less regions.

 

[Kenny]

Thanks, Steve - it did the trick!

Regards,

Kenny.

Kenneth,

Yes, you can do this in the Query Design view. But not in one step.
You will need to make and save the two queries for countries per region
and coins per country, and then create a third query which includes both
of the original queries. You will need to use a Left Join between them,
on the region field, in order to include coin-less regions.

--
Steve Schapel, Microsoft Access MVP

Hello,

I have 3 tables in my Coin database:-

1] tblRegion
pkRegion - text, primary key

Values include "Europe", "Caribbean", "Oceania", etc.

2] tblcountry
pkCountry - text, primary key
fkRegion - foreign key to tblRegion
Values include "Bermuda," "Tuvalu", etc - lots of country names

3] tblCoin

pkCoin - autonumber, primary key
fkCountry - foreign key to tblCountry
Year - e.g. 1977
Denomination - e.g. "10 cents"


I want to do the two following count variations:-

1] Group by Region, display number of countries per region AND number of
coins for entire region

2] As above, but include any region for which I have NO coins

Can I do this in QBE? Or will I have to get into SQL to do this?

Currently I can do EITHER: group by pkRegion, count pkCountry -
OR: group by pkCountry, count pkCoin -
BUT - I don't know how to extend this to achieve 1] and/or 2] above.

Can you help?

Regards,

Kenneth Ndogu.