Access Query

[abdullahsikandar]

Hi,

I need a favor from you guys, I already made a button in Access, I need to import the files from excel through that button on Macro, whenever we click the button it have to be import the excel files in database by matching the two columns.

here is the example

Database:

Col A Col B Col C

A B
CC DD
EW FA
MK SD


Excel Sheet

Col A Col B Col C

A B AA
CC DD AA
EW FA AA
MK SD AA


So when we are importing the file at that point if we found "A" in COL A and "B" in COL B so paste all the data in COL C. Same thing gonna be happen in all the fields

After importing an Excel files it will check for the column to match and import the data. The result will be like this:

Database:

Col A Col B Col C

A B AA
CC DD AA
EW FA AA
MK SD AA

Kindly help me in this query.

Thanks in Advance

 

[TerryHogarth21]

I'm not sure how your database is set up and what your import does but I'm interpreting that below is what you want.

1) Your button imports data from excel(maybe it's a specific sheet, maybe multiple) into a say Table A.

Did you already put code into your button? I'm assuming you used the DocMD.TransferSpreadsheet methodology?

2) You already have Table B in Access

3) Create a join on both those fields in Table A and Table B then do an update query on Table B where both those fields match by setting Col C in Table B to whatever Col C is in Table A

UPDATE TableA INNER  JOIN TableB ON (TableA.ColB = TableB.ColB) AND (TableA.ColA =  TableB.ColA) SET TableB.ColC = [TableA].[ColC];

Also is it possible to have multiple records in Col A and Col B in Excel sheet equal to each other? Say (ColA, ColB) A B with a value of 7 and A B with a value of 10.

 

[abdullahsikandar]

I'm not sure how your database is set up and what your import does but I'm interpreting that below is what you want.

1) Your button imports data from excel(maybe it's a specific sheet, maybe multiple) into a say Table A.

Did you already put code into your button? I'm assuming you used the DocMD.TransferSpreadsheet methodology?

2) You already have Table B in Access

3) Create a join on both those fields in Table A and Table B then do an update query on Table B where both those fields match by setting Col C in Table B to whatever Col C is in Table A

UPDATE TableA INNER  JOIN TableB ON (TableA.ColB = TableB.ColB) AND (TableA.ColA =  TableB.ColA) SET TableB.ColC = [TableA].[ColC];

Also is it possible to have multiple records in Col A and Col B in Excel sheet equal to each other? Say (ColA, ColB) A B with a value of 7 and A B with a value of 10.

Yea, Thanks for your reply:D i got it thank you so much :D