An old table with old information, a flat file with new information and a query.

[OldPete]

Ok! I'm going to work hard to keep this organized, informational, and as short as possible.

I have a table with product information.

The table has 18 fields. Of those 18 fields, 7 fields contain information relevant to the flat file I'm going to discuss.

The data on this table is 2 years old.

The newer data WAS on a previously saved version of this table, and has been lost due to operator error. (failed hard drive, unrecoverable).

HOWEVER, the data *IS* available on a delimited flat file that was made via Access by a query. The data on the flat files is 100% current, and is the data that I need to repopulate the fields with on the old table.

An added note: The old table has 1951 records and the flatfile has 2167.

Additionally, the actual information in all the other fields has changed also (price, description, etc.)

So, I guess the big question is, how can I repopulate the information? So far I've done a few things. I've imported the flat file and tried to mess around with copy/paste and a few other things... but nothing is working to replace the old information with the new information from the flat file.

Ok! Am I in heap of trouble or does anyone have any ideas?

Thanks for any help!

Pete

 

[Beetle]

Ok! Am I in heap of trouble....

Maybe, maybe not. Can you provide any more details about the actual fields/data in the old table and the flat file? Is there a Primary Key value in each record set that could be used to identify/match rows?

 

[OldPete]

Maybe, maybe not. Can you provide any more details about the actual fields/data in the old table and the flat file? Is there a Primary Key value in each record set that could be used to identify/match rows?

Beetle, thank you kindly for the reply!

The old table has an "ItemID" field that is the key.

The old table has the following fields:

(Had a screen capture of the fields but system won't let me post it... let me know if I can get it to you another way (PM?)

Of those fields, the ones that need to be updated/added/changed are:

ItemId <------ KEY
Item Category
Price
Short Description
Full Description
Item Options(s)
More Desc Info

^^ Not exact names, but should make sense...

Thank you for giving me hope!
Pete

 

[OldPete]

If you go to photobucket and search by user "funnypete" you'll see the image there in the first spot.

 

[Beetle]

OK, but what about the flat file? Does it also have an ItemID column and do these ItemID's still match the ItemID's in the old table (i.e. data in the other fields has changed but the PK values are still the same)?

 

[OldPete]

OK, but what about the flat file? Does it also have an ItemID column and do these ItemID's still match the ItemID's in the old table (i.e. data in the other fields has changed but the PK values are still the same)?

Sorry I didn't give you more information. Yes. The flat file has an item ID file and yes, they are the same value. The only difference is that there are 1951 records in the old table and the flat file has 2167.

But, for example, item #1000 is the same in both files (since the flat file was generated as a query from the table.

 

[Beetle]

So are you just wanting to insert the additional 216 records into the old table, or do you want to update all the old records with new info plus insert the additional 216 records?

 

[OldPete]

So are you just wanting to insert the additional 216 records into the old table, or do you want to update all the old records with new info plus insert the additional 216 records?

I want to update all the old records with new info plus insert the additional 216 records.

Thank you for all your help!

 

[OldPete]

I'm reading about "append queries" -- anyone know if this is the right path to achieving my goal?

 

[JANR]

I want to update all the old records with new info plus insert the additional 216 records

You can use an UPDATE query with an OUTER JOIN, joined on the ItemID. That will update old records AND Insert new records, see this link: http://www.access-programmers.co.uk/forums/showthread.php?t=211711

JR