• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 568
  • Last Modified:

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
0
stryker11
Asked:
stryker11
  • 3
  • 3
1 Solution
 
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
 
stryker11Author Commented:
Event-Scheduler.accdb

Hi Ed

I have attached herewith the sample DB. Hope this would be useful to finding a solution
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
VTKeganCommented:
Check out the report called EE Help Report.

See if this does what you want.
Event-Scheduler.accdb
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now