Microsoft Access - Access Query - Asked By Avinash Naik on 07-Jul-12 02:37 AM

I have a access database which contains a table called " Customer database", it contains following columns :

Customer database
Contact number	Name	REASON
123456789	A
987654321	Z
85467321	F
879878985	J
111111111	L
22222222	O

and on the other hand i gets a status report from the client in excel which is as follows :

Status report
Contact number	REASON
123456789	NOT REPLIED
22222222	WRONG NUMBER
111111111	will reply tommorrow
879878985	accepted

I want the "REASON" COLUMN of EXCEL should automatically get transferred to the "REASON" COLUMN of ACCESS database. and it should be exactly the same reason with the corresponding contact number.


Can you help me out for this Query?

How do you want to bring the Excel file in:  Select it in a dialog, hard-coded path, attached table?

And, if it isn't an attached table, how would you identify the range:  a set of columns on a specific sheet, named range?

You will need to import the spreadsheet.  You can then create an update query that joins the spreadsheet to your Access table on the contact number and update the reason code.

This is of course fraught with issues. 
1.  You can't create an update query using a linked spreadsheet since Access cannot update linked spreadsheets and any non-updateable element of a query will render the query non-updateable.
2. You have no definitive key to join on so the contact number is being used as the join field.
3. The implication of the data is that there are multiple contacts so how will you control which Access record gets updated with the spreadsheet data?  An unqualified query will update all matching rows.