CrossTab Help!

[John]

I have a query that uses a subquery to calculate a prior period cost, so I
can calculate the difference between a total todate cost this month and the
total do date cost last month, giving me the period cost. The query works
great! Here's the code:

SELECT tblMonthly.act, tblProjInfo.Period, tblMonthly.adesc,
tblMonthly.pcompl_td, tblMonthly.budgt_cur, tblMonthly.ebudgt_td,
tblMonthly.actualttd, [tblMonthly].[actualttd]-IIf(IsNull((SELECT TOP 1
tblDupe.actualttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act)),0,(SELECT TOP 1 tblDupe.actualttd FROM tblMonthly AS
tblDupe WHERE tblDupe.act = tblMonthly.act AND tblDupe.FnDte <
tblMonthly.FnDte ORDER BY tblDupe.FnDte Desc, tblDupe.act)) AS ThisPrdCst
FROM (tblMonthly LEFT JOIN tblActMap ON tblMonthly.act = tblActMap.Act) LEFT
JOIN tblProjInfo ON tblMonthly.FnDte = tblProjInfo.Period_Dte
ORDER BY tblMonthly.act, tblProjInfo.Period;

The IIF statment replaces a null value with a 0 (zero) so the difference
between the two monthly values will give me a true monthly cost.

What I need is a crosstab query that shows all the Activity (tblMonthly.act)
as a row heading, the period (tblProjInfo.Period) as the column headings, and
ThisPrdCst as the values that intersect each Activity ID and Period. I tried
several different approaches and keep getting an error. My suspission is
that subqueries can't be used in a crosstab. Is this correct? (please say
NO!!!)

Any help would be appreciated. What I would like to see as final output:

Act (GroupBy), last(adescr), last(pcompl_td), last(budgt_cur),
last(ebudgt_td), last(actualttd), then period1, 2, 3, ... with ThisPrdCst as
the values in the intersection of Act and Period. (There is only one value
so I am not sure what I should use here First, Last, Sum???)

Any help is greatly appreciated!

 

[John Spencer]

I don't see any reason that you can't use this saved query as the source for a
crosstab query.

TRANSFORM First(ThisPrdCost) as TheCost
SELECT [Act]
FROM [qNameOfYourSavedQuery]
GROUP BY Act
PIVOT [Period]

If the above fails, then post back with the error message you are getting.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John]

John,

Thanks for the response. It seems to me this should be an easy thing. I
might be overlooking something simple. Here's the SQL:

TRANSFORM First([ThisPrdCost]) as TheCost
SELECT [Act]
FROM [qryCreatePeriodCost]
GROUP BY Act
PIVOT [Period]

Here's the error I get:

"The Microsoft Jet database engin does not recognize 'tblMonthly.act' as a
valid field name or expression."

The code for qryCreatePeridCost is in my previous post and runs fine.

--
Thanks - John

I don't see any reason that you can't use this saved query as the source for a
crosstab query.

TRANSFORM First(ThisPrdCost) as TheCost
SELECT [Act]
FROM [qNameOfYourSavedQuery]
GROUP BY Act
PIVOT [Period]

If the above fails, then post back with the error message you are getting.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a query that uses a subquery to calculate a prior period cost, so I
can calculate the difference between a total todate cost this month and the
total do date cost last month, giving me the period cost. The query works
great! Here's the code:

SELECT tblMonthly.act, tblProjInfo.Period, tblMonthly.adesc,
tblMonthly.pcompl_td, tblMonthly.budgt_cur, tblMonthly.ebudgt_td,
tblMonthly.actualttd, [tblMonthly].[actualttd]-IIf(IsNull((SELECT TOP 1
tblDupe.actualttd FROM tblMonthly AS tblDupe WHERE tblDupe.act =
tblMonthly.act AND tblDupe.FnDte < tblMonthly.FnDte ORDER BY tblDupe.FnDte
Desc, tblDupe.act)),0,(SELECT TOP 1 tblDupe.actualttd FROM tblMonthly AS
tblDupe WHERE tblDupe.act = tblMonthly.act AND tblDupe.FnDte <
tblMonthly.FnDte ORDER BY tblDupe.FnDte Desc, tblDupe.act)) AS ThisPrdCst
FROM (tblMonthly LEFT JOIN tblActMap ON tblMonthly.act = tblActMap.Act) LEFT
JOIN tblProjInfo ON tblMonthly.FnDte = tblProjInfo.Period_Dte
ORDER BY tblMonthly.act, tblProjInfo.Period;

The IIF statment replaces a null value with a 0 (zero) so the difference
between the two monthly values will give me a true monthly cost.

What I need is a crosstab query that shows all the Activity (tblMonthly.act)
as a row heading, the period (tblProjInfo.Period) as the column headings, and
ThisPrdCst as the values that intersect each Activity ID and Period. I tried
several different approaches and keep getting an error. My suspission is
that subqueries can't be used in a crosstab. Is this correct? (please say
NO!!!)

Any help would be appreciated. What I would like to see as final output:

Act (GroupBy), last(adescr), last(pcompl_td), last(budgt_cur),
last(ebudgt_td), last(actualttd), then period1, 2, 3, ... with ThisPrdCst as
the values in the intersection of Act and Period. (There is only one value
so I am not sure what I should use here First, Last, Sum???)

Any help is greatly appreciated!

 

[John Spencer]

Strange. I don't think ACT is a reserved word, but just in case bracket it.
Also remove the ORDER BY clause in qryCreatePeriodCost.

TRANSFORM First([ThisPrdCost]) as TheCost
SELECT [Act]
FROM [qryCreatePeriodCost]
GROUP BY [Act]
PIVOT [Period]

If you still have problems, then all I can think of would be to use
qryCreatePeriodCost as the source for a make table query and then use the
table as the source for the crosstab. Actually, I would probably build the
table as I wanted it and then use delete query to clean it out and an append
query to populate it.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John]

John,

That didn't work either... It's puzzling... I thought of the make table
query as well and figured that would be a solution. What's the best approach
to something like that? Create the table, run the crosstab, and then delete
the table. This way one would be sure that any reports or data comming out
would be from fresh (up-to-date) data. What's the advantage of appending and
not deleting?

--
Thanks - John

Strange. I don't think ACT is a reserved word, but just in case bracket it.
Also remove the ORDER BY clause in qryCreatePeriodCost.

TRANSFORM First([ThisPrdCost]) as TheCost
SELECT [Act]
FROM [qryCreatePeriodCost]
GROUP BY [Act]
PIVOT [Period]

If you still have problems, then all I can think of would be to use
qryCreatePeriodCost as the source for a make table query and then use the
table as the source for the crosstab. Actually, I would probably build the
table as I wanted it and then use delete query to clean it out and an append
query to populate it.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John,

Thanks for the response. It seems to me this should be an easy thing. I
might be overlooking something simple. Here's the SQL:

TRANSFORM First([ThisPrdCost]) as TheCost
SELECT [Act]
FROM [qryCreatePeriodCost]
GROUP BY Act
PIVOT [Period]

Here's the error I get:

"The Microsoft Jet database engin does not recognize 'tblMonthly.act' as a
valid field name or expression."

The code for qryCreatePeridCost is in my previous post and runs fine.

 

[John Spencer]

The advantage of having the table already built and deleting all the records
before appending a new set is that you have full control over the table
structure. (And it may be faster than using a make table query)

Either method can cause bloat in your database size. You might want to look
at Tony Toews site for an example of using a temporary database if the
bloating of your database is a problem.

See Tony Toews website
http://www.granite.ab.ca/access/temptables.htm
for an example

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John]

Thanks for the Info John. Tony's website is down now but I'll check it out...