Hello everyone,
I have two tables, let's call them tbA and tbB.
In tbA I keep track of the number of invoices added to the system for each location on a given date and in tbB how many invoices were processed for each location on a given date. My primary keys for both tables are composite keys of location and date (no location can have more than one entry per date).
For sake of simplicity, let's say both tables are identical in structure and contain the following fields:
aDate (date)
Location (integer)
Invoices (integer)
Now I would like to create a query that shows me the backlog (invoices added minus invoices processed) for each location given a certain date range. At first I tried to create a query where I join the group fields and the location fields from the two tables and calculate the Invoices from "added" minus invoices from "processed", but then I realised there is no guarantee that there will be entries in both tables for the same dates, so that won't work.
What would be a good way to solve this? My end goal is to have a form with a subform showing all locations and their backlogs. There will be textboxes to fill in the date range to which the calculations should apply.
Any help is appreciated.
I have two tables, let's call them tbA and tbB.
In tbA I keep track of the number of invoices added to the system for each location on a given date and in tbB how many invoices were processed for each location on a given date. My primary keys for both tables are composite keys of location and date (no location can have more than one entry per date).
For sake of simplicity, let's say both tables are identical in structure and contain the following fields:
aDate (date)
Location (integer)
Invoices (integer)
Now I would like to create a query that shows me the backlog (invoices added minus invoices processed) for each location given a certain date range. At first I tried to create a query where I join the group fields and the location fields from the two tables and calculate the Invoices from "added" minus invoices from "processed", but then I realised there is no guarantee that there will be entries in both tables for the same dates, so that won't work.
What would be a good way to solve this? My end goal is to have a form with a subform showing all locations and their backlogs. There will be textboxes to fill in the date range to which the calculations should apply.
Any help is appreciated.