MS Access 2007 Summary Reports

Dear Experts

I have a Access 2007 table which looks like this:

Event      EventMgr      EventStatus      EventDays
                  
MPGC      Abby      OnSchedule      2
CPGC      SM      OnSchedule      3
PGI      VM      Cxld      2
PPI      Abby      OnSchedule      3
PSC      SM      OnSchedule      4
CICL      VM      OnSchedule      3
MSRC      Abby      Cxld      2

I want a report which produces this below:
Event MGR      Abby      SM      VM
                  
TotalEventDays                  
Total Events on Schedule                  

Kindly help me with this.

Thanks

Anand
stryker11Asked:
Who is Participating?
 
VTKeganCommented:
Check out the report called EE Help Report.

See if this does what you want.
Event-Scheduler.accdb
0
 
MINDSUPERBCommented:
A sample db would be helpful to us to help you better. You may post it here using sample data.

Sincerely,
Ed
0
 
VTKeganCommented:
Create a report in design view.

Add your table as the recordsource for the report.

For each of the boxes under each Event Mgr use something like this.

=DSum("EventDays","Table_Events","[EventMgr]='Abby'")

=DCount("Event","Table_Events","[EventMgr]='Abby' AND [EventStatus]='On Schedule'")

This will provide you a report as you described, but would require hardcoding in the names of the users.

I would suggest flipping the report around like this
Event Mgr            TotalEvent Days           Total Events On Schedule
Abby                                 35                                                6
VM
SM

etc...

This way the users can be dynamic.

The record source will be "Select Distinct [EventMgr] From Table_Events"

Then the textbox for Total Event Days would be:

=DSum("EventDays","Table_Events","[EventMgr]='" & [EventMgr] & "')

=DCount("Event","Table_Events","[EventMgr]='" & [EventMgr] & "' AND [EventStatus]='On Schedule'")


Hope this helps
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
stryker11Author Commented:
Event-Scheduler.accdb

Hi Ed

I have attached herewith the sample DB. Hope this would be useful to finding a solution
0
 
stryker11Author Commented:
Thank you VTKegan. You're a star.
0
 
stryker11Author Commented:
Just another question, How do I add the totals under each column?
0
 
VTKeganCommented:
Try this version.  I replaced the DLookups with a better query which should speed up performance of the report.

I also added the Totals to the bottom of the report.


Event-Scheduler.accdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.