Compare records with Excel Sheet (1 Viewer)

[tt1611]

This is more of a pick your brains but lets assume a table Devices exists in my database and 2 of the 8 fields in that table are Device Serial and Device User.

I also have a master xls sheet with a number of columns which also includes these 2 fields ie device serial and device user.
What would you suggest as the best way of comparing my two fields against the two in excel.

DLookup comes to mind (much the same as I would have used a VLookup in excel).

I want a datasheet where access compare results from my table against the xls master list and if it finds a difference between my records and the xls, flag it by generating all fields from mine and excel in some kinda datasheet.

Do you have any suggestions in this?
Thanks in adavance for your help

 

[James Dudden]

If the Excel file and table also have the same ID field then you could link the Excel file in to Access. Then create a query with the linked excel file and your table joined on the id field. Now add all the columns you need to see and then in a new column add something like this:

iif(LinkedExcelFileName!DeviceSerial <> TableName!DeviceSerial,True,False)

You can then filter on this new column to see any anomalies in your data.

 

[tt1611]

Hey James
Although I know there wer probably 50 ways of doing this, I am actually liking the linked excel version the most.

I attempted with creating a transferdata macro but that had too many arguments that were at this stage variable. The link I have created allows me to use this sheet as though it were a table.
The only question per se I have is that I have saved this excel file in a location say c:\users\my_user\Documents with a name Master.xls. The sheet can change every so often I am hoping at least with the same row headings. If I saved new files with this same name ie Master, you dont envision any problems with the link though right?

I know the queries may be affected but I

 

[James Dudden]

Yes, there should be no problem in saving new versions of the master Excel file over the old one as long as you keep the same column headers and refresh the link.