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:
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.