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.
Who is Participating?
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<I have no idea why its 6MB.>
Did you read my steps?
    11. Run the compact/Repair utility.

You can do what you are asking for here quire easily, ...
However, it would be *much* simpler if your main table was more normalized.
If one try can have many FrameLines, then you need a FrameLine table.
If one FrameLine can have many FrameModels, then where is your FrameModel table?
Where is your Status table...?

But all of this aside...
To answer your question directly:
1. Add another textbox to the detail section, and make the controlsource:
2. Format this new control as Percent
3. Set the decimal places to whatever is appropriate
4. Move and size the controls to fit into your column width

Jeffrey CoachmanMIS LiasonCommented:
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:

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, 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?

thandelAuthor Commented:
OK Jeff, Attached.  Sorry about the size I have no idea why its 6MB... it has no content/data.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

thandelAuthor Commented:
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 CoachmanMIS LiasonCommented:
yes, my size went to 360k
(Make sure you refresh the folder)
thandelAuthor Commented:
Odd, when I compact and repair with 2003 the size doesn't change.
Jeffrey CoachmanMIS LiasonCommented:
I'm using 2010
thandelAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.