Solved

MS Access 2007 Summary Reports

Posted on 2010-11-30
7
546 Views
Last Modified: 2012-06-27
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
Comment
Question by:stryker11
  • 3
  • 3
7 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34237537
A sample db would be helpful to us to help you better. You may post it here using sample data.

Sincerely,
Ed
0
 
LVL 10

Expert Comment

by:VTKegan
ID: 34237997
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
 

Author Comment

by:stryker11
ID: 34244559
Event-Scheduler.accdb

Hi Ed

I have attached herewith the sample DB. Hope this would be useful to finding a solution
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 10

Accepted Solution

by:
VTKegan earned 500 total points
ID: 34244861
Check out the report called EE Help Report.

See if this does what you want.
Event-Scheduler.accdb
0
 

Author Closing Comment

by:stryker11
ID: 34244971
Thank you VTKegan. You're a star.
0
 

Author Comment

by:stryker11
ID: 34244980
Just another question, How do I add the totals under each column?
0
 
LVL 10

Expert Comment

by:VTKegan
ID: 34245047
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question