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

[rahuld]

=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.

[TheSmileyCoder]

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?

[rahuld]

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 tblTabularPremi um-Individual]","[10A tblTabularPremi um-Individual]![PLAN TYPE]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![PROPOSED PLAN TYPE - IND] AND [10A tblTabularPremi um-Individual]![INSURANCE CO]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![PROPOSED INSURANCE CO - IND] AND [10A tblTabularPremi um-Individual]![PLAN NAME]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![PROPOSED PLAN NAME - IND] AND [10A tblTabularPremi um-Individual]![AGE]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![06B tblSubQuoteMemb er-Individual].Form![FINAL AGE] AND [10A tblTabularPremi um-Individual]![SUM INSURED]=Forms![05 frmqryMainQuote - INDIVIDUAL]![06A tblSubQuotePlan-Individual].Form![06B tblSubQuoteMemb er-Individual].Form![PROPOSED SUM INSURED - IND MEM]")

[TheSmileyCoder]

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.

[rahuld]

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+4 000=8200.

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

Help is much appreciated!!!

[rahuld]

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

[TheSmileyCoder]

Those 4 entries, do they not have a foreign key linking it to a main account for instance?

[rahuld]

how can i send or upload my database

[rahuld]

5 mb approx...can i email it or is there a way such that all can see?

[TheSmileyCoder]

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

[rahuld]

database attached

Have attached the file. Please have a look.

I am presently working on Form 05....

thanks a ton

[nico5038]

I'm afraid that your table and fieldnames cause havoc.
These names:
[PROPOSED PLAN TYPE - IND]
[06B tblSubQuoteMemb er-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.:
[ProposedPlanTyp eInd]
[tblSubQuoteMemb erIndividual]
[ProposedSumInsu redIndMem]
etc.

Nic;o)