Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Use query data to do a total  calculations in a text box  on a form.

Posted on 2013-01-06
10
Medium Priority
?
435 Views
Last Modified: 2013-01-14
I have a field in a query that is called Percentages.


i Have a text box that needs to take the information from precentages and total the precentages. I can total a table information is it possible to total a query caluculation in a text box?


=DSum("[Scope]","tblScopeUnit")


Like this some how?

=DSum("[Percentages]","Queryname")

Here is my database



for example
 A door installed  a=10% b=20% c=30%    percentages = 60%
 B door installed  a=15% b=30% c=20%    Percentages =  65%

Total= 125%
MODKitProduction.zip
0
Comment
Question by:gigifarrow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 38749670
Yes. Set the datasource for that textbox to be =DSum("[Percentages]","Queryname").
0
 

Author Comment

by:gigifarrow
ID: 38749759
thank you for your time and help! But the query

is embedded in the form in the record source . so I dont know what the name would be
I used

=DSum("[Percentages]","frmTempleFullUp:Query Builder")

i get  this   error? as an error.



if you have the time could you please look at my database and tell me what the name would  be,

in the record source of the form it has this

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, 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%") AS Percentages FROM tblTempleFacilityFullup;
0
 
LVL 10

Expert Comment

by:etech0
ID: 38749771
This way might be easier:

In the properties box for the form, click the button with three dots next to recordsource.

Then, add a field to the query, and you can have it do the calculations. Once youpen the querybuilder to do that, you should be able to see the names of your fields.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 40

Expert Comment

by:als315
ID: 38749994
You can sum field directly (Procent fields were changed from text to numeric). Look at sample.
But may be better to use subform with results from query. Dlookups took too many time.
MODKitProduction.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38751305
"frmTempleFullUp:Query Builder"
..is that the actual name of the table/query?

Try changing it to something without the special characters...
0
 

Author Comment

by:gigifarrow
ID: 38756297
When I click on the record source to see the the query on the tab it has the name "frmTempleFullUp:Query Builder" .

I dont know how to change it because it doesnt show up as a query,on the Navigation under queries.

 I need the total of precentages from a field that is not in the table but was addd to  the query.
So when I put,
TotalPrentages:=DSum("[Percentages]","frmTempleFullUp:Query Builder")  
 error says cant find table or query.


When I go to save it to see what the name of the query is. It ask me if I want to save the record of the SQL statement? it doesnt tell me the name of the query.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38757170
Go to the record source box, and click the little button with three dots that you'll see at the right side.
0
 

Author Comment

by:gigifarrow
ID: 38762905
When I click on the record source where the dots are on the right side the query opens up. on the tab it has the name "frmTempleFullUp:Query Builder" .

When I go to save it to see what the name of the query is. It ask me if I want to save the record of the SQL statement? it doesnt tell me the name of the query.

When I put it in a text box I get a error. It seems to me that you cant use DSum with a query but you can use a Dlookup but it doesnt total with a Dlookup. made a new query called qryTemple and tried this.

=DLookUp("[Percentage]","qrytemple"," [Unit ]= ('278th ABCT')") works but doesnt total
=DSum("[Percentage]","qrytemple"," [Unit ]= ('DRS TEST')") gives me a error everytime?
0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 38764047
If you remove Format from your query:
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%") AS Percentages
replace with:
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) AS Percentages
and dsum should work.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38765907
Try taking out the parentheses and brackets from this:
=DSum("[Percentage]","qrytemple"," [Unit ]= ('DRS TEST')")

Like this:
=DSum("[Percentage]","qrytemple"," Unit = 'DRS TEST'")

If that  doesn't work, also try taking out the single quotes from around DRS TEST.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

610 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