Automatically create query for weeks worth of info

N

NeoFax

I have three Crosstab queries that take info from one table that shows
the information for HrsSpent, HrsAssigned and HrsWorked by day.
However, I really don't want to add 93 columns worth of info in a
query that then recombines the info from the three crosstab queries
for a report. Here is how my data looks:

tblHoursAccumulation
Helo Station Date HrsSpent HrsAsg HrsWkd
HrsSpentDelta HrsAsgDelta HrsWkdDelta
14726 1 1/21/09 225.05 225.05 225.05
0.0 0.0 0.0
14726 2 1/21/09 423.74 375.27 300.00
125.0 0.0 100.0
....
14726 1 1/22/09 225.05 225.05 225.05
0.0 0.0 0.0
14726 2 1/22/09 428.00 375.27 308.00
4.26 0.0 8.0

crtbDailySpentDelta
Helo Station TotalSpent 1 2 3 4 5 6 7 8...21
22 23...31
14726 1 225.05
0.0 0.0
14727 2 428.00
125.00 4.26

crtbDailyAssignedDelta

crtbDailyWorkedDelta

qryHoursAccumulation
Helo Station TotalSpent TotalAsg TotalWkd <AutomaticallyCreated>
19 20 21 22 23

Is this a possibility? If not, it will work if I manually hand jam
all of the 31 days. Thanks!
 
K

KARL DEWEY

I really don't want to add 93 columns worth of info in a query that then
recombines the info from the three crosstab queries for a report.

You did not say what you want the final to look like.
 
N

NeoFax

recombines the info from the three crosstab queries for a report.

You did not say what you want the final to look like.

--
KARL DEWEY
Build a little - Test a little








- Show quoted text -
Click to expand...

The qryHoursAccumulation is how I would like the output to look.

Helo Station TtlSpt TtlAsg TtlWkd Mon Tue Wed Thu Fri
14726 1 0.00 0.00 0.00 1.00 2.00 3.00 4.00 5.00
14726 2 0.00 0.00 0.00...
14727 1 0.00 0.00 0.00 1.00 2.00 3.00 4.00 5.00
14727 2 0.00 0.00 0.00 1.00 2.00 3.00 4.00 5.00
....

If that is doable, then it is great.

Terry
 
K

KARL DEWEY

Try this, it pulls last weeks data --
TRANSFORM Sum([HrsSpentDelta]+[HrsAsgDelta]+[HrsWkdDelta]) AS Expr2
SELECT tblHoursAccumulation.[Helo], tblHoursAccumulation.[Station],
tblHoursAccumulation.[HrsSpent], tblHoursAccumulation.HrsAsg,
tblHoursAccumulation.HrsWkd
FROM tblHoursAccumulation
WHERE (((tblHoursAccumulation.Date) Between Date()-Format(Date(),"ww")-7 And
Date()-Format(Date(),"ww")))
GROUP BY tblHoursAccumulation.[Helo], tblHoursAccumulation.[Station],
tblHoursAccumulation.[HrsSpent], tblHoursAccumulation.HrsAsg,
tblHoursAccumulation.HrsWkd
PIVOT Format([Date],"ddd");
 
N

NeoFax

Try this, it pulls last weeks data --
TRANSFORM Sum([HrsSpentDelta]+[HrsAsgDelta]+[HrsWkdDelta]) AS Expr2
SELECT tblHoursAccumulation.[Helo], tblHoursAccumulation.[Station],
tblHoursAccumulation.[HrsSpent], tblHoursAccumulation.HrsAsg,
tblHoursAccumulation.HrsWkd
FROM tblHoursAccumulation
WHERE (((tblHoursAccumulation.Date) Between Date()-Format(Date(),"ww")-7 And
Date()-Format(Date(),"ww")))
GROUP BY tblHoursAccumulation.[Helo], tblHoursAccumulation.[Station],
tblHoursAccumulation.[HrsSpent], tblHoursAccumulation.HrsAsg,
tblHoursAccumulation.HrsWkd
PIVOT Format([Date],"ddd");

--
KARL DEWEY
Build a little - Test a little



The qryHoursAccumulation is how I would like the output to look.
Click to expand...
Helo  Station  TtlSpt  TtlAsg  TtlWkd  Mon  Tue  Wed  Thu Fri
14726  1       0.00    0.00    0.00   1.00  2.00 3.00 4.00 5.00
14726  2       0.00    0.00    0.00...
14727  1       0.00    0.00    0.00  1.00  2.00  3.00  4.00 5.00
14727  2       0.00    0.00    0.00  1.00  2.00  3.00  4.00 5.00
....
Click to expand...
If that is doable, then it is great.
Click to expand...
Terry- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Here is the final code:

TRANSFORM Sum([SAP_Delta]+[MECAPP_Delta]+[MECAPP_WkdDelta]) AS Expr2
SELECT tblSAPvsMECAPPHrs.HELO, tblSAPvsMECAPPHrs.STATION,
tblSAPvsMECAPPHrs.SAP_Delta, tblSAPvsMECAPPHrs.MECAPP_Delta,
tblSAPvsMECAPPHrs.MECAPP_WkdDelta
FROM tblSAPvsMECAPPHrs
WHERE (((tblSAPvsMECAPPHrs.DATE) Between Date()-Format(Date(),"ww")+1
And Date()-Format(Date(),"ww")+7))
GROUP BY tblSAPvsMECAPPHrs.HELO, tblSAPvsMECAPPHrs.STATION,
tblSAPvsMECAPPHrs.SAP_Delta, tblSAPvsMECAPPHrs.MECAPP_Delta,
tblSAPvsMECAPPHrs.MECAPP_WkdDelta
PIVOT Format([Date],"ddd");

It worked great! I just need to see if I can have multiple values in
a crosstab query.
 
Top