Access table and excel sheet join

Question

I have to append latest access record to an excel sheet with headers from access table where a column in excel sheet(LAN) matches column (LAN) in access table. This is my code so far:

 qry = "Select (Select Call_Date from Tel_List Where t.LAN=Tel_List.LAN And Ctrl=(Select Max(Ctrl)  From Tel_List Where t.LAN=Tel_List.LAN)) From [Excel 12.0;HDR=Yes;Database=C:\Boda Boda Banja Ltd\Modules\Prototypes\UG RFIN UI 1.0.xlsm].[TEL_List$]t Inner Join Tel_List on t.LAN=Tel_List.LAN Where Ctrl=(Select Max(Ctrl) From Tel_List Where t.LAN=Tel_List.LAN)"

The code returns values but is not appended against the relevant rows. Below is the screen shot after retrieving previous call date from access table.

Is there a way to append the data against the relevant rows?

Are you trying to add record to Access table? Or do you want to update existing record in Access table? Nothing in that SQL will append nor update records. That would require an INSERT or UPDATE action. Or do you want to export data to Excel sheet? That cannot be done with query in Access. — June7
I am not trying to insert or update, I am trying to join an access table and excel sheet using the column 'LAN'. A LAN will occur only once in the excel sheet but could be many times in the access table. Some LANs in the excel sheet maynot be there in the access table. I want to join certain columns. I have done the join, but it is not happening against each LAN. — Jishnu Surendran
Why don't you set a link to worksheet? Then use the link as a table in query. Show sample data as text tables in question. — June7
'Append' usually means add something. I think that for each LAN in Excel you just want to find the 1 latest record in access and report the values of certain fields. The relationship is that 1 record in Excel can have 0,1 or many records in Access. Is that correct ? — CDP1802
Yes. I can use 'for each' loop but as the data is on the network it takes too much time. Further, the db is multi user. — Jishnu Surendran

CDP1802 CDP1802 · Accepted Answer · 2020-02-22T09:32:10

Try

Const WORKBOOK = "C:\Boda Boda Banja Ltd\Modules\Prototypes\UG RFIN UI 1.0.xlsm"
Const SQL = "SELECT e.LAN, Max(a.Call_Date) " & _
            "FROM Tel_List AS a " & _
            "INNER JOIN [Excel 12.0;HDR=Yes;Database=" & WORKBOOK & "].[TEL_List$] AS e " & _
            "ON a.LAN = e.LAN " & _
            "GROUP BY e.LAN;"

Access table and excel sheet join

1 Answers