G
Guest
I have 2 simple crosstab queries pulling similar data from 2 different tables
like this:
ForecastMonth
(col heading)
FullName ForecastDays
(row heading) (total - sum)
This pulls from my "forecast" table where ForecastMonth criteria is for the
current month, current month + 1, and current month + 2, resulting in 3
columns.
The second crosstab query works the same way but pulls from my "actual"
table, using the same criteria to create the same 3 columns:
ActualMonth
(col heading)
FullName ActualDays
(row heading) (total - sum)
Sometimes a person may have data in the "forecast" table and not in the
"actual" table, but I need their name and any data (even if it's null or 0)
for the 3 month period mentioned above.
I want this to appear on a report where I can have an additional field
(calculated) in each of the 3 columns that will figure the difference between
the forecasted amount of days versus the actual amount of days (I know how to
do the calculated field on the report). I just need help with how to get the
data from the 2 tables to combine on a report like the sample below:
June July
Aug
Fore. Act. Diff. Fore. Act. Diff. Fore.
Act. Diff.
Name1 15 10 5 10 8 2 22 13
9
Name2 21 19 2 20 0 20 24 0
24
Name3 33 21 12 28 0 28 25 0
25
How can I best reach my goal? Is there a way of making one crosstab query
to accomplish this? I haven't used crosstabs much at all, and appreciate any
insight you can provide! Maybe I need a createtable query...right now I
can't think clearly enough to figure it out!
like this:
ForecastMonth
(col heading)
FullName ForecastDays
(row heading) (total - sum)
This pulls from my "forecast" table where ForecastMonth criteria is for the
current month, current month + 1, and current month + 2, resulting in 3
columns.
The second crosstab query works the same way but pulls from my "actual"
table, using the same criteria to create the same 3 columns:
ActualMonth
(col heading)
FullName ActualDays
(row heading) (total - sum)
Sometimes a person may have data in the "forecast" table and not in the
"actual" table, but I need their name and any data (even if it's null or 0)
for the 3 month period mentioned above.
I want this to appear on a report where I can have an additional field
(calculated) in each of the 3 columns that will figure the difference between
the forecasted amount of days versus the actual amount of days (I know how to
do the calculated field on the report). I just need help with how to get the
data from the 2 tables to combine on a report like the sample below:
June July
Aug
Fore. Act. Diff. Fore. Act. Diff. Fore.
Act. Diff.
Name1 15 10 5 10 8 2 22 13
9
Name2 21 19 2 20 0 20 24 0
24
Name3 33 21 12 28 0 28 25 0
25
How can I best reach my goal? Is there a way of making one crosstab query
to accomplish this? I haven't used crosstabs much at all, and appreciate any
insight you can provide! Maybe I need a createtable query...right now I
can't think clearly enough to figure it out!