Using a Condition from one table in a UNION SELECT query

[CJ_London]

it does.

However still suggest you make a temporary table first and check performance and results - reason is you may find that the single daily sales table needs to be modified further, so get this right first.

Sort of things that will make a difference is to store numbers rather than text so

Month |Account |ISBN |Sales
01/01/2014|RetailerX|12345ABCDE|5

RetailerX should be their numeric unique ID
ISBN should be numeric anyway if it is a book code

Things like titles and authors should be in a separate tables anyway

Another way to improve performance is to limit the data in some way. So for example you may have a form where you can select an author or book classification and then display the results in a subform - but rather than using a filter on the subform, modify the recordsource in VBA

SELECT DailySales.SalesMonth, DailySales.SaleDate, DailySales.Vendor, DailySales.Account, DailySales.ISBN, DailySales.Units, DailySales.Revenue, (SELECT sum(Revenue) FROM DailySales as tmp WHERE Salesmonth=DailySales.SalesMonth and Vendor=Dailysales.Vendor and Account=DailySales.Account and ISBN=DailySales.ISBN) AS DailySum, (SELECT first(Revenue) FROM MonthlySales as tmp WHERE Salesmonth=DailySales.SalesMonth and Vendor=Dailysales.Vendor and Account=DailySales.Account and ISBN=DailySales.ISBN) AS Monthly
FROM DailySales
[COLOR=red]WHERE ISBN=12345[/COLOR]
[COLOR=black]UNION SELECT ...[/COLOR]
[COLOR=black]...[/COLOR]
[COLOR=#ff0000][COLOR=red]WHERE ISBN=12345[/COLOR]
[/COLOR]

This will save having to load a million rows initially

 

[Acc]

Hi,

just wanted to say thanks again you have been a great help. I currently have a basic mock up working with some limited data and am trying to implement your suggestions to make it work better before feeding in all the data.

Titles and authors are already stored in a separate table, I am currently making all fields into numerics as per your suggestion.