Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula in Query (Access 2002)

    In the undone field i tried.

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

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Formula in Query (Access 2002)

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

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •