Access 97: How to write this query efficiently?
Posted on 2014-01-21
In Access 97:
I would like a report to show the first time a product has shipped into a new Country.
I have a table called 'InvoiceOrderEntry' with about 50,000 records.
The table has key fields like:
Of course, there are many different Invoices, Products and Countries.
For example, I want to see the first time a Red Pen has been shipped to Iceland.
Then I want to see the first time a Blue Pen has been shipped to Iceland.
Then I want to see the first time a Black Pen has been shipped to Canada.
As the years go by, more new Products will get shipped to new Countries.
I can code it a brute-force way whereby I run a query on all products shipped and save that to a temp Table. Then I run a second query to see if any new Countries have been shipped to and append the temp Table. And, so on and so forth.
That does not seem to be very efficient.
Is there an elegant way of doing this? I like the idea of saving the results to a temp Table so I can then run reports on that table.
I hope I have made myself clear here.
High points for a speedy solution!