Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009

    Unanswered: Using a query to sum diff field values from two tables based upon a date range

    If this was answered before, then I ask that you forgive my weak search-fu.

    First of all, I'm getting used to Access. However, what I am NOT used to is all the SQL I see flying around. I've tried to adapt other SQL solutions, yet I'm not quite keen enough with it to be able to adapt it without Access yelling at me.

    Moving onto the meat of the question:

    I have two tables that are completely identical in design and field names:
    tbl_complete - Contains data for completed job tickets
    tbl_incomplete - Contains data for uncompleted job tickets

    Once a job ticket is marked as complete, I have an update query that moves (not copies) the record from tbl_incomplete to tbl_complete.

    The names and design of these tables cannot be modified as these tables operate as the core for many other queries and forms already in place. I might be able to get away with modifying certain data types in some fields, but I'd rather not chance it to be honest.

    For the sake of this question, the following three fields from both tables are the only fields of interest in this particular instance:
    - Date
    - MoneyOut
    - MoneyIn

    Another field that might be of interest if it'll do any good is the primary key: TicketID (auto-number)

    When a ticket is initially made, Date and MoneyOut are filled in. However, MoneyIn is left at zero. This ticket goes into tbl_incomplete.
    When the ticket is marked completed, Date and MoneyOut are left untouched, but MoneyIn is filled in before the record is moved to tbl_complete.

    What I am trying to do is make a query that will:
    - Accept a date range (manual or created using a function)
    - Select the records from both tbl_complete and tbl_incomplete that fall in that range
    - Sum the MoneyOut values from the selected records from tbl_incomplete
    - Sum the MoneyIn values from the selected records from tbl_complete
    - Output a single sum from the two above summed numbers

    Ideally I just want a "put in date range, get final sum" type of query. If it can be down to one query, that would be awesome.

    Again, keep in mind that I'm nowhere close to an SQL expert. If anything, I like Access' design interface much better than just typing in text.

    If someone can drop me a copy+paste solution, you would get my thanks, as well as save me a good 10 minutes a day of answering the same stupid question over and over from fellow managers.

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    tbl_complete - Contains data for completed job tickets
    tbl_incomplete - Contains data for uncompleted job tickets
    Sorry for not focusing on your immediate problem, but this sounds too complex. Why not just have a yes/no field in your JOB TICKET table to indicate whether it is complete or not?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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