=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.
11  4915 
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?
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]")
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.
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!!!
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
Those 4 entries, do they not have a foreign key linking it to a main account for instance?
how can i send or upload my database
5 mb approx...can i email it or is there a way such that all can see?
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
Have attached the file. Please have a look.
I am presently working on Form 05....
thanks a ton
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)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John Hargrove |
last post by:
I am building a database to manage test samples in an environmental
laboratory. I am learning Access as I go and don't know much about
the programming aspects. I hope to make the application...
|
by: JLM |
last post by:
What am I missing here?
I have a form where I enter a "Class Code". This value corresponds to what
sits in table "class code descriptions" along with the "title" of each
"class code." Key...
|
by: Kalvin Schroder |
last post by:
I am fairly new to Access, and am trying to put together an invoice
form. The main form in called InvoiceDetailFm. Source is the table
InvoiceDetail and has invoice number, saleman, and CustID as...
|
by: LSemos |
last post by:
I am trying to lookup phone numbers in a separate table based on the
name in the current Tab control
tblContacts has the fields: Name, Phone, Fax, and Email
In my Form's Tab control, I have...
|
by: Tony Williams |
last post by:
Does DLookup work in an expression in a query? I have this expression
-(DLookUp("","tblmaintabs","= "))
Which works fine as a calculated control on a form but when I try to use it
in a query as an...
|
by: Don Sealer |
last post by:
I've written this expression for a DLookup function. It works almost
alright.
What I'm trying to do is type in a description and the ID field (number)
populates automatically.
It works almost as...
|
by: John Crowley |
last post by:
I keep running into this over and over again...
I want a block server control that renders a header and footer, and child
controls in between.
But I don't want a templated control, for the...
|
by: hrreece |
last post by:
I have a form, Master List Temp, in Access 2003 that uses the Dlookup command to pull values from another table, Address Master. The user types in a person's home phone number in the HomePhone field...
|
by: |
last post by:
In my database I have a 'control table' in which basic info is stored about the application, for instance the application's path and the name of the company that is using it. In all of the...
|
by: rleepac |
last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue
This is a table used to...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
