Solved

Lookup in a text  box trying to sum it.

Posted on 2013-01-08
360 Views
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
Question by:gigifarrow
• 5
• 5
• 2

LVL 84

Expert Comment

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

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

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

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:
...(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

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

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

LVL 74

Expert Comment

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

Expert Comment

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

Author Comment

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

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

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

Expert Comment

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

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custoâ€¦
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledâ€¦