By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

how do you get a sum of a dlookup control in the form footer?

P: 7
=Sum(Dlookup(....

does not work for me as the dlookup has 4 criteria and table has some 15000 lines, the sum(dlookup...) causes access to shut down, the dlookup however is working just fine.

Dsum on the other hand does not work as it takes the current record and gives me the total only of that one record.
Feb 4 '10 #1
Share this Question
Share on Google+
11 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
You should be able to write the criteria in DSUM. Maybe your not doing it right :)

What have you tried to put in DSUM, and what is it your want it to sum?
Feb 4 '10 #2

P: 7
FORGIVE THE POOR NAMING OF THE TABLES FORMS ETC....

the dsum works but it gives me the total of the premium field only for the current record

DSum("[PREMIUM]","[10A tblTabularPremium-Individual]","[10A tblTabularPremium-Individual]![PLAN TYPE]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![PROPOSED PLAN TYPE - IND] AND [10A tblTabularPremium-Individual]![INSURANCE CO]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![PROPOSED INSURANCE CO - IND] AND [10A tblTabularPremium-Individual]![PLAN NAME]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![PROPOSED PLAN NAME - IND] AND [10A tblTabularPremium-Individual]![AGE]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![06B tblSubQuoteMember-Individual].Form![FINAL AGE] AND [10A tblTabularPremium-Individual]![SUM INSURED]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![06B tblSubQuoteMember-Individual].Form![PROPOSED SUM INSURED - IND MEM]")
Feb 4 '10 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
If your only getting it for the current record, its because you have a wrong criteria somewhere. Do you really need all those AND?

Im guessing that only 1 record meets your criteria, thats why you only get the sum on that record.
Feb 4 '10 #4

P: 7
My database is to help me arrive at the premium for an insurance plan

Premium depends on
1. Plan Type – Individual Plan
2. Insurance Co – Allianz
3. Plan Name – Health guard
4. Age – Age of that individual
5. Sum Insured – desired sum insured

Name Plan type Ins co Plan name Age Sum ins premium
Rahul Individual Allianz Healthguard 35 100000 2000
Family1 Individual Allianz Healthguard 32 100000 1500
2 Individual Allianz Healthguard 5 100000 700
3 Individual Allianz Healthguard 60 100000 4000

If I create a query and base the form on it …it works out easily, but then it is not updateable.

So the form is based on some combination of tables and the premium in the form comes from the dlookup based on another table. The advantage being that it allows me to change any parameter and pops the correct premium immediately / in realtime.

As of now the dsum put in the form footer will show me 2000 if I am on record 1, 1500 if am on record 2, 700 if I am on record 3 and 4000 if am on record 4, instead of showing me the total for all 4 i.e. 2000+1500+700+4000=8200.

I have forms and subforms, does that create any problem in the dsum.

Help is much appreciated!!!
Feb 4 '10 #5

P: 7
what you are saying is logical as it would seem that :

anyway for each of the full set of criteria there can be only one answer, but obviously if there are 4 members access should understand that we are looking at the sum for all 4 and not merely the current record in that form
Feb 4 '10 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
Those 4 entries, do they not have a foreign key linking it to a main account for instance?
Feb 4 '10 #7

P: 7
how can i send or upload my database
Feb 4 '10 #8

P: 7
5 mb approx...can i email it or is there a way such that all can see?
Feb 4 '10 #9

TheSmileyCoder
Expert Mod 100+
P: 2,321
If you click the "Go advanced" button and scroll down, you can see a "Manage Attachments" button. Before adding your db please do a compact and repair.

.mdb extensions are not allowed (I think) so eitehr Zip it, or change the extension from .mdb to .txt
Feb 4 '10 #10

P: 7
Have attached the file. Please have a look.

I am presently working on Form 05....

thanks a ton
Attached Files
File Type: zip FOR BYTES.zip (494.3 KB, 128 views)
Feb 4 '10 #11

nico5038
Expert 2.5K+
P: 3,072
I'm afraid that your table and fieldnames cause havoc.
These names:
[PROPOSED PLAN TYPE - IND]
[06B tblSubQuoteMember-Individual]
[PROPOSED SUM INSURED - IND MEM]
hold the "-" operator and it's a known bug in Access that mathematical characters are ruining the commands....

Please use only alphabetic characters in your names and don't use spaces. I would recommend using names like e.g.:
[ProposedPlanTypeInd]
[tblSubQuoteMemberIndividual]
[ProposedSumInsuredIndMem]
etc.

Nic;o)
Feb 6 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.