grouping and then comparing

[ytakbob]

Hello,
I have a table keyed by date ie 09/24/2003
so each row represents a day of acitivyt for doctors (the columns)

columns look like:
date dr.1 dr.2 dr.3
09/01/2002 2 1 0
09/02/2002 5 1 8
09/03/2002 0 1 0
09/01/2003 1 3 0
09/02/2003 3 0 1
09/03/2003 1 2 3

I want to creat a report that compares years of for each doctor. THe report should show something like:
Doctor 2002 2003 %increase/decrease
Dr.1 7 5 -29 %
Dr.2 3 5 +66 %
Dr.3 8 4 -50 %

Approach on the above ?

Bob


Bob Schmid
bob_schmid@hmis.org
330-746-1010 ext. 1347

 

[dhookom]

I would start by normalizing your table structure. You should not have Drs as field names. A better structure has a date field ("Date" is a poor name for a field), DoctorID, and a value (what you are storing).

Is it possible to change your structure? If not, you would start with a union query:
Select [Date], "Dr1" as Doctor, [Dr1] as TheValue
FROM tblYourTable
UNION ALL
Select [Date], "Dr2", [Dr2]
FROM tblYourTable
UNION ALL
Select [Date], "Dr3", [Dr3]
FROM tblYourTable
etc...

Then you can create a crosstab from the result of your union query that has Doctor as the Row Heading, "YR" & Year([TheValue]) as the Column Heading, and Sum(TheValue) as the Value.

Calculate the % in a report.

Duane
MS Access MVP