Calculation Queries

[scsuflyboy]

Help Please..I'm about to pull my hair out.

I have a table, called Daily Deposit Info.

The table has fields for the following:

Name
Date
Deposit Amount
ID #
Gate Card #
Prox ID Fee
Mag ID Fee
Gate Card Fee
Parking Permit Fee
and other "Fee" fields.

Here's what I am trying to do:

I have the "Fee" fields set up as Currency format.

I would like to have a field titled "Total Applicant Fees".
I would like this field to be calculated automatically as fees are entered into the table for each applicant. I assume this has to be query but I can't get it to work.

Can someone help?

 

[Golom]

Something like

Select [Date], [Deposit Amount], ...,
       ([Prox ID Fee] + [Mag ID Fee] + ...) As [Total Fees]

From ... etc.

 

[scsuflyboy]

Could you expand on that for me please..I am not an expert
at programming in access

 

[Golom]

Forgot to mention ... its generally not a good idea to retain calculated results in a table because you then need to deal with the issues of keeping them updated if and when one of the components of the calculation changes. Usually we just store base data in tables and use queries to display information to end-users.

 

[scsuflyboy]

Ok, that's what I have attempted to do:

I have the table with all of the input fees.

I created a query based on that table and I get the sum values I am looking for.

Here's my problem:

On my form I want to add a field that shows the Total fees (from the query) but am having problems accomplishing this.

 

[scsuflyboy]

Let me explain further.

The form displays the contents of the table. For some reason I can't display the results of the query in the form because it is based on the table that the form is based on.

Am I making this clear? i hope so.

 

[Golom]

Can I expand on it? Hmmmm ...

What I gave you is the outline of an SQL query that you would use Access's query designer to build. In words the query says

Display the fields I have listed such as Date, Deposit Amount and any others that you want to see from the table (represented by ...). Also compute a new field that will be called [Total Fees] and make its value the sum of the fields Prox ID Fee, Mag ID Fee and any other fees you want in the sum (... again.)

The FROM part will specify the name of the table from which those fields are drawn.

You may optionally have a WHERE clause (not shown) to restrict which records appear in the final result.

 

[scsuflyboy]

Ok, guess I haven't been clear enough.

Here's what I have done:

1. I have a table titled "Daily Deposit Info"

Table contains fields such as Mag ID Fee, Prox ID Fee,
Deposit Amount, etc..plus other fields such as Date
and Name of Person paying the fees.

2. I have a Form based on this table.

I would like to add an additional field on my form that
calculates the sum of all of the "fee" fields.

3. I created a Query of the Daily Deposit Info table to
to sum these fee fields.

My question is: How do I get the results of the query (the sum) into my form so it is displayed with each record?

 

[mkirros]

There are two easy ways I can think of to do this. If the query that has the summary field in it also contains all of the other fee fields from the table, simply link the form to the query instead of the table. Otherwise, you could skip the query altogether, link the form to the table, and have a calculated text box on the form that adds up the fees in the other boxes. The control source for the calculated text box would be Text1 + Text2 + ... where Text1, etc., are the names of the text boxes that hold the individual fee amounts.