How to Design Comparisons for Reporting

[victory92]

I am trying to design a process for creating comparison reports. This would allow the user to ask for any range of dates -- to be compared to another range of dates. For example, they may want to compare 2nd qtr (07/02 - 09/02) to 3rd qtr 2002 (10/02 - 12/02) Or they may select only a month - such as comparing 12-02 to 1-03.

I have 2 parameter queries that prompt for beginning/ending dates. The first one will extract data based on the first range of dates given - and likewise with the second.

Now comes my problem. I have no way to join this data. I was anticipating a report such as the one below - as the cross tab does not allow for as many months as may be requested... For the report - the first range was 7/02 - 9/02 and the second range was 10/02-12/02. The data is on 2 tables...

REP MO/YR SALES COMM MO/YR SALES COMM SALES %

BOB 07/02 $1000 $50 10/02 $1200 $60
08/02 0 0 11/02 $1100 55
09/02 $2000 $100 12/02 0 0
TOTAL $3000 $150 $2300 $115 -23.33%



Can anyone give me some pointers on how to do this? Or can you give me an alternative way??

I'm a newbie on this --

Thanks!!!!!!!!!

 

[PaulBricker]

Well, here is one way to approach it. You could get all your data into one query using a Union Query. A shortened SQL statement would be
Select Table1.Rep, Table1.Mo/Yr, Table1.Sales,"Group1" as MyGroup
From Table1
Where Table1.Mo/Yr Between [Enter sDate] And [Enter eDate]
Union Select Table2.Rep, Table2.Mo/Yr, Table2.Sales,"Group2" as MyGroup
From Table2
Where Table2.Mo/Yr Between [Enter StartDate] And [Enter EndDate]

I added Group1 and Group2 to differenciate between the two date intervals. Note that the syntax for the Parameters is different. This should put your info into a single query. Then you should be able to build a Report that displays the info the way you want and does the calculations. I tested the Union query and that seems to work fine with a short date parameter (not sure about the Mo/Yr format). Post back with specific questions if you go this route.

Paul

 

[victory92]

Thank you for your suggestion. I never used a union query before - and tried it. Unfortunately, I doesn't satisfy what I need for reporting...unless there are ways for one report to have 2 input files.

I am trying to build a utility type process so that the users can ask for any range of dates for comparison. I have designed the process whereby the user gives the first range of dates and that data is captured on one file. Then the second process does the same thing - but captures data for the second range on a different file.

Now I want to join those two files - where the first record on the first file is joined with the first record on the second file.

I have no keys in order to join this way. If I could somehow get the record number of the file, I could do the join.

I could define 2 tables with an autonumber and append each file into them - but I am leary that the numbers could get out of sync and there would be no way for me to control them.

I'll try to illustrate - The user wants to compare the rep's first quarter vs the second quarter sales & commission. The process that I have working will put the first quarter in the first file and the second quarter in the second file thru a series of queries. The user enters that beginning month/year and ending month/year. So this process will work for a comparison of any months, quarters, year or rolling 12-months.

Using the example of 1st vs 2nd qtr, Now I want to join so that I have a report that looks like the following:

FIRST RANGE------- SECOND RANGE------ COMPARISON
REP MO/YR SALES COMM MO/YR SALES COMM SALES %

LEANN 01/02 $2000 $100 04/02 $1200 $60 -40%
----- 02/02 0 0 05/02 $1100 55 100%
----- 03/02 $2000 $100 06/02 0 0 -100% TOTAL $4000 $200 $2300 $115 -42.5%


Unfortunately, when I preview this, it is not showing with aligned columns which helps. I hope you can read it or format it.

I believe I have to join the record containing 01/02 data with record containing 04/02 data. Record number would do it...But is there a better way?

Thanks. I hope this is clear.

 

[PaulBricker]

If I had the data in front of me I might be able to work with it to get the rows lined up the way you need but beyond that, I would say using the record number is a reasonably good method. I appreciate the concern about getting the numbers out of sync and it is something you would have to be careful of. If you want to send me a small copy of the data, I'd be happy to look it over. There are ways to get values from one record to line up horizontally with values from another record so calculations can be performed. My email is pbricker@williston.com

Paul