Need help with queries that span tables. (1 Viewer)

sumdumgai

Registered User.
Local time
Today, 06:17
Joined
Jul 19, 2007
Messages
453
I'm new to 'union' queries and need some help, please, with creating queries that span tables.


I have six tables. The first four, call them 'Sales_Master', have identical structure with these fields: Sales-ID, Product_ID, Quantity, and MMYYYY. Each of these tables represents a month's worth of sales transactions for each Sales_ID (e.g., s100, p123, 10, 022019). The fifth table is 'Product_Master' and contains Product_ID, Product_Name (e.g., p123, pname123). The sixth table is 'Person_Master' and contains Sales_ID, Sales_Name (e.g., s100, sname100).

Is it possible for an Access query to answer the following question: What is the average quantity sold for each sales person for each product over last three months, assuming sales data is available for months 202019, 012019, and 122018?

I have other similar questions that span the sales monthly tables, and I hope any help I get here will get me started on Access 'union' type queries. Thanks.
 

plog

Banishment Pending
Local time
Today, 05:17
Joined
May 11, 2011
Messages
11,611
Sales_Master', have identical structure

Why? This isn't the proper way to house data. Why isn't all the Sales_Master data housed together properly?

I don't understand your example's in parenthesis. Are those row names or values in a table? Can you provide a sample database? or at least a breakdown of each tables structure? You can just post it in here like this:

TableNameHere
Field1NameHere, Field2NameHere, Field3NameHere, ...
1, Saly, 100, 1/1/2019, ...
2, Dave, 23, 2/1/2019, ...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:17
Joined
May 21, 2018
Messages
8,463
You can combine all six Sales_Master into a single query using a union query. From there you can make one single table, if necessary.
 

sumdumgai

Registered User.
Local time
Today, 06:17
Joined
Jul 19, 2007
Messages
453
Thanks plog. The sales transactions are imported monthly. To house them all in one appended table would exceed Access capacity. The example in parenthesis is a record example where 's100, p123, 10, 022019' would be a record with 's100' as Person_ID, p123 as Product_ID, 10 is quantity and 022019 is month year.


Sales_Master (for Feb 2019) ; e.g., personA sold 10 of itemA, personB sold 20 of itemA, and personC sold 5 of itemB.
Sales-ID, Product_ID, Quantity, and MMYYYY
1, s10, p100, 10, 022019
2, s11, p100, 20, 022019
3, s12, p101, 5, 022019

Sales_Master (for Jan 2019); e.g., personA sold 15 of itemA, personB sold 25 of itemB, and personC sold 3 of itemC.
Sales-ID, Product_ID, Quantity, and MMYYYY
1, s10, p100, 15, 012019
2, s11, p101, 25, 012019
3, s12, p102, 3, 012019

Product_Master
Product_ID, Product_Name
1, p100, itemA
2, p101, itemB
3, p102, itemC

Person_Master
Sales_ID, Sales_Name
1, s10, personA
2, s11, personB
3, s12, personC
 

sumdumgai

Registered User.
Local time
Today, 06:17
Joined
Jul 19, 2007
Messages
453
Thanks MajP. Sounds good. How do you do that? Can you please give an SQL example?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:17
Joined
May 21, 2018
Messages
8,463
Code:
https://www.w3schools.com/sql/sql_union.asp
If returning all fields then it is simply

Code:
SELECT * 
FROM tableOne
UNION
SELECT *
FROM tableTwo
 

plog

Banishment Pending
Local time
Today, 05:17
Joined
May 11, 2011
Messages
11,611
Follow MajP's example when building your UNION. The thing to remember is that the UNION should simply bring all your data together into one query object.

--Don't apply criteria to it
--Don't try and GROUP it
--Don't try and transform it in any way (change strings to dates, break out data from fields)
--Don't try and JOIN it to another table.

Keep the UNION simple and you won't go wrong. Save it (e.g. qry_Union), then if you need to do any of those things, make a new query based on the UNION query and do them in that new query built on the UNION.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:17
Joined
Feb 28, 2001
Messages
26,996
I recall this problem. You obviously chose to stay with an Access back-end. This problem with UNION queries will be the first of many problems associated with having very large data amounts to roll up.

Be aware that the larger (and more numerous) your tables get, the more complex this will become. However, using a UNION query like this CAN cross back-end files. If you have groups of identical tables with different names due to different date ranges, you can have them in separate files and still make a UNION query of them. That UNION query can then be the base of further SELECT queries. However, the resulting recordset is still limited to 1 GB total size. I.e. it is not only the tables that are limited in size. Since you know the structure of the tables, there ARE other ways to aggregate the data sets that would allow you to effectively exceed that 1 GB using a divide and conquer strategy. Ask if you need to know about that.

Be aware that it will take some "twiddling" of the UNION query to go multi-file since you would have to qualify the locations of the queries by opening the various database back ends before this query could span the multiple files. Also, you might have to play with some parameters such as the MAXLOCKSPERFILE parameter. Failure to do so will result in you getting a "resources exceeded" message or some other capacity-based error.

Good luck.
 
Last edited:

Users who are viewing this thread

Top Bottom