CrossTab Query Report

[Oscar]

I work for a childcare company and I am trying to create a report to
analyze our total enrollment from all of our centers per quarter. A
crosstab query is appropriate for this because I would like the center
number to be on the left and the column headings to be the week ending
date. In my query, I have it set to only show week ending dates from
the current quarter.
The data is put in the database when our center directors fill out an
excel form and email it in every week. We then import that data and
access generates the reports. So we don't have any data for future
week ending dates until those dates come around. How can I get access
to just automatically put in the future week ending dates into this
report (even if they're blank) for the entire quarter so I don't have
to go add them in manually every week once they appear in my field
list?
Any help is greatly appreciated!
-Oscar

 

[Duane Hookom]

I am not sure how far you have gotten with this since you didn't provide the
SQL view of your crosstab. You can set the Column Headings property to all
potential week ending dates.

 

[Oscar]

I'm not very familiar with SQL, how could I provide the SQL view of my
crosstab? I am using Microsoft Access 2003. Thanks!

 

[Duane Hookom]

While in the query design, select View -> SQL.
Is this the same question as your more recent thread?

 

[Oscar]

Duane,
Unfortunately, this is not the same question. They are for two
different reports. However, in the future I do hope to incorporate the
two ideas... but that's for another day. Here is the SQL view like you
had asked.

TRANSFORM Max(tblWeeklyFigures.[Labor $]) AS [MaxOfLabor $]
SELECT tblWeeklyFigures.[Center No], tblCenterDirectory.[Center Name]
FROM tblWeeklyFigures INNER JOIN tblCenterDirectory ON
tblWeeklyFigures.[Center No] = tblCenterDirectory.[Center No]
WHERE (((Format([Week Ending Date],"Short Date")) Between (Date()) And
(Date()-84)))
GROUP BY tblWeeklyFigures.[Center No], tblCenterDirectory.[Center Name]
ORDER BY Format([Week Ending Date],"Short Date")
PIVOT Format([Week Ending Date],"Short Date");

Thanks,
Oscar

 

[Oscar]

Duane, I apologize, I was getting my questions confused. The SQL view
I put in the other view was for another question... Here is the SQL
view of the query for the enrollment question. However, it's not so
much the query I am having trouble with as the report, my query only
shows entries from this quarter (it is linked to another query with
that criteria qryGeneralRunnignData).

TRANSFORM Max(qryGeneralRunningData.[PS No]) AS [MaxOfPS No]
SELECT qryGeneralRunningData.[Center No], qryGeneralRunningData.[Center
Name], Max(qryGeneralRunningData.[PS No]) AS [Total Of PS No]
FROM qryGeneralRunningData
GROUP BY qryGeneralRunningData.[Center No],
qryGeneralRunningData.[Center Name]
PIVOT Format([Week Ending Date],"Short Date");

Thanks,
Oscar

 

[Duane Hookom]

I'm not sure how you divide weeks into quarters since a week could span two
quarters. I always attempt to use relative dates as column headings rather
than actual dates.

You might get some idea of my general solutions by searching google groups
on
relative dates group:*access.queries* author:hookom


--
Duane Hookom
MS Access MVP

Duane, I apologize, I was getting my questions confused. The SQL view
I put in the other view was for another question... Here is the SQL
view of the query for the enrollment question. However, it's not so
much the query I am having trouble with as the report, my query only
shows entries from this quarter (it is linked to another query with
that criteria qryGeneralRunnignData).

TRANSFORM Max(qryGeneralRunningData.[PS No]) AS [MaxOfPS No]
SELECT qryGeneralRunningData.[Center No], qryGeneralRunningData.[Center
Name], Max(qryGeneralRunningData.[PS No]) AS [Total Of PS No]
FROM qryGeneralRunningData
GROUP BY qryGeneralRunningData.[Center No],
qryGeneralRunningData.[Center Name]
PIVOT Format([Week Ending Date],"Short Date");

Thanks,
Oscar