Solved

Turn a query into a function in access 2010.

Posted on 2013-01-09
6
433 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 40

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 40

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 40

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with vba code 4 39
SQL Select in Access 2003 3 25
Collapse and expand table in Word 2010 2 32
Access Web App or PowerApp 24 33
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

856 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