Month Order ID via SubQuery

[gall3on]

I've been working on an excel calendar project that's now migrating into access.

Essentially, I have a table of events entered by various users and I need to create a report/view of such events by month.

The table collects the following basic info:

created by:
date start:
event name:
date end:

The view below essentially displays how I need this to look as in a report.

Jan || Feb || Mar || April ||
JanEv1 FebEv1 MarEv1 AprEv1
JanEv2 FebEv2 MarEv2 AprEv2
JanEv3 AprEv3
AprEv4

I was able to achieve the format above in Excel using array formulas, however, in Access, I've been trying numerous times to get it to show via CrossTab query, but coming up empty. I was advised to do a separate query which assigns an order number for the events per month (which in turn is used as the row heading in a crosstab query), but I am unable to do so successfully.

How can I achieve what I want to do above?

 

[dhookom]

I would probably try to use something like the sample calendar reports at Calendar Reports sample.

A solution might involve using a table of all months to create a cartesian type query where a record is generated for each month of a project.

If you have further questions, come back with exact table and field names and some sample records.

Duane
Hook'D on Access
MS Access MVP

 

[BitZero]

One possibility is to do this:

For each column, create a query and a report built over that query. You would have a qryJan, rptJan, qryFeb, rptFeb, etc. Each query would select the records for that particular month. The reports would not have any headings or footers.

Next, build a master report, where each column is a subreport, using rptJan, rptFeb, etc.

Hope that helps

 

[dhookom]

BitZero,
Why not create a single query with a month column and a single subreport. The main report can include 12 adjacent copies of the same subreport and use the link master/child to filter the records displayed based on the month column.

BTW: It doesn't do much good to insert a link to an MDB on your hard drive. If you want to share a file, you need to place it on a web host like box.net or other.

Duane
Hook'D on Access
MS Access MVP

 

[BitZero]

Duane
I'm not sure I understand using the master/child link to filter, but having just a single query/report would be much better.

I noticed my attachment didn't work. That's the first time I've tried that. You have to pay for box.net, so I gave up on that.

Thanks