asked on

Determine total percentage on a report

I have a 2003 Access report pulling data from a qeury using:

SELECT tMain.FrameModel, tMain.FrameLine, Count(tMain.FrameModel) AS CountOfFrameModel, tMain.Status
FROM tMain
GROUP BY tMain.FrameModel, tMain.FrameLine, tMain.Status
HAVING (((tMain.FrameLine)=[ENTERFRAMELINE]) AND ((tMain.Status)<>"Cancelled"))
ORDER BY tMain.FrameModel;

In the report detail I have a text box displaying CountOfFrameModel which is showing the toal number of models. The report is fine but instead of displaying the total number I would like to have the report display the total of all frame models based on ENTERFRAMELINE in the table as a percentage for the total number of ENTERFRAMELINE

I think I just need to somehow get a grand total of the number of ENTERFRAMELINE entries found then I can do some basic division with that number to get a percent.

Jeffrey Coachman

Without a sample database for context, I am having trouble visualizing what you have and what you need.

This is why it is always best to include a sample database...
We basically only need you show us what you have, then show exactly what you want.
In other words don't say "I want to sum the quantities"
...Instead *Show* us the actual quantities summed:
12
37
71
____
120

Description are great, but remember we don't know:
1. Why you would have a "Total" textbox in the detail section (total for each record?, or is this a running sum?
2. what a "frame" is or how it relates to a Model...
3. what ENTERFRAMELINE is...
<display the total of all frame models based on ENTERFRAMELINE in the table as a percentage for the total number of ENTERFRAMELINE>
???

You know the drill by now:
;-)

1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide any hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
(Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
Post the explicit steps on how to recreate the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

Again, just show us what you have, and then show us what you want...
If we need anything clarified, we will ask.
Sound fair?
;-)

JeffCoachman

thandel

ASKER

OK Jeff, Attached. Sorry about the size I have no idea why its 6MB... it has no content/data.
BETA.mdb

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thandel

ASKER

Thanks Jeff, I had run a compact and repair and the file size remained unchanged. Did you run it with better results?

Frame models are entered by the user... so no table.
Status table was removed from the sample database.

Thanks for the percentage solution. Please let me know if you were able to get the file size smaller somehow.... just curious.

Jeffrey Coachman

yes, my size went to 360k
(Make sure you refresh the folder)

thandel

ASKER

Odd, when I compact and repair with 2003 the size doesn't change.

Jeffrey Coachman

I'm using 2010

thandel

ASKER

What is interesting is that if I export the report and query its 264K... if I take my original DB and export everything its 6MB smaller... for some reason 2003 can't compact and repair my sample or original DB.... something in it is 6MB that will not go away... OK thanks for confirming. I may start a new DB and import everything into it.