SQL in VBA Excel to query multiple tables in Access database

  • Hi guys,


    I have an Access database with the following tables - Clients, IT2019, IT2018, IT2017, IT2016 etc. A client can exist in multiple IT... tables, or just one (depending on when a client joined). All tables use the field ClientCode to identify clients (This is the primary key in all tables). The table Clients also has a Yes/No Boolean field ("ITActive") that is marked Yes if a client is present in one of the IT... tables. It also has a field named "IBManager" that indicate a manager responsible for the client.


    I use Excel VBA to run SQL queries and display them on a spreadsheet (this works flawlessly for mutliple other queries).


    I am having trouble with the following query. I need to select a IBManager from Clients table, and then select all clients for IT2019, IT2018 that is marked as ITActive (in Clients table) etc that is managed by that Manager. I then want to see the status of each client, which is in a field "TaxStatus" in each IT.... table.


    I hope the above explanation will be sufficient to understand what I am trying to do.


    The code below currently runs, but generates about 82,000 results, instead of only 130 clients that is currently being managed by manager "IM".


    I need data to be shown in recordset as ClientCode, ClientName, IT2019.Taxstatus, IT2018.Taxstatus, etc. This should show a simple spreadsheet showing all statuses for all clients managed by the chosen manager.


    Thank you for your help!

    dwsteyl

    Code
    1. sQRY = "SELECT Clients.ClientCode, Clients.RegNameSurname, Clients.Initials, Clients.IBNr, IT2019.TaxStatus, IT2018.TaxStatus, IT2017.TaxStatus, IT2016.TaxStatus, IT2015.TaxStatus FROM IT2019, (((Clients INNER JOIN IT2018 ON Clients.ClientCode = IT2018.ClientCode) INNER JOIN IT2017 ON Clients.ClientCode = IT2017.ClientCode) INNER JOIN IT2016 ON Clients.ClientCode = IT2016.ClientCode) INNER JOIN IT2015 ON Clients.ClientCode = IT2015.ClientCode WHERE (((Clients.IBManager) = 'IM')) ORDER BY Clients.ClientCode ASC;"
  • I suggest you build the query in Access using the UI. Then copy the SQL code. I use the attached program to set the code properly in VBA. Copy the SQL statement you built in the U/I and paste into this program.

  • I suggest you redesign your Access database, which sounds fundamentally flawed. :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Hi guys - thank you for the feedback. AlanSindman - great tool you have there. Will definitely use that. Thanks!


    Below is the solution to my question. Worked flawlessly the first time. I know my query is complex, but the database design is fine. There is no redundant data, and with my less than adequate knowledge of SQL I just needed some help from the masters.


    SQL
    1. SELECT Clients.ClientCode, Clients.RegNameSurname, Clients.Initials, Clients.IBNr, IT2019.TaxStatus, IT2018.TaxStatus, IT2017.TaxStatus, IT2016.TaxStatus, IT2015.TaxStatus
    2. FROM IT2019 LEFT JOIN ((((Clients LEFT JOIN IT2018 ON Clients.ClientCode = IT2018.ClientCode) LEFT JOIN IT2017 ON Clients.ClientCode = IT2017.ClientCode) LEFT JOIN IT2016 ON Clients.ClientCode = IT2016.ClientCode) LEFT JOIN IT2015 ON Clients.ClientCode = IT2015.ClientCode) ON IT2019.ClientCode = Clients.ClientCode
    3. WHERE (((Clients.IBManager)='IM'))
    4. ORDER BY Clients.ClientCode;

    Thanks once again for your suggestions!


    Regards

    wesselsteyl