mishlce
asked on
Access Sales Report
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Both flyster and Boag2000 were great helps and my report is final. Thanks to them both.
ASKER
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.