Find changed data

[CindySue]

I have a table called LP that has name, address, phone and some other types of information. Periodically I receive a delimited text file that has all current information. I don't want to replace the original table because I want to keep info that may be in a memo or other field that I have added and I want to retain any entries that may no longer be active and therefore no longer included in the new info. I can easily find new entries to add to the LP table using an unmatched query, but is there a way that I can find if a manager name or phone number or some other piece of information has simply changed so that I can make the appropriate updates? Thanks in advance.

[ADezii]

I have a table called LP that has name, address, phone and some other types of information. Periodically I receive a delimited text file that has all current information. I don't want to replace the original table because I want to keep info that may be in a memo or other field that I have added and I want to retain any entries that may no longer be active and therefore no longer included in the new info. I can easily find new entries to add to the LP table using an unmatched query, but is there a way that I can find if a manager name or phone number or some other piece of information has simply changed so that I can make the appropriate updates? Thanks in advance.

Is there a common, Primary Key Field, in both the Delimited Text File and LP Table that uniquely identifies an individual, such as a Social Security Number?

[CindySue]

Yes, a license number field does. (Sorry for the delay in response, I've been out of town.)

[ADezii]

Yes, a license number field does. (Sorry for the delay in response, I've been out of town.)

Don't feel bad, I'm on Vacation myself and was only able to get to this PC for a short interval. Since the [license number] Field uniquely identifies an individual in both the Delimited Text File and the LP Table, this should be a cake walk for you. Follow these instructions, and you should be OK:

1. Import the Delimited Text File data and for the sake of Argument, lets call it Table1.
2. Delete any Records that have no value in the [license number] Field, since they will be of no use to you.
3. Make the [license number] Field in Table1 the Primary Key for the Table (reason for prior deletions).
4. Make sure it is the same Data Type as the [license number] field in your LP Table.
5. Create a Query, then add Table1 and your LP Table to the Query Grid.
6. Create a Join on the [license number] Field in Table1 to the [license number] Field in the LP Table. It will look like this: Table1.[license number](1) ==> [LP Table].[license number](M).
7. Drop down any Fields you want into the Query Grid.
8. Now that the Join exists between these 2 Tables you can easily verify any individuals whose specifrc data has changed for instance: if you want to see if an Employee's Address has change, drop the [Address] Fields from both Tables into the Query Grid and set the Criteria for the [Address] Field in Table1 to <> [LP Table].[Address].
9. Good luck. I don't know if I'll be able to get to a PC again, so if you have any other questions, I'm sure one of the Moderators/Experts will pick it up.

[CindySue]

That worked like a dream. Thanks so much!

[ADezii]

That worked like a dream. Thanks so much!

Any time, CindySue. We are always glad to assist anyone here at TheScripts.