Listing of Transactions from Two Tables

[eric333]

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.

 

[jmd0252]

You could create a view in say SQL, and when you create the view, you do a "UNION", which combines all entries from table1, and all entries from table2 into a brand new file.. Would that give you what you need??

 

[eric333]

Yes, I'm absolutely open to creating a View that combines the tables. I've created basic Views in the past, but never something like this. Do you have any tips to offer on how to create such a View?

 

[lbass]

You can use a command as your sole datasource, set up like this:

select table1.field1, table1.field2
from table1
where <your criteria>
union all
select table2.field1, table2.field2
from table2
where <your criteria>

The fields you want to merge must be in the same ordinal position in the Select statement. Use "union" instead of "union all" is you want to eliminate duplicate rows.

-LB