I have a series of transactions for inventory items that are stored in two different tables. An update is made each time the inventory item has a status change. The most current status (i.e., the most recent status change) is in Table1. When the status has changed, a new entry is made in Table 1 for that item and the prior entry is moved into Table 2. So, Table 1 always has the most current status for each item and table 2 has the history of status changes.
I need to develop a report that lists all types of transactions with specific status code changes for a date range (e.g., prior week), but does not differentiate as to whether or not the status change exists in Table 1 or Table 2.
The two tables are joined by three fields (all inner joins). All three fields are components that make up the item in the database. The fields specific to the status change in Table 1 and Table 2 are the same - it's just that Table 1 has only one record for each inventory item and Table 2 can have unlimited number of records for each inventory item. The fields are:
Date
Time
Status Change Code
Comments
Thanks in advance for any assistance that anyone can offer.
I need to develop a report that lists all types of transactions with specific status code changes for a date range (e.g., prior week), but does not differentiate as to whether or not the status change exists in Table 1 or Table 2.
The two tables are joined by three fields (all inner joins). All three fields are components that make up the item in the database. The fields specific to the status change in Table 1 and Table 2 are the same - it's just that Table 1 has only one record for each inventory item and Table 2 can have unlimited number of records for each inventory item. The fields are:
Date
Time
Status Change Code
Comments
Thanks in advance for any assistance that anyone can offer.