Querying A Field Twice...

[jesusoneez]

Hi all.

I have a table with a couple of years worth of figures and want to compare figures from 2001 and 2002 on a month by month basis. The query I have asks for start and end invoice dates in the format 20021001 (for October 1st 2002). The dates are in this format because our old mainframe stores them that way, and that's where the data comes from.

I need the comparisons on one report. For example;

Customer Name October 2001 October 2002
Company1 £18,000 £20,000
Company2 £25,000 £16,000

And so on...getting the data for October 2001 is no problem, but obviously I need to query the dates again to get October 2002...like I say, it needs to be on the same report. Any ideas on how to do this would be great (especially if I can just "click and drag" the problem away)! I started thinking along the lines of a make table query, and making two tables and creating the report from these...but I'm not sure how to go about it, or even if it's the best option.

Any help most appreciated.

Regards,

Steve

 

[Jack Cowley]

Create a new query and add the table in question twice. Join them on a common field, most likely the Primary key. Now you can compare dates from Oct. 2002 and Oct. 2001.

hth,
Jack

 

[jesusoneez]

Okay....

OK...I've created a new query and put the table in twice, and linked it with the Primary Key. I've also linked them both to a customers table (key is account number) and sales rep (key is rep number)...now I'm stuck again.

I can't figure out what to do from this point to get the data into a report. I query the Rep Number, the a start and end date from the first table and a start and end date from the second table. This returns no data, which isn't suprising me as how can you ask it to select two date ranges with a select query?

I'm using;

"Between [Start Date 1] And [End Date 1]" to select October 2001 and "Between [Start Date 2] And [End Date 2]" for selecting October 2002.

Super stumped...

 

[jesusoneez]

Okay....

EDIT
------

Sorry...posted twice!

 

[Jack Cowley]

Sorry but I seem to be leading you on a wild ride! Create a query to get the values for 2001 and use another query to get the values for 2002. Now create a query based on the two queries and base your report on that.... Hope I got it right this time!

Jack

 

[jesusoneez]

This has all been most helpfull...thanks all.

What I've ended up doing is basically what Jack suggested (two queries). These queries are MakeTable queries giving me the data I need to do the comparisons so my report is based on them.

I've just set up a button that runs the two queries (asking for dates), runs the reports, prints it, then deletes the two tables.

Pat, thanks for the info regarding dates...I didn't know you could change the format as it came into the database from the mainframe...this is certainly something I'll look at in the next build.

Thanks again.

Steve