Listing activities by Week - Revisited

[Winnetou]

(Reformulated question based on an earlier post. Thanks to Duane
Hookom's hints!)

I have a query (qryActDay) that sorts activities by weekdays (Mon, ...,
Sun). Each weekday is either void or contains the first activity
recorded on that day. How can I create a query that would "add up" all
the weekday's activities to produce a single week record?


Current data (qryActDay):
ActDueDay Wk Mon Tue Wed Thu Fri Sat
Sun
02/27/06 09 Init PRJ1
03/01/06 09 Quote PRJ1
03/03/06 09 Specs PRJ2
03/07/06 10 Invc PRJ1
03/10/06 10 Init PRJ2

Desired output:
Wk Mon Tue Wed Thu Fri Sat Sun
09 Init PRJ1 Quote PRJ1 Specs PRJ2
10 Invc PRJ1 Init PRJ2


Thanks for any insights!

Mark

Query 'qryActDay':
SELECT tblAct.ActDueDay, CInt(DatePart("ww",[ActDueDay],2)) AS Wk,
First(IIf(Len([ActDesc])>5,Left([ActDesc],5) & "..",[ActDesc])) AS
ActDesc1, Weekday([ActDueDay]) AS WkDay, IIf([WkDay]=1,[ActDesc1],Null)
AS Sun, IIf([WkDay]=2,[ActDesc1],Null) AS Mon,
IIf([WkDay]=2,[ActDesc1],Null) AS Tue, IIf([WkDay]=3,[ActDesc1],Null)
AS Wed, IIf([WkDay]=4,[ActDesc1],Null) AS Thu,
IIf([WkDay]=6,[ActDesc1],Null) AS Fri, IIf([WkDay]=7,[ActDesc1],Null)
AS Sat
FROM tblAct
GROUP BY tblAct.ActDueDay
ORDER BY tblAct.ActDueDay;

 

[Tom Ellison]

Dear Mark:

Are you able to guarantee that there will never be two events on the same
data? It won't happen that Project A will be Quoted on the same day that
Project B is Invoiced? Or, if this could happen, what do you want it to
show?

A specification must cover all contingencies. I believe it was a wise man
who said this! OK, I just made that up. Well, it should have been said.

Tom Ellison

 

[John Vinson]

A specification must cover all contingencies. I believe it was a wise man
who said this! OK, I just made that up. Well, it should have been said.

Yep. That was part of the Specification Specification. Or it should
have been!

<g>

John W. Vinson[MVP]

 

[Tom Ellison]

John,

I was trying to be slightly funny. But you make me laugh! Thanks!

Tom

 

[Winnetou]

Thanks for your comments.

I completely forgot about crosstab queries! Using Duane Hookum's hint,
I created crosstab query 'qryActWeek' which solves the problem.

Regards,

Mark

Query 'qryActWeek':
TRANSFORM First(qryActDay.ActDesc1) AS FirstOfActDesc1
SELECT qryActDay.Wk
FROM qryActDay INNER JOIN qryWkDay ON qryActDay.WkDay = qryWkDay.Code
GROUP BY qryActDay.Wk
PIVOT qryWkDay.Desc In ("Sun","Mon","Tue","Wed","Thu","Fri","Sat");

 

[Saran]

Mark,

Try this query...

Select T.Week,Mo.tMon,Tu.tTue,Wd.tWed,Th.tThu,Fr.tFri,Sa.tSat,Su.tSun
From
(Select Distinct Week from TestTable) T,
(Select Week, Max(Mon) as tMon from TestTable Group By Week) Mo,
(Select Week, Max(Tue) as tTue from TestTable Group By Week) Tu,
(Select Week, Max(Wed) as tWed from TestTable Group By Week) Wd,
(Select Week, Max(Thu) as tThu from TestTable Group By Week) Th,
(Select Week, Max(Fri) as tFri from TestTable Group By Week) Fr,
(Select Week, Max(Sat) as tSat from TestTable Group By Week) Sa,
(Select Week, Max(Sun) as tSun from TestTable Group By Week) Su
Where T.Week = Mo.Week and
T.Week = Tu.Week and
T.Week = Wd.Week and
T.Week = Th.Week and
T.Week = Fr.Week and
T.Week = Sa.Week and
T.Week = Su.Week

Works on the assumption that for any given week a week-day will
have data only once.

Note: Output does not contain ActDueDay

Thanks,
Saran.