Need help designing query to sum data from 2 tables grouped by 2 f

[Marty]

I'm using Acess 2003. I have 2 tables (for let's say Wigets A and Wigets B)
that contain sales detail by Mo/Yr and Seller. I'm trying to design a query
that will tell me the total number of wigets (A & B) that each seller sold in
each Mo/Yr. So far my efforts have not given me the desired results. How can
I do this? Thanks, example below...

Example:

Table 1 (Wigets A)
Mo/Yr Seller #
1/08 Bob 1
1/08 Bob 1
2/08 Bob 1
2/08 Joe 1

Table 2 (Widgets B)
Mo/Yr Seller #
1/08 Bob 15
1/08 Joe 20
2/08 Joe 15

Desired query results
Mo/Yr Seller #
1/08 Bob 17
1/08 Joe 20
2/08 Bob 1
2/08 Joe 16

 

[orange]

I'm using Acess 2003. I have 2 tables (for let's say Wigets A and Wigets B)
that contain sales detail by Mo/Yr and Seller. I'm trying to design a query
that will tell me the total number of wigets (A & B) that each seller sold in
each Mo/Yr. So far my efforts have not given me the desired results. How can
I do this? Thanks, example below...

Example:

Table 1 (Wigets A)
Mo/Yr Seller #
1/08 Bob 1
1/08 Bob 1
2/08 Bob 1
2/08 Joe 1

Table 2 (Widgets B)
Mo/Yr Seller #
1/08 Bob 15
1/08 Joe 20
2/08 Joe 15

Desired query results
Mo/Yr Seller #
1/08 Bob 17
1/08 Joe 20
2/08 Bob 1
2/08 Joe 16

I made a database based on your data. My tables are widgetA and widgetB
Columns in each table are:
moyr
seller
numberSold

Here is SQL query that answers you request.

SELECT X.moyr, X.seller, Sum(X.numberSold) AS SumOfnumberSold
 FROM [select moyr,seller,numberSold from widgetA
 union all
  select moyr,seller,numberSold from widgetb
 ]. AS X
 GROUP BY X.moyr, X.seller;

 

[John W. Vinson]

I'm using Acess 2003. I have 2 tables (for let's say Wigets A and Wigets B)
that contain sales detail by Mo/Yr and Seller.

Then your database is designed incorrectly. It makes sense in a spreadsheet to
have a workbook with a page for widgets A and widgets B, but NOT in a
relational database! Instead use *ONE* table with an additional field
indicating what's being sold.

I'm trying to design a query
that will tell me the total number of wigets (A & B) that each seller sold in
each Mo/Yr. So far my efforts have not given me the desired results. How can
I do this? Thanks, example below...

With your current design you will need two queries: a UNION query combining
the two tables, and a Totals query based on that Union query.

SELECT [Mo/Yr], Seller, [#] FROM WidgetsA
UNION ALL
SELECT [Mo/Yr], Seller, [#] FROM WidgetsB;

Save this query as uniAllSales and then base a Totals query on it.

Note that using special characters such as / or # in fieldnames is a Bad Idea;
Access can and will get confused.

 

[Marty]

Thank you so much for the solution; it works great. I thought I needed a
UNION ALL query but I couldn't figure every thing out from the Help file
instructions.

As for the database design, a single table would be nice but it's not that
easy. I simplified my request. The actual tables are custom designed for
their separate divisions and the people who enter the data. Both tables have
a lot of stuff defaulted to reduce errors and they both also have autonum
fields. I wanted to pull limited similar data from both because a very small
number of "sellers" appear in both tables. I had been exporting the data to
Excel and combining the data there for a small report each month. I knew
there had to be a better way and you provided it. Thanks so much!

I'm using Acess 2003. I have 2 tables (for let's say Wigets A and Wigets B)
that contain sales detail by Mo/Yr and Seller.

Then your database is designed incorrectly. It makes sense in a spreadsheet to
have a workbook with a page for widgets A and widgets B, but NOT in a
relational database! Instead use *ONE* table with an additional field
indicating what's being sold.

I'm trying to design a query
that will tell me the total number of wigets (A & B) that each seller sold in
each Mo/Yr. So far my efforts have not given me the desired results. How can
I do this? Thanks, example below...

With your current design you will need two queries: a UNION query combining
the two tables, and a Totals query based on that Union query.

SELECT [Mo/Yr], Seller, [#] FROM WidgetsA
UNION ALL
SELECT [Mo/Yr], Seller, [#] FROM WidgetsB;

Save this query as uniAllSales and then base a Totals query on it.

Note that using special characters such as / or # in fieldnames is a Bad Idea;
Access can and will get confused.