Compare two tables

[vavs]

I am importing data from a csv file into Access. Each import represents a set of data about orders. It is actually an 830 for those who know EDI. I am going through the raw data and parsing everything to create a record in Access. I have one record for every different date in the file. For example, I will have a requirement of 240 widgets on 6/1/2011, 250 widgets on 6/8/2011, no widgets on 6/15/2011 etc. The requirements are further broken down into planned and actual quantities. I need to do a comparison of the new data to the latest old data. My thought is this, I can do my import into a temporary table called 830temp. Then do a comparison of this table to my 830 table to determine what if anything has changed. Some of the suppliers send the 830 to me everyday even if no change to the schedule has occurred. I have a field in the 830 called issuedate. This is the date the 830 was issued by the supplier. I also have an order #, part # and PO# to match.

What I don't know how to do is to compare the 830 temp record with the 830 table. I thought I could somehow check the first record for a match and if it did move to the next record. If a match was not found, I would like to indicate what record was affected. I would also have to do this in the reverse. It is possible that as I showed in the example above, the 250 widgets due on 6/8 got moved to 6/15. In this case, I would need to see that in the 830temp table I have a new record on 6/15 and on the 830 table I no longer have a 6/8 record.

I hope that someone can point me in the right direction. I can provide a sample of the data to anyone who needs it.

 

[boblarson]

Does the new data have a key which can be used to ensure that the record it has is looking at the correct one in the existing table? Tables don't store data in any particular order - think of it like a big bucket where things are just thrown in. So, two things - one, unless there is something to be able to sort on to consistently match the two sets of data, there isn't really a way to compare them because an import doesn't necessarily keep the same order either (as I said - bucket).

 

[vavs]

I do not have a key on the table. I know that they are helpful especially in situations like this but because I am getting data from 4 different sources and consistency is a major issue, I have eliminated it from the table. I have found two columns that can create what I want. I can select MAX([IssueDate]) from 830 where [PO#] = PO

When I do this in the database as a SQL query, I get the oldest issue date.

Now what I need to do is use this to create another table that has all of the records where the date and the PO match this query. In the file that is being imported, several PO's may be included. So for example my query would need to check the oldest date based on 4,5,6 or whatever number of PO choices.

I can create a list of PO choices by using select Distinct([PO#]) from 830temp but I don't know how to make this a variable then.

What I would like to do is this:

Select distinct([PO#]) from 830temp as POList
Select MAX([IssueDate]) from 830 where [PO#] is in the POList as MDate
create a temp table that would return * from 830 where [IssueDate] = MDate and [PO#] = POList joined with * from 830temp where [PO#] = POList, order by [DueDate]

This will create a table that has the information from the 830 table with the latest date and the new 830 based on the same PO#.

I think that I can create a report or display that will show where any differences lie.

Hope this makes sense.