join

[kbreiss]

I have a table that has all the facility IDs available.
I also have a table that has the number of license issued for each facility.

I would like to write a query that lists ALL of the facility IDs. and have the number of license issued for each facility. If a particular facility did not issue any I would like for a zero to be placed in that field.

Anyone have any ideas?

Kacy
________
COACH HANDBAGS

 

[llkhoutx]

Build the following query in the SQL view of the QBE frame and save it (as Query1):

Select FacilityID, 0 from tbFacilities
UNION
Select FacilityID, NumLicenses from tbLicenses;

The first part of the union gives all FacilityID with a zero count for each, the second all the license counts.

Then query "Query1," grouping on FacilityID, and summing on the 2nd field, number of licenses, sorihng on FacilityID.

FacilityID's with 0 counts, will show 0, your desired result.

Without the UNION query you'd only get counts greater than zero.

Hence, one solution is two queries.

 

[kbreiss]

Works just the way I wanted it. I can put in my date criteria and it gives me the count for ALL facilities for that particular month.

Thanks again.

Kacy
________
Suzuki sv1000 specifications

 

[llkhoutx]

Where there's a will, there's a way to get into it.