Combine 2 crosstabs on 1 report for analysis

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mona-ABE - 30 Jun 2006 01:07 GMT

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!

Signature

Thanks in advance for any suggestions/tips!
Mona-ABE
--
Thanks!
Mona-ABE

Reply to this Message

Duane Hookom - 30 Jun 2006 02:34 GMT

It is possible to create a multi-value crosstab. Search Google Groups with
the following search text:

multi value crosstab cartesian group:*access.queries* author:hookom

Signature

Duane Hookom
MS Access MVP

>I have 2 simple crosstab queries pulling similar data from 2 different
>tables
[quoted text clipped - 51 lines]
> insight you can provide! Maybe I need a createtable query...right now I
> can't think clearly enough to figure it out!

Reply to this Message

Mona-ABE - 30 Jun 2006 13:54 GMT

First of all, thank you again for the help. I think I'm heading in the right
direction now. I'm just running into a roadblock that I hope you can help me
with...

If I use this SQL, I get correct totals for the forecasted and actual days
for the employees listed:

TRANSFORM
Sum(IIf([FldName]="Forecast",[tblPrjTrkForecastedDays]![ProjForecastDays],[tblPrjTrkActualDays]![ProjActualDays])) AS [Value]
SELECT tblPrjTrkForecastedDays.ProjEmplID
FROM tblPrjTrkXtabColumns, tblPrjTrkForecastedDays INNER JOIN
tblPrjTrkActualDays ON tblPrjTrkForecastedDays.ProjEmplID =
tblPrjTrkActualDays.ProjEmplID
WHERE
(((tblPrjTrkForecastedDays.ProjForecastMonth)=DateSerial(Year(Date()),Month(Date()),1)
Or
(tblPrjTrkForecastedDays.ProjForecastMonth)=DateSerial(Year(Date()),Month(Date())+1,1)
Or
(tblPrjTrkForecastedDays.ProjForecastMonth)=DateSerial(Year(Date()),Month(Date())+2,1)))
GROUP BY tblPrjTrkForecastedDays.ProjEmplID
PIVOT [FldName] & [tblPrjTrkForecastedDays]![ProjForecastMonth];

When I change the relationship to a LEFT JOIN (because I want all the
records to show from the "forecast" table and any matching from the "actual"
table) I get the following error message:

"The SQL statement could not be executed because it contains ambiguous outer
joins. To force one of the joins to be performed first, create a separate
query that performs the first join and then include that query in your SQL
statement."

Any advice you can provide is greatly appreciated!

Signature

Thanks!
Mona-ABE

> It is possible to create a multi-value crosstab. Search Google Groups with
> the following search text:
[quoted text clipped - 56 lines]
> > insight you can provide! Maybe I need a createtable query...right now I
> > can't think clearly enough to figure it out!

Reply to this Message

Duane Hookom - 30 Jun 2006 14:34 GMT

Maybe try your joins in a previous or subsequent query.

Signature

Duane Hookom
MS Access MVP

> First of all, thank you again for the help. I think I'm heading in the
> right
[quoted text clipped - 101 lines]
>> > I
>> > can't think clearly enough to figure it out!

Reply to this Message