Solved

Lookup in a text  box trying to sum it.

Posted on 2013-01-08
12
352 Views
Last Modified: 2013-01-14
I have a query called qrytemple.

In the query I have a field called Percentage.
It calculates precentage for each record.

I am not getting no error Messages.
I am using a text box for the following code:

=DLookUp("[Percentage]","qrytemple"," [Unit ]= ('278th ABCT')")
I need a total for all the percentages from each unit.
 (and this code  is just counting the first record)

I also need a over all total.
My code looks like this and it works:
=DLookUp("[Percentage]","qrytemple")(this is also only adding the first record)



How do I get these two codes to sum?
HelpDlookUp.zip
0
Comment
Question by:gigifarrow
  • 5
  • 5
  • 2
12 Comments
 
LVL 84
ID: 38757318
You'll need to provide a better explanation. I can't understand what you mean by:

"I need a total for all the percentages from each unit."

Can you show some sample data of what you're looking for? The way I read this, the following DSum would work:

DSum("Percentage", "qryTemple")

That would add together all values in the Percentage column in the qryTemple query.

By the way, your attached database has 0 values in the Percentage column, so you'll only get 0 Sum values, obviously.
0
 

Author Comment

by:gigifarrow
ID: 38757452
In the sample database there is a  table called tblTempleFacilityFullup.  Yes ,it is 0 amount in that field but the whole field name is percentage complete and that is not the field Im talking about.

In the sample database there is a query called qryTemple. In that query there is a calculation called "Percentage" that is the field Im talking about. It does a calculation..

It totals the value of each field that has a percentage and divides by 100 to give you the toatal percentage for each military unit.

In the table there is a field called units that is a name of  a company in the military.

This code does not work get ?Name error Message.
DSum("Percentage", "qryTemple")
 When I try Dsum with a query I get a error

However when I use Lookup I dont get an error. I just dont get the right totals which is based on the query in the sample datbase. There are percentages there in those records in the query..



My code is only giving me the first record in the query Percentage as the answer to both of my calculatioms..

Using the lookup is not calulating what I need.



Example

=DLookUp("[Percentage]","qrytemple")(this is also only adding the first record) need total of all the units in the military.

=DLookUp("[Percentage]","qrytemple")(this is also only adding the first record)

(this is also only adding the first record and I  need total of  each unit separarely).

On the form I have a text field called total for all units and another total for each military unit.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 333 total points
ID: 38758118
I think I see what you mean ... so you're saying that in qryTemple, the Percentage should be that calculation (the one where you multiple various items by numbers, and then divide by 100) for ALL instances of the value in Unit?

If so, you'll need to do this in a separate query, and Sum the value as needed, and then Group those on the Unit. You can then add that new query to qryTemple, and relate the two on the Unit field, then drop the new Percentage field (from the Group query you created) into qryTemple.

You may need to do this in three steps: Build a query with the individual fields calculated - for example, have a field named "PctIncudtion:Induction * 6", and another named PctTearDown:TearDown * 10" and so forth. After you build those calculated fields, then build a second query that Sums those calculated values and Groups on the Unit, and then relate that SECOND query back to to qryTemple.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
ID: 38759276
Gig,

FWIW, you can use:
=DSum("Percentage","qrytemple")
...in your form, if you get rid of the Format() function in your query:
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 Percentage
...(The format() function converts the result to a string, hence no math can be performed)

...Then modify the formula to format it in the form as a percent:
=Format(DSum("Percentage","qrytemple"),"Percent")

However I got 186.00%, ...as the result...

Again, just FWIW...

JeffCoachman
0
 

Author Comment

by:gigifarrow
ID: 38760381
Thanks Jeff!!!! I will try that. Just to let you know I was asked to convert a excel spreadsheet with lots of formulas to Access. This is part of it.

Just when I think I am finished they ask me to do something else.lol  But Im getting better, especially with a lot of your help!!!!
0
 

Author Comment

by:gigifarrow
ID: 38760412
Wow!! LSM thats alot of work I will also try that and see what will work best.
Thanks for your help and time!!!!
I think if the user could like instead of checking the step that they  could input what the percentage the step was worth. That would be so much easier. But too much trouble for the user.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38760460
Gig,

No points wanted for me.
I am sure LSM can get you sorted without my comment.

I only thought to mentioned it as a "FWIW"...

;-)

Jeff
0
 
LVL 84
ID: 38762369
Not really that much work. You just build a few queries you can use with qryTemple.
0
 

Author Comment

by:gigifarrow
ID: 38763017
Okay I tried to make another query from the query that is in the record source of the form called frimTempleFullup. but when I go to the query wizard that query doesnt show up. And I dont know how to make it show up. Or even change the name. I really would like to just use that query since I already have that field in my form. Can you tell me how to do that?


Since I didnt know how to do that:
 I  made another query called qryTemple. And put a field called Percentage and used the code from the query in the record source in the from (the field called  Percentages)

I then made a query called qryTempletotal out of the query called qryTemple
I put a field to sum percentages called  Total: Sum([Percentage]).

 I get an error." You tried to execute a query that does not include the specified expression 'Production' as part of an aggregate funtion. "
MODKitProduction.zip
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 333 total points
ID: 38763091
Queries that are built specifically for a Form won't show up. You need to use the query designer to build this.

You should be able to use the SAME datasource as qryTemple, and build your calculated fields as I showed earlier. Once you do that SAVE that query, then open a new query, and use the query you just built as the source and SUM the percentage columns and GROUP on the Unit.

Then use that second query in qryTemple, and JOIN it on the UNIT field.
0
 

Author Comment

by:gigifarrow
ID: 38764507
Okay I will try this but why cant i Just use my second query that I created and do all the calculations from my text boxes as I was asking in my first question?


Queries cant caculate in a text box?

=DLookUp("[Percentage]","qrytemple"," [Unit ]= ('278th ABCT')")
I need a total for all the percentages from each unit.
 (and this code  is just counting the first record)

I also need a over all total.
My code looks like this and it works:
=DLookUp("[Percentage]","qrytemple")(this is also only adding the first record)



How do I get these two codes to sum?
0
 
LVL 84
ID: 38765547
but why cant i Just use my second query that I created and do all the calculations from my text boxes as I was asking in my first question?
You could perhaps do that, but you'd want to use DSum instead of DLookup.

I don't know what you mean by "How do I get these two codes to sum?". What are the "two codes", and how would they sum?
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
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 …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

708 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

16 Experts available now in Live!

Get 1:1 Help Now