SQL in VBA Excel to query multiple tables in Access database

[dwsteyl]

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

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;"

 

[Legacy 456155]

Do you have control over the design of your tables? Basic normalization would remove the over-complication here. Why are your clients broken out into different tables? For now, assuming that you don't have any control over the design, you should use outer joins. Unless ClientCode is in every table selected, you should not be getting any return at all using equa-joins. You mentioned a field named "ITActive." I don't see that in your query. How do you typically use that field?

See if this helps you at all.

SELECT Clients.ClientCode, Clients.RegNameSurname, Clients.Initials, Clients.IBNr, IT2019.TaxStatus, IT2018.TaxStatus, IT2017.TaxStatus, IT2016.TaxStatus, IT2015.TaxStatus
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
WHERE (((Clients.IBManager)='IM'))
ORDER BY Clients.ClientCode;

 

[JonXL]

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.

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.

From what you describe I would tackle the problem starting with a UNION query of all the IT tables - thus treated as a single table - that you then join to the Clients table.

Once joined, select only the fields you need and group by those fields to eliminate the duplicates.

In all, you'd have three queries:

1. The UNION query of the IT tables
2. The JOIN query connecting the Clients table to the UNION query and filtering out the records you don't want
3. The aggregate query selecting the few fields you want from the JOIN query using GROUP BY to eliminate any duplicates

 

[dwsteyl]

dataluver,

Your Select statements worked flawlessly first time! Genius.

I am fairly new to SQL and understand the basis with regards to selecting data from at least two tables. Because of lack of knowledge I do not yet understand the more complex Join commands etc, so I will have to study much more into various commands and what they do. I just knew it must be possible since the client code is used in all tables as the primary keys and should thus be available to use in a single report to see client statuses as an overview over five years.

Thank you for the assistance!

dwsteyl

 

[dwsteyl]

JonXL

Thank you - what you suggest makes sense. I will look into that to further my knowledge.

Regards
dwsteyl

 

[JonXL]

JonXL

Thank you - what you suggest makes sense. I will look into that to further my knowledge.

Assuming it works (since I didn't test it for your business requirement) an approach like that is easy to maintain in code. You can build the UNION SQL in a loop as your table names increment logically.

For xIT = 2015 to 2019
    sqlUnionIT = sqlUnionIT & "SELECT [ClientCode], [TaxStatus] FROM IT" & xIT
    sqlUnionIT = sqlUnionIT & " UNION ALL "
Next xIT

As you add tables, getting them accounted for in the code is as easy as changing 2019 to 2020, then 2021, and so on.

To get fancier, you can use a simple loop along with a check for whether a table exists that will build the SQL without you ever needing to modify it no matter how many tables you add.

 

[dwsteyl]

JonXL,

That makes a lot of sense. Thank you very much for the time you took to bring this under my attention! I will definitely use that somewhere in my reporting modules.

It is great when experienced members take the time to pass down knowledge and experience. Thank you!

Regards
dwsteyl

 

[Legacy 456155]

Glad we could help. Just a tip. I would rather spend twice as much time attempting a good design than to later spend 4 times as much time querying against a poor design. I am not criticizing your (or someone else's) design, but just lending a hand to help you save LOTS of trouble down the road. The design of your tables breaks normal form. Look up normalization. It's worth the read to understand at least up to 5th Normal Form though, frankly, getting to 3rd Normal Form will be "good enough" for the majority of desktop databases.