Cross Tab Query Days of the week

[Joe H]

Hello, Would appreciate anyone's ideas on this problem:

I have a crosstab query running on daily data where I need
each day of the week (Stretching out 6 months) to get a
column heading. Since I am using parameters to filter-out
certain individuals and am run multiple queries on the
same recordset, I do not always get a crosstab result that
includes all days during that six month period (e.g. based
on the parameters passed a number of the weekend days are
not always present). However, I need all days of the week
to be included in the crosstab irregardless of the
parameters passed. In essence, I need the columns for the
days without data to still show up on the crosstab
result. I've tried appended extra data into the recordset
to be queried. However, it is already quite large and
added the extra data just make it larger and the
performance in that scenario is not acceptable. Is
there a way to pass a query/table that includes all days
during the period to the Pivot statement of the SQL code?

 

[Duane Hookom]

It isn't clear to me what you mean by "each day of the week (Stretching out
6 months)". Do you mean 7 columns or about 180 columns?

Normally when you ask a crosstab (or any query) question it helps if you
provide the SQL view of your query.

 

[Joe H]

Sorry about that. I mean 180 columns. SQL code for my
current query follows:

PARAMETERS WhichArea Text ( 255 ), WhichRU Text ( 255 ),
WhichGEO Text ( 255 );
TRANSFORM Sum(qryResourceCalDly_Union.CHRGHOURS) AS
SumOfCHRGHOURS
SELECT qryResourceCalDly_Union.EMPL_KEY,
qryResourceCalDly_Union.ENG_DESCRIPTION
FROM qryResourceCalDly_Union
WHERE (((qryResourceCalDly_Union.PERIOD_END_DT)>#1/1/2005#
And (qryResourceCalDly_Union.PERIOD_END_DT)<#7/1/2005#)
AND ((qryResourceCalDly_Union.AREA_NAME) Like [WhichArea]
& "*") AND ((qryResourceCalDly_Union.Rollup1) Like
[WhichRU] & "*") AND ((qryResourceCalDly_Union.GEO_NAME)
Like [WhichGEO] & "*"))
GROUP BY qryResourceCalDly_Union.EMPL_KEY,
qryResourceCalDly_Union.ENG_DESCRIPTION,
qryResourceCalDly_Union.AREA_NAME,
qryResourceCalDly_Union.Rollup1,
qryResourceCalDly_Union.GEO_NAME
ORDER BY qryResourceCalDly_Union.PERIOD_END_DT
PIVOT qryResourceCalDly_Union.PERIOD_END_DT;

 

[Duane Hookom]

Enter all of the possible values in your crosstab's Column Headings
property.

--
Duane Hookom
MS Access MVP

Sorry about that. I mean 180 columns. SQL code for my
current query follows:

PARAMETERS WhichArea Text ( 255 ), WhichRU Text ( 255 ),
WhichGEO Text ( 255 );
TRANSFORM Sum(qryResourceCalDly_Union.CHRGHOURS) AS
SumOfCHRGHOURS
SELECT qryResourceCalDly_Union.EMPL_KEY,
qryResourceCalDly_Union.ENG_DESCRIPTION
FROM qryResourceCalDly_Union
WHERE (((qryResourceCalDly_Union.PERIOD_END_DT)>#1/1/2005#
And (qryResourceCalDly_Union.PERIOD_END_DT)<#7/1/2005#)
AND ((qryResourceCalDly_Union.AREA_NAME) Like [WhichArea]
& "*") AND ((qryResourceCalDly_Union.Rollup1) Like
[WhichRU] & "*") AND ((qryResourceCalDly_Union.GEO_NAME)
Like [WhichGEO] & "*"))
GROUP BY qryResourceCalDly_Union.EMPL_KEY,
qryResourceCalDly_Union.ENG_DESCRIPTION,
qryResourceCalDly_Union.AREA_NAME,
qryResourceCalDly_Union.Rollup1,
qryResourceCalDly_Union.GEO_NAME
ORDER BY qryResourceCalDly_Union.PERIOD_END_DT
PIVOT qryResourceCalDly_Union.PERIOD_END_DT;

-----Original Message-----
It isn't clear to me what you mean by "each day of the week (Stretching out
6 months)". Do you mean 7 columns or about 180 columns?

Normally when you ask a crosstab (or any query) question it helps if you
provide the SQL view of your query.

--
Duane Hookom
MS Access MVP


code?


.