comparing last year's results

[Claire]

I have a nice long table that lists all of our leads over the past 3+ years
along with the department, date opened, and date approved (when it gets
turned into a job). My goal is to create a query/report that will compare
each week's leads and jobs over the past three years. Something like:

Week Dept 2006 2007 2008
1 A 1 3 2
1 B 3 1 0
2 A 2 2 2
2 B 1 4 3

I have created three separate queries that group by the week of the year and
the department and count the number of leads and the number of jobs, one
query for each year.

Where I'm stuck is getting these together into one query. I can create
dummy fields for the other years and union them together, but I would like to
see the results next to each other, not on a separate line. It shows:

Week Dept 2006 2007 2008
1 A 1
1 A 3
1 A 2

instead of listing the corresponding week and department (ie 1 A)
information on one line.

Thanks for your help!
~Claire

 

[KARL DEWEY]

I have created three separate queries ... one query for each year.
Use only one query with a calculated field like this for each year --
2006: IIF(..your 2006 date criteria.. , [ValueField], 0)

If can not follw this then post your SQL for someone to edit.

 

[Claire]

Thank you so much! I used the Design view to generate the SQL, and for some
reason was not able to contemplate putting an if statement in the expression.
That's just perfect.
~Claire

Use only one query with a calculated field like this for each year --
2006: IIF(..your 2006 date criteria.. , [ValueField], 0)

If can not follw this then post your SQL for someone to edit.
--
KARL DEWEY
Build a little - Test a little

I have a nice long table that lists all of our leads over the past 3+ years
along with the department, date opened, and date approved (when it gets
turned into a job). My goal is to create a query/report that will compare
each week's leads and jobs over the past three years. Something like:

Week Dept 2006 2007 2008
1 A 1 3 2
1 B 3 1 0
2 A 2 2 2
2 B 1 4 3

I have created three separate queries that group by the week of the year and
the department and count the number of leads and the number of jobs, one
query for each year.

Where I'm stuck is getting these together into one query. I can create
dummy fields for the other years and union them together, but I would like to
see the results next to each other, not on a separate line. It shows:

Week Dept 2006 2007 2008
1 A 1
1 A 3
1 A 2

instead of listing the corresponding week and department (ie 1 A)
information on one line.

Thanks for your help!
~Claire