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.
thandelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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
0
thandelAuthor Commented:
OK Jeff, Attached.  Sorry about the size I have no idea why its 6MB... it has no content/data.
BETA.mdb
0
Jeffrey CoachmanMIS 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:
    =[CountOfFrameModel]/DCount("FrameModel","tmain")
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

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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.
0
Jeffrey CoachmanMIS LiasonCommented:
yes, my size went to 360k
(Make sure you refresh the folder)
0
thandelAuthor Commented:
Odd, when I compact and repair with 2003 the size doesn't change.
0
Jeffrey CoachmanMIS LiasonCommented:
I'm using 2010
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.