Solved

Turn a query into a function in access 2010.

Posted on 2013-01-09
6
427 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now