Setting Up Report

[JS]

I am having a difficult time figuring this one out!

I have a query that is pulling the fields that I need in a report.
The fields are:

BA Complexity Code NumberOfWorkDaysToCode/Key

ES MCK 7
ES LCK 1
ES HCK 7
ES HCK 7

I need my report to look something like this:

Number of Days
To Code/Key HCK LCK MCK

1 Day 1
2 Days
3 Days
4 Days
5 Days
6 Days
7 Days 2
1
etc.
31 Days

For Example From the above query - 7 Days would = 2 at HCK for the Benefit
Administrator. Am I forced to use Excel?

Any suggestions would be helpful! I can't figure this one out.

JS

 

[Marshall Barton]

I am having a difficult time figuring this one out!

I have a query that is pulling the fields that I need in a report.
The fields are:

BA Complexity Code NumberOfWorkDaysToCode/Key

ES MCK 7
ES LCK 1
ES HCK 7
ES HCK 7

I need my report to look something like this:

Number of Days
To Code/Key HCK LCK MCK

1 Day 1
2 Days
3 Days
4 Days
5 Days
6 Days
7 Days 2
1
etc.
31 Days

For Example From the above query - 7 Days would = 2 at HCK for the Benefit
Administrator. Am I forced to use Excel?

You will need a table (named Numbers) with one field (named
Num) populated witht he numbers 1 through at least 31.

Then add that table to your query and join them on the
NumberOfWorkDaysToCode/Key field

SELECT Complexity, [NumberOfWorkDaysToCode/Key], Num
FROM Numbers LEFT JOIN your table
ON Num = [NumberOfWorkDaysToCode/Key]

or something like that.

Then, create a Crosstab query based on the above query using
the crosstab query wizard.

 

[JS]

Thanks Marshall, I will give it a try!

I am having a difficult time figuring this one out!

I have a query that is pulling the fields that I need in a report.
The fields are:

BA Complexity Code NumberOfWorkDaysToCode/Key

ES MCK 7
ES LCK 1
ES HCK 7
ES HCK 7

I need my report to look something like this:

Number of Days
To Code/Key HCK LCK MCK

1 Day 1
2 Days
3 Days
4 Days
5 Days
6 Days
7 Days 2
1
etc.
31 Days

For Example From the above query - 7 Days would = 2 at HCK for the Benefit
Administrator. Am I forced to use Excel?

You will need a table (named Numbers) with one field (named
Num) populated witht he numbers 1 through at least 31.

Then add that table to your query and join them on the
NumberOfWorkDaysToCode/Key field

SELECT Complexity, [NumberOfWorkDaysToCode/Key], Num
FROM Numbers LEFT JOIN your table
ON Num = [NumberOfWorkDaysToCode/Key]

or something like that.

Then, create a Crosstab query based on the above query using
the crosstab query wizard.

 

[JS]

It worked Marshall, you are the best. Thank you!

Thanks Marshall, I will give it a try!

I am having a difficult time figuring this one out!

I have a query that is pulling the fields that I need in a report.
The fields are:

BA Complexity Code NumberOfWorkDaysToCode/Key

ES MCK 7
ES LCK 1
ES HCK 7
ES HCK 7

I need my report to look something like this:

Number of Days
To Code/Key HCK LCK MCK

1 Day 1
2 Days
3 Days
4 Days
5 Days
6 Days
7 Days 2
1
etc.
31 Days

For Example From the above query - 7 Days would = 2 at HCK for the Benefit
Administrator. Am I forced to use Excel?

You will need a table (named Numbers) with one field (named
Num) populated witht he numbers 1 through at least 31.

Then add that table to your query and join them on the
NumberOfWorkDaysToCode/Key field

SELECT Complexity, [NumberOfWorkDaysToCode/Key], Num
FROM Numbers LEFT JOIN your table
ON Num = [NumberOfWorkDaysToCode/Key]

or something like that.

Then, create a Crosstab query based on the above query using
the crosstab query wizard.