Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Turn a query into a function in access 2010.

Posted on 2013-01-09
6
Medium Priority
?
444 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 2000 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 2000 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

571 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