Thread: Formula in Query (Access 2002)

1. Formula in Query (Access 2002)

I have two tables. In table 1, I have a count (ak) and in table 2, I have two fields (remits) and (undone). In a query, I have both tables connected. How can I have a formula in "undone" so it subtract REMITS from AK? 100 (AK) - 20(remits) = 80 (undone)

2. Re: Formula in Query (Access 2002)

What do you mean by
<< How can I have a formula in "undone" so it subtracts REMITS from AK? >>

Pat

3. Re: Formula in Query (Access 2002)

AK is the number of jobs submitted by a group and is in table 1. In Table 2, We record the number of jobs completed (Remits) by the Document Processing Group.. In Table 2, I have another field called Undone, here I would like to record the number of jobs left to be done. 100 - 20 = 80.

In a Query I tried to enter a formula in the Undone field to get this number. [ak] - [remits] and ([ak] - [remits]). both did not work.

4. Re: Formula in Query (Access 2002)

It is not common practice to store a calculated field in a table. You can run a query (both tables linked in the query), using the formula:
Undone: [ak]-[remits]
and the results will display in the calculated field Undone. I would suggest that you remove the field Undone from the table and just use a query to calculate it as needed.

5. Re: Formula in Query (Access 2002)

There should be no reason why you cannot do that in a query.
Post your query and let's see if we can work it out.
Pat

6. Re: Formula in Query (Access 2002)

In the undone field i tried.

[ak] - [remits] also ([ak] - [remits])

7. Re: Formula in Query (Access 2002)

You will have to excuse my ignorance here, but what do you mean by <<It does not work>>.
Do you mean the query returned no lines, or the query returned nothing in the "undone" column?

8. Re: Formula in Query (Access 2002)

It returns no lines. If I take the formula out, it shows around 1,500 records. Could this be done in a form better?

If not... I will just drop it and continue with something else.

9. Re: Formula in Query (Access 2002)

I would love to see the actual query, the full SELECT statement.

10. Re: Formula in Query (Access 2002)

You put the formula in the first row of the query (the Field row), not the Criteria row, correct?

11. Re: Formula in Query (Access 2002)

That's a good question. I will have to wait until my assit. comes in, in the morning. Never thought about checking that myself.

12. Re: Formula in Query (Access 2002)

They had the formula in the Criteria row. IT works fine.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•