Comparring Tables

[John Cunradi]

I have data stored in comma delimited files that represent data for the inventory levels on specific dates.

I would like to create reports and exports comparing the two files. Reports would indicate things like the inventory count for an item on date-X, date-Y and then show the variance between the two.

Would the best way to approach this be to deal with two seperate tables or incoroporate the date-X data and date-Y data into a single table?

I would ideally prefer to have two seperate tables. To make a long story short, because I don't want users to mess up a single file.

Thanks

[JohnZaleski]

RE: Comparring Tables

John,
You could import the data into two tables and create a set linking the two tables "one to one". Link would probably be an expression on both tables "item + cdate(datefield". You could then do a report on this set comparing the amount fields from the two different tables for the same date.

[Tom Cone Jr]

RE: Comparring Tables

I like John's approach, but I think it assumes that there are no new items in the inventory list, and that the inventory list has a single value for each item. (Both are reasonable assumptions, let me hasten to add...)

I wonder if a more general solution would be:

1) Append the data for one date to a temp table
2) Append the data for the second date to a different table.
3) Fetch through the second table, and subtract the inventory count for each item from it's counterpart in the temp table; if there is no counterpart, add it to the temp table.

After the last record in the second table has been processed, the temp table holds a record for each item, and that record contains a 'quantity changed' for each item.