(877) 934-6198
Send your laptop to us and we will diagnose the...
Answer
can we enter data in table through query calculation
Problem for Microsoft Office Access 2003 (077-02871) for PC
Access 2003 query issue
I have 2 tables:
I then have a query that contains all the columns from the Customer Data and a summarised total of the payments received for each booking, this is so that I can see the total payments received for each booking.
I would like to add an additional column to the above query that calculates the value of deposits received. I have tried an Iff formula to establish this e.g. Iff(Payment Value >=Deposit Value Requested, Deposit Value Requested, Payment Value).
This works providing there is only ever one payment, however when more than one payment is received I need to somehow add a summary within the Iff statement that says something like this:
Iff(Sum of Payment value>=Deposit Value Requested, Deposit Value Requested, Sum of Payment value)
Any help?
- Customer Data that only ever contains one row of data for each customer booking, within this table there is a Column called Deposit Value Requested which is manually updated e.g. £50.00
- Payment Data containing multiple rows of data depending on the number of payments received for each booking.
I then have a query that contains all the columns from the Customer Data and a summarised total of the payments received for each booking, this is so that I can see the total payments received for each booking.
I would like to add an additional column to the above query that calculates the value of deposits received. I have tried an Iff formula to establish this e.g. Iff(Payment Value >=Deposit Value Requested, Deposit Value Requested, Payment Value).
This works providing there is only ever one payment, however when more than one payment is received I need to somehow add a summary within the Iff statement that says something like this:
Iff(Sum of Payment value>=Deposit Value Requested, Deposit Value Requested, Sum of Payment value)
Any help?
Comments:
Aug 11, 2008
- Hi DarkDoctrine1. Yes I would like the "Deposit Received" value to appear as a new column within the Access query.
2. Initially I require the calculation to appear within the query.
Thanks
Activeman
Clarification Request
Posted by AccessHelp on Feb 06, 2009
Please post the query you are currently using and I will extend it to solve your problem. You probably could benefit from moving the sum operations to a separte Group By query, and then join your original data with this query, where your iif approach will then work for the aggregated sums no matter how many entries there are.
Posted by DarkDoctrine on Aug 09, 2008
May I clarify your problem:
1. Did you mean that you want to add new fields in your query?
2. Where do you want it to be viewed? In the report, form or in the query itself?
Popular Solutions for Microsoft Office Access 2003
Questions and Unsolved Problems for Microsoft Office Access 2003
Do you recommend Microsoft Office Access 2003 (077-02871) for PC?
Answer
Answer
export report from acces 2007 to excel
Answer
different files. How do I do that
Answer
Hey everyone. Can anyone help me come up with a script that will allow me to import a text file...
(More)
Answer
I am getting the message "Module not found" after deleting a form that I no longer use...
(More)
Ask our Experts
Browse popular Problems
More Common Problems
Most Common Problems for:
For Microsoft Office Access 2003 ...:
Top Business and Pro... Experts
Experience:
I have owned a computer networking sales and service company.
Ask Me
Top Computer & Laptop Repair
Best Business and Productivity
Recommended by Owners
Recommended by Owners
