Solved

Turn a query into a function in access 2010.

Posted on 2013-01-09
6
432 Views
Last Modified: 2013-01-14
I have been having a lot of problems with query that is in a textbox. I would like to get help and turn it into a function then i wouldnt have problems doing other calcualtions in my textboxes. I have the Idea but Im not sure how the code would be.
If anybody knows a better way then using a function please let me know.


I am trying to calculate check boxes.  each check box is  worth a percentage.
so you have

 for example a installation of a car

Name    Door Installed   window installed Hood installed total percenatage
Prius       10%                       50%              40%                      100%
Linc         20%                       30%              50%                      100%
Prius       10 %                      70%                0%                         80%
                                                                                                ________
  Total                                                                                        280%



Here is the code:Percentage it is in a query and when you check a field it calculates the percentage of the amount done for each item.:

Percentae: Format(Abs(([tblTempleFacilityFullup]![Induction]*6+[tblTempleFacilityFullup]![TearDown]*10+[tblTempleFacilityFullup]![Provision]*10+[tblTempleFacilityFullup]![AssemblyA]*10+[tblTempleFacilityFullup]![AsemblyB]*10+[tblTempleFacilityFullup]![AssemblyC]*10+[tblTempleFacilityFullup]![AssemblyD]*10+[tblTempleFacilityFullup]![AssemblyE]*10+[tblTempleFacilityFullup]![RoadTest]*4+[tblTempleFacilityFullup]![QC]*10+[tblTempleFacilityFullup]![OutDuction]*10)/100),"00.0%")


So I thought I could do a function\

Some function_()

Check Mark 1 = 10%

Check mark 2 = 30%

Check Mark 3=  20%  etc.




then it totals and divides each check mark by 100 then I can do my calculatios

Total percentage of all
total percentage of each
total percentage of each make of car


 Here is the database it is not about cars though I just made this for example.
HelpwithqueryTemple.zip
0
Comment
Question by:gigifarrow
  • 3
  • 2
6 Comments
 
LVL 39

Assisted Solution

by:als315
als315 earned 500 total points
ID: 38761757
Have you tested sample from my comment #a38749994 ?
0
 

Author Comment

by:gigifarrow
ID: 38763155
alsThanks for the help but that was the wrong field that I am trying to calulate. I have a query in the recordsource of the field the name of the field is called percentages.

It gives the percentage complete for each record. I looked to see the name and this is what I got below. Im not sure if that is the right name but that is what is on the left tab of the query.  I need more calculations from this field. Dsum doesnt seem to work for queries in a text box but dLookup does but doesnt total. so that is where Im stuck at. In that table I need the total of each and the total of all.

=DSum("[Percentages]","frmTempleFullUp:Query Builder") If you get time please look at the field again.
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 500 total points
ID: 38763924
I don't undrstand idea. May be sample is too far from reality.
You have table with columns: Production (ID ?, but almost always empty), Unit where are some duplicates. On your form nothing is filtered.
May be you can prepare sample in Excel of expected result? I also don't understand percent > 100
In your example:
Name    Door Installed   window installed Hood installed total percenatage
Prius       10%                       50%              40%                      100%
Linc         20%                       30%              50%                      100%
Prius       10 %                      70%                0%                         80%
If you like to calculate some totals, may be you need sum of percents divide to count of lines? In this example Total may be 280/3=93.3
I can write function, where we can calculate totals from your recordset, but I should understand idea.
Try this query (qrytemple):
SELECT tblTempleFacilityFullup.Production, tblTempleFacilityFullup.InShopDate, tblTempleFacilityFullup.SerialNumber, tblTempleFacilityFullup.Unit, tblTempleFacilityFullup.Induction, tblTempleFacilityFullup.TearDown, tblTempleFacilityFullup.Provision, tblTempleFacilityFullup.E1GroundInsert, tblTempleFacilityFullup.ReliefHoleExtinguisher, tblTempleFacilityFullup.AssemblyA, tblTempleFacilityFullup.FuelShotOffSleeve, tblTempleFacilityFullup.AsemblyB, tblTempleFacilityFullup.CEPUpgrade, tblTempleFacilityFullup.AFESCEPSwitch, tblTempleFacilityFullup.AssemblyC, tblTempleFacilityFullup.ERRHandle, tblTempleFacilityFullup.DriverSeat, tblTempleFacilityFullup.AssemblyD, tblTempleFacilityFullup.HotBoxEnhanceMent, tblTempleFacilityFullup.AssemblyE, tblTempleFacilityFullup.LegacyBattery, tblTempleFacilityFullup.[LED/ERR], tblTempleFacilityFullup.T161Track, tblTempleFacilityFullup.AFES, tblTempleFacilityFullup.HotBox, tblTempleFacilityFullup.BASSM2, tblTempleFacilityFullup.GunnersSeatStopRework, tblTempleFacilityFullup.RoadTest, tblTempleFacilityFullup.QC, tblTempleFacilityFullup.OutDuction, tblTempleFacilityFullup.PercentageComplete, tblTempleFacilityFullup.DateCompleted, tblTempleFacilityFullup.DateReturned, tblTempleFacilityFullup.Remarks, -([tblTempleFacilityFullup]![Induction]*6+[tblTempleFacilityFullup]![TearDown]*10+[tblTempleFacilityFullup]![Provision]*10+[tblTempleFacilityFullup]![AssemblyA]*10+[tblTempleFacilityFullup]![AsemblyB]*10+[tblTempleFacilityFullup]![AssemblyC]*10+[tblTempleFacilityFullup]![AssemblyD]*10+[tblTempleFacilityFullup]![AssemblyE]*10+[tblTempleFacilityFullup]![RoadTest]*4+[tblTempleFacilityFullup]![QC]*10+[tblTempleFacilityFullup]![OutDuction]*10)/100 AS Percentage
FROM tblTempleFacilityFullup;

Open in new window

Do not convert percents to integers, you can always use percentage format on your form or report.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Expert Comment

by:lhartono
ID: 38764116
You mean like this?

Form1
0
 

Author Comment

by:gigifarrow
ID: 38764399
Yes,  I hartono thats part of the idea. How did you get the total on the right bottom?
I also need a seperate total of percentages for the Primus so that total would be 150%.

Each check is worth a percentage when they check it calculatets the total amount of percentage of checks until it gets to 100.

On my sample database all the calculations are done in text boxes. I have trouble working with queries. Im still not good in ACCess.


als315
I guess my dummy data should have all the fields filled in Let me .

fill the fields out I will give you the excel spreadsheet that all the callculations need to be done. Which I have to take that sheet and make it look exactly like Excel done in  Access.
ExcelHelpbingochart.JPG
BUSK-III-FS3--STATUS-REPORTforhe.zip
MODKitProduction.zip
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 38766464
Look at sample. May be it is not concerned with your question, but it could be a step to.
MODKitProduction.zip
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The purpose of this article is to demonstrate how we can use conditional statements using Python.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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