learning to count with a query?

[Guest]

How do I count the total number of occurences of a record across many tables?
I need to report on the number of products using a certain part number. This
would be easy if I knew how to use Access, VBA and SQL. I have not even
figured out how to get the query wizard to work on this problem. An example
follows:

BabyTable
ID PN
1 123
2 264
3 365

ChildTable
ID PN
2 264
3 365
4 442

TeenTable
ID PN
1 123
3 365
4 442

AdultTable
ID PN
2 264

SeniorTable
ID PN
1 123
2 264

I need to create a report based on a query that returns the following type
of information:

Part Number 264 is used in 4 different products.

Thank you for your help, time and experience!

 

[Guest]

You need to change your table structure to the following --
ID PN Use
1 123 Baby
2 264 Baby
3 365 Baby
2 264 Child
3 365 Child
4 442 Child
1 123 Teen
3 365 Teen
4 442 Teen
2 264 Adult
1 123 Senior
2 264 Senior

Use a union query to bring the data together.

SELECT ID, PN, "Baby" AS [USE]
FROM BabyTable
UNION ALL SELECT ID, PN, "Child" AS [USE]
FROM ChildTable
UNION ALL SELECT ID, PN, "Teen" AS [USE]
FROM TeenTable
UNION ALL SELECT ID, PN, "Adult" AS [USE]
FROM AdultTable
UNION ALL SELECT ID, PN, "Senior" AS [USE]
FROM SeniorTable

 

[Guest]

Hello Karl Dewey,

Thank you for your time and help! I will work on reorganizing the tables and
then I will apply your code. Afterwords, I will post my experience and let
you know how it went. Thanks Again!

Andy

You need to change your table structure to the following --
ID PN Use
1 123 Baby
2 264 Baby
3 365 Baby
2 264 Child
3 365 Child
4 442 Child
1 123 Teen
3 365 Teen
4 442 Teen
2 264 Adult
1 123 Senior
2 264 Senior

Use a union query to bring the data together.

SELECT ID, PN, "Baby" AS [USE]
FROM BabyTable
UNION ALL SELECT ID, PN, "Child" AS [USE]
FROM ChildTable
UNION ALL SELECT ID, PN, "Teen" AS [USE]
FROM TeenTable
UNION ALL SELECT ID, PN, "Adult" AS [USE]
FROM AdultTable
UNION ALL SELECT ID, PN, "Senior" AS [USE]
FROM SeniorTable

How do I count the total number of occurences of a record across many tables?
I need to report on the number of products using a certain part number. This
would be easy if I knew how to use Access, VBA and SQL. I have not even
figured out how to get the query wizard to work on this problem. An example
follows:

BabyTable
ID PN
1 123
2 264
3 365

ChildTable
ID PN
2 264
3 365
4 442

TeenTable
ID PN
1 123
3 365
4 442

AdultTable
ID PN
2 264

SeniorTable
ID PN
1 123
2 264

I need to create a report based on a query that returns the following type
of information:

Part Number 264 is used in 4 different products.

Thank you for your help, time and experience!