Solved

Access Sales Report

Posted on 2012-03-23
16
480 Views
Last Modified: 2012-03-28
I have a table in access [Please see attachment for fields].  I want to produce a report that will do the following.    Group By:       SUM:            SUM OF # OF ACCOUNTS    
                   
EMP1              SALES          ACCOUNT
EMP2
EMP3
Name
Account
I have already written the query to do the grouping by, sums and counts but ... I want this in a report format that will have one page for each employee.  Pls see example of what I want report to look like below:

Report to look as such:

TITLE OF REPORT

EMP1         EMP2          EMP3            NAME                               ACCOUNT          SALES
160901      68574         JON17          JORDAN, MICHAEL             JUST0001-1       $25.00
                                                                                                   JUST0001-1       $25.00
                                                                                                   JUST0001-1       $25.00
                                                                                                   JUST0001-1       $25.00
                                                                                                   JUST0001-1       $25.00
_____________________________________________________________________________________________
TOTAL: JORDAN, MICHAEL                                                           5                      $125.00
Access-Report.xls
0
Comment
Question by:mishlce
  • 7
  • 5
  • 4
16 Comments
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 218 total points
ID: 37760083
See rptAccess_Report. Is this what you're looking for?

Flyster
Access-Report.accdb
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 282 total points
ID: 37761026
Flyster,
Could not open your sample.
I encountered the "Repair" error when I tried to open the report

Jeff
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 282 total points
ID: 37761028
mishlce,

1. I am confused as to how one employee can have 3 employee numbers?
My guess is that, (based on the emps with no records), that EMP1 is the primary EMP identifier...

2.<one page for each employee. >
I think you mean "Each employee will start on their own page"
(If an employee had 100 records, this will be impossible to fit on one page...)

3. You report here is rather custom, ...so the question becomes, do you want to know "How" to create it?
Or do you just want a sample, and you can "take it form there..."
Explaining every single step, setting, and technique will be a bit much to ask...

The "basics" of your report are straightforward.
It is basically a Grouped report with a summary.
So your first step should be to play around with the Report wizard, and see if you can get something that is close, then ask for help to customize it.

In any event, here is my take on your Report...

JeffCoachman
Database73.accdb
0
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 218 total points
ID: 37769324
Jeff,

See if this one opens. Thanks.

Paul
Access-Report.accdb
0
 

Author Comment

by:mishlce
ID: 37771355
I am unable to open either of these examples.  When I do so it states this is an unreconized database format.  I am running Access 2003?  I tried to change name to .mdb vs. accdb, but still got the same error.  

In regards to your questions:  
Yes each employee has three numbers aligned to them.  One for our sales dept, one for payroll one for HR.  These align with them everywhere and are always the same.  Just used in different circumstances.  

Yes I want each employee to have their own page with no other employees rolling onto their page.  Each employee to have their own report.  If they have multiple pages due to multiple line items, this is ok, just so the nest report page starts with new emplaoyee.

I would like a sample report, and then I can take it from there and work with it.  I tried to use the report wizard and was not able to get the results I was looking for.  I will send this ass attachment.
0
 

Author Comment

by:mishlce
ID: 37771490
Attached is Access DB.  Here is an example of what I came to.  Problem is I only want (all emp numbers and emp name listed once acrross top.  Then I want each account number listed with the dollar amt followed by a total sum of dollar amt and number of accounts per employee name as listed below.  Also I want only each employee to have own report not a run on.  Pls look at Access_Report1




EMP1         EMP2          EMP3            NAME                               ACCOUNT          SALES
160901      68574         JON17          JORDAN, MICHAEL             JUST0001-1       $25.00
                                                                                                   JUST0001-1       $25.00
                                                                                                   JUST0001-1       $25.00
                                                                                                   JUST0001-1       $25.00
                                                                                                   JUST0001-1       $25.00
_____________________________________________________________________________________________
TOTAL: JORDAN, MICHAEL                                                           5                      $125.00
Access-Report.mdb
0
 

Author Comment

by:mishlce
ID: 37771511
Above I attached the access database with report1.  I have elements, but I want the report to look like above example and I am unable to get all Emp information on one line and all accounts and $ amts listed with sum of $ amt and a total number of accounts by employee.  Pls adjust database to provide the above looking report.

The reason it appears I am unable to open is that I am running on access 2003 vs. 2007 which I am soon converting to, however in order to work with it now, I need in 2003.
0
 
LVL 22

Assisted Solution

by:Flyster
Flyster earned 218 total points
ID: 37771926
See if this works for you.
Access-Report.mdb
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 282 total points
ID: 37772013
mishlce,

Just a note here.
Flyster posted a sample DB first, I could not open it, so I posted my own.

This being a fairly straightforward Report, I can't imagine that our samples vary all that much.

So I have no problem with you giving the points to Flyster if his latest DB works as you wanted...
;-)

I am still curios about the multiple Employee ID's though...
;-)

JeffCoachman
0
 

Author Comment

by:mishlce
ID: 37772045
This is great, just a couple of things:  Would like Report name on Each page.  and In the end would like the totals to look as below.

So instead of it saying summary for 'EMP1' = 160901 (5 detaliled records)  I would like it to  Say Total: Name field and under Account have the total of 5 and under sales the 125 to look as $125.00  
______________________________________________________________________________________________

TOTAL: JORDAN, MICHAEL                                                           5                      $125.00
0
 

Author Comment

by:mishlce
ID: 37772087
Jeff,
His worked somewhat, however needed some additional help, see above additional comments.  I was never able to view yours as it is in 2007 and I am working currently on 2003.  Pls submit what you originally submitted in 2007 in 2003 if possible.  

In regards to the Employee numbers: They are 3 examples numbers that tie to the employee for different areas, these numbers follow him throughout all of the systems and reporting.  For confidentiality reasons they are made up.  They are actually an emp # a payroll # and an HR #.  They never change and are all aligned to the employee.   Pls send your example database in Access 2003.  Thanks.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 282 total points
ID: 37772124
OK, thanks for the Emp Ids explanation.
;-)

Here is my sample in the 2003 format.. (sorry about that)
Database731.mdb
0
 

Author Comment

by:mishlce
ID: 37773116
I utilized both of the above reports.  I took flysters report, and then once Jeff, sent his in 2003 I was able to look at it in design view and do the following:
Move report name so that each page showed report header.
And the bottom of each page to look as listed below,  
So instead of it saying summary for 'EMP1' = 160901 (5 detaliled records)  It now says Total: Name field and under Account have the total of 5 and under sales the 125 to look as $125.00  

______________________________________________________________________________________________

TOTAL: JORDAN, MICHAEL                                                           5                      $125.00


All has been completed with a combination from both, I am just not sure how to provide credit to both of you?
0
 
LVL 22

Accepted Solution

by:
Flyster earned 218 total points
ID: 37773230
I'll have to let Jeff answer that one. I've been on this service for 9 years and never asked a question. I glad I could be part of the solution. Thanks.

Paul
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 282 total points
ID: 37773465
In design view, set the "Format" property for that control to: Currency
0
 

Author Closing Comment

by:mishlce
ID: 37778275
Both flyster and Boag2000 were great helps and my report is final.  Thanks to them both.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now