Solved

Lookup in a text  box trying to sum it.

Posted on 2013-01-08
12
355 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

911 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

24 Experts available now in Live!

Get 1:1 Help Now