# Lookup in a text box trying to sum it.

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
###### Who is Participating?

MIS LiasonCommented:
Gig,

FWIW, you can use:
=DSum("Percentage","qrytemple")
...in your form, if you get rid of the Format() function in your query:
...(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

Infotrakker SoftwareCommented:
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 Commented:
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

Infotrakker SoftwareCommented:
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

Author Commented:
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 Commented:
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

MIS LiasonCommented:
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

Infotrakker SoftwareCommented:
Not really that much work. You just build a few queries you can use with qryTemple.
0

Author Commented:
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

Infotrakker SoftwareCommented:
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 Commented:
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

Infotrakker SoftwareCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.