Listing activities by week

[Winnetou]

Hi,

Is there a clever way to speed up query execution? I would like to
create a query that lists activities with the following rule: if no
activity per day, leave blank; if multiple activities per
day, show first.

The tables are:
a. tblWeek:
WkID
1
...
52

b. tblAct:
ActDay ActDesc
02/27/06 Call XYZ
03/06/06 Start ABC
03/07/06 Finish ABC
03/14/06 Work Proj1
....

Ideally, over the week range: 9..11, the query would produce:
Wk Monday Tuesday Wednesday
09 Call XYZ
10 Start ABC Finish ABC
11 Work Proj1

Currently, a query (qryActWk) sorts all tasks by day and creates a week
number fld. This field is used to set up an outer join with tblWeek
(qryActWkLst). To show only one activity per day, I added LAST in
qryActWkLst. However, this criterion will only list one activity per
week.

A work-around would be to create 7 distinct LAST queries (one per day)
and use an union query to show the whole week. But, I would like to
avoid UNION queries due to speed slow down.

Thanks for any insights!

Mark

 

[Duane Hookom]

Don't you want to present this in a report? Check the calendar samples at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

 

[Winnetou]

Duane,

Thanks for looking at my question.

Actually no. I would like to have the week's activities displayed in a
DS form and allow users to click on the day of their choice. This
should list all the events pertaining to that day in a separate
subform. The query I am interested in should provide a simple overview
of what happened during that day: blank if nothing, otherwise the first
activity if one or more activites were recorded on that day.

Regards,

Marc

 

[Duane Hookom]

Use a crosstab query that uses WeekDay([yourdatefield]) as the column
heading.

 

[Winnetou]

Duane,

Thanks. I posted a slightly revisited question a few moments ago but
this should get me started... I completely forgot that Access supports
crosstab queries!

 

[Winnetou]

Duane,

Thanks. I posted a slightly revisited question a few moments ago but
this should get me started... I completely forgot that Access supports
crosstab queries!