Setting Up Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
JS said:
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?
Click to expand...


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.
 
Thanks Marshall, I will give it a try!

Marshall Barton said:
JS said:
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?
Click to expand...


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.
Click to expand...
 
It worked Marshall, you are the best. Thank you!

JS said:
Thanks Marshall, I will give it a try!

Marshall Barton said:
JS said:
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?
Click to expand...


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.
Click to expand...
Click to expand...
 
Back
Top