I am trying to produce a report but struggling.
My database is essentially as follows.
Note: Tables are linked to Excel spreadsheets and can not be editted.
Table 1 = Code, Assets Amount, Assets Date
e.g.
ABC 100 01/02/2003
ABC 800 01/03/2003
ADB 900 01/02/2003
ADB 750 01/03/2003
Table 2 = Code, Sales, Sales Date
e.g.
ABC 2 01/01/2003 to 01/02/2003
ABC 3 01/02/2003 to 01/03/2003
ADB 4 01/01/2003 to 01/02/2003
ADB 8 01/02/2003 to 01/03/2003
Query = Code, Assets, Assets Date, Sales, Sales Date
e.g.
ABC 100 01/02/2003 2 01/01/2003 to 01/02/2003
ABC 800 01/03/2003 2 01/01/2003 to 01/02/2003
ABC 100 01/02/2003 3 01/02/2003 to 01/03/2003
ABC 800 01/03/2003 3 01/02/2003 to 01/03/2003
ADB 900 01/02/2003 4 01/01/2003 to 01/02/2003
ADB 750 01/03/2003 4 01/01/2003 to 01/02/2003
ADB 900 01/02/2003 8 01/02/2003 to 01/03/2003
ADB 750 01/03/2003 8 01/02/2003 to 01/03/2003
I now want to make a report that filters the Asset Date and Sales Date so the following data is generated.
ABC 100 01/02/2003 2 01/01/2003 to 01/02/2003
ABC 800 01/03/2003 3 01/02/2003 to 01/03/2003
ADB 900 01/02/2003 4 01/01/2003 to 01/02/2003
ADB 750 01/03/2003 8 01/02/2003 to 01/03/2003
Basically as you can see, I want the Asset Date to match the end of the Sales Date.
Is this possible?
Can I do this when generating a report? Or do I need to do this earlier, at query or table stage?
Thanks!