Dynamic report

[Nengster]

Would anyone care to provide asssitance on how I may to able to produce a report as if I am creating an excel worksheet based on data entered into my table monthly? Here is an explantion of what I am trying to achieve in Access.

I have 2 tables: One that stores my contacts list, and another that I use to record the date of the courses I conduct for my clients and the number of attendees as I progress through the year. the number of courses and dates are not fixed per month and may vary. I have created a form to enter this data and table to store it

Eg.

Client AAA
Course date: 1/2/2009
No of attendees: 3

Client BBB
Course date: 1/2/2009
No of attendees: 5

Client CCC
Course date: 1/2/2009
No of attendees: 1

Client AAA
Course date: 15/2/2009
No of attendees: 2

Client BBB
Course date: 15/2/2009
No of attendees: 4

Client CCC
Course date: 15/2/2009
No of attendees: 1

Client AAA
Course date: 6/3/2009
No of attendees: 3

Client BBB
Course date: 6/3/2009
No of attendees: 5

Client CCC
Course date: 6/3/2009
No of attendees: 7

I would like to create a report that would print out the date of the courses and number of attendees in table form.

Eg

Company 1/2/2009 15/2/2009 6/3/2009 ... total

AAA 3 2 3 ... 8

BBB 5 4 5 ... 14

CCC 1 1 7 ... 9

Thanks alot guys!

 

[Rich]

Try a crosstab query

 

[Nengster]

Thanks for the suggestion. But the problem is that every month the course dates as well as the number of courses held are different hence I cannot fix the number of date fields on the report but instead it has to be able to extract from my table the dates in which the courses are held and then determine the number of columns required for each date accordingly.

 

[thatlem]

First build a query with all the data fields from the two tables into the one query. Then build a report from the query defining the sort criteria you wish to sort by. In the details you can define a field for the event and number of attendes, and then sum the field in the sort field footer. It would appear as:

AAA
Event x Attendees 2
Event y Attendees 8
TOTAL: 10

BBB
Event A etc....

Hope this helps