Stuck on how to create a Union query that groups from two different tables

[gmcconville]

Good afternoon,



It has been about 25 years since I last used databases so I am a little rusty.

I have the following query...

SELECT Invoice, SalesID, First(Customer) AS Cust, First(BookoutDate) AS BODt, Sum(Quantity) AS Qty, Sum(Cost) AS Cst, Sum(SellPrice) AS Sell, Sum(GrossProfit) AS GP
FROM Sales082017
WHERE Invoice <> "0"
GROUP BY Invoice, SalesID


UNION SELECT Invoice, SalesID, First(Customer) AS Cust, First(BookoutDate) AS BODt, Sum(Quantity) AS Qty, Sum(Cost) AS Cst, Sum(SellPrice) AS Sell, Sum(GrossProfit) AS GP
FROM Sales092017
WHERE Invoice <> "0"
GROUP BY Invoice, SalesID;

Which doesn't quite do what I want it to.

It groups everything together, but only per month

EG: I might have the following lines that were meant to be grouped together, but because they are from different tables, they are seperate

Invoice	SalesID	Cust	BODt	Qty	Cst	Sell	GP
1629418	6316	Sample Customer	16/08/2017	5	$225.78	$208.70	-17.08
1629418	6316	Sample Customer	1/09/2017	2	-$84.74	-$0.01	84.73

Any assistance would be great thanks

[June7]

Why do you have two tables with identical structure to begin with?

Do the UNION and then do another query to do the aggregation. Or build a report using the UNION query as RecordSource and use report Sorting & Grouping features with aggregate calcs in textboxes.

You probably should use UNION ALL.

[ArviLaanemets]

You really have a table for every month???!!! How many of them?

The best solution will be you create a new empty table and insert all your monthly tables into it. After that your query will be an easy thing.