Help with an SQL join.

[twistedfrog]

Hi guys,

I am trying to get all of the clients out of a database table where that client has an entry in the accounts table AND that entry is of a type Workers Compensation. I have this working rather nicely. HOWEVER I have 2 questions that go along with this:

#1 I get everything EXCEPT the clientID. How do I get the clientID to come with the rest of the information?

#2 I am taking the client information and putting it into a combo box ..... then I want another combo box to populate with all the account information related to the client the user clicked on in the first combo box. Is there any way to do this with one recordset?

Matt

[twistedfrog]

Oh here is my SQL code .... remember this gets all the information I want EXCEPT the clientID ..... and I don't know how to populate the two combo boxes.

' fill the client box and make it visible
SQLCode = "SELECT clients.*, accounts.* FROM clients, accounts WHERE clients.clientID = accounts.clientID AND accounts.accountType = 'Workers Compensation'"


Thanks again.

[M Owen]

MyCombobox.RowSourceType=Table/Query
MyCombobox.RowSource=SQLCode

#1 : Where is the ClientID? In which table?

#2 : Not really. I wouldn't advise it ... What I would do is populate the Account combo with all the accounts and then upon selection of the client, set the account combo to the client combo in the AfterUpdate event of the client combo ...

Recap: 2 queries. 1 for the client 1 for the accounts ...

[JonnyPoet]

I think you have to change your sql a bit use a left ( or right ) join connection. Easiest way ist open your mdb and
1) draw all the connection which you have in that db you can do that in menue extras-> connections.
2) Use the querys and the sql wizzard there to get the correct sql

In the end this should look like

SQLCode = "SELECT clients.*, accounts.*, accounts.accountType 
FROM clients left join accounts ON clients.clientID = accounts.clientID 
WHERE accounts.accountType = 'Workers Compensation'" 
ORDER By clients.clientsID

3) Then in the database you can look for yout reasults and you will find
all fields which have identical names like client ID called with their full name as accounts.clientID and a second time also as clients.clientID

So when using a recordset to readout data you have there
rs!fieldA, rs!fieldB... rs!accounts.clientID ... or rs!clients.clientID

...
#2 I am taking the client information and putting it into a combo box ..... then I want another combo box to populate with all the account information related to the client the user clicked on in the first combo box. Is there any way to do this with one recordset?

Basically you can use the output of your sql for a recordset and populating your clients combo with that and then in the combo afterUpdate messagehandler looping through your recordset checking the clientsID and put it e.g. into variable myClientsID

' first loop to the first item with your ID
Do Until rsClients = rs!clients.clientID 
	DoEvents
loop
' then loop through all the needed items
Do until rs!clients.clientID <> myClientsID
... Fill data into anything combo, list whatever
 
loop

Hint: If I would be the user I would prefer to have a MsFlexgrid here for
all the accountdata.

If you dont want to do this looping then you need to use twos qls but then it is not necessary to have all data from account in the first sql
only use

SQLCode = "SELECT clients.ClientName, clients.clientID 
FROM clients left join accounts ON clients.clientID = accounts.clientID 
WHERE accounts.accountType = 'Workers Compensation'" 
ORDER By clients.clientsID

I hope that helps

Jonny Poet

[Pinky98]

Sorry are you saying you aren't getting the ClientID and you want to, or that you are getting it and you DON'T want to? Because you should be getting it...

If you don't want to return it, then you can't use the wild card (*) you have to stipulate evey column individually.

[sotoasty]

I would have to agree with Mike. Separate in to 2 queries. Also, dont pull all the info from either table.

As an example.

Select ClientID, ClientName from Clients, Accounts WHERE Clients.ClientID = Accounts.ClientID AND Accounts.AccountType = 'Workers Compensation'";


Then When you know the ID of the selected in the first combo...

Select AccountID, AccountName from Accounts WHERE ClientID = " & mvarSelectedID

to populate your second combo box.

When dealing with Database, especially when looking at initial programming, try to think where the data will end up. I start something very similar to this 4 years ago. The design spec listed about 50 Clients with about 10 accounts each. Well, now we have about 1000 clients and and several of them have up to 100 accounts.

Now if I pull 1000 clients and each of the accounts, and all the data from each record, well that is a lot of data.

As to your initial question, the reason you can't find the CleintID is because there are actually 2 of them. You are pulling Clients.*, Accounts.* Joining on Accounts.ClientID = Clients.ClientID. Your field names are probably Client.ClientID and Accounts.ClientID instead of just ClientID.

Using your original SQL statment, if you want an easy Identifier try.

SQLCode = "SELECT clients.clientID as CLID, clients.*, accounts.* FROM clients, accounts WHERE clients.clientID = accounts.clientID AND accounts.accountType = 'Workers Compensation'"


And use CLID as the field name.

[JonnyPoet]

....
Select ClientID, ClientName from Clients, Accounts WHERE Clients.ClientID = Accounts.ClientID AND Accounts.AccountType = 'Workers Compensation'";....

We did nearly the same in the same time in an totally other place of the world ! Jonny

[twistedfrog]

Hey guys - thanks for the help. I've got 2 recordsets working and things are much simplified and more efficient I think.

HOWEVER,

I've got a strange problem. When I populate the clientCombo box it duplicates everything. I.e. I get two entries in the combobox with the same information for every 1 entry in the database. Ideas? .... oh yeah how do you get your code to post into a code box like your suggestions....

Here is the code:

commandHolder = "SELECT clients.*, accounts.clientID FROM clients, accounts WHERE clients.clientID = accounts.clientID AND accounts.accountType = 'Workers Compensation' ORDER BY lastName"

Set MyRecSet = MyConn.Execute(commandHolder)
fillClientCombo clientBox
clientBox.Visible = True


Sub fillClientCombo(Combo1 As ComboBox)
With MyRecSet
Do Until .EOF
Dim clientName As String
If (MyRecSet("middleName") <> "N/A") Then
clientName = MyRecSet("lastName") & ", " & MyRecSet("firstName") & " " & MyRecSet("middleName")
Else
clientName = MyRecSet("lastName") & ", " & MyRecSet("firstName")
End If
Combo1.AddItem clientName
Combo1.ItemData(Combo1.NewIndex) = MyRecSet("clientID")
.MoveNext
Loop
End With

MyRecSet.MoveFirst
Combo1.Visible = True

End Sub

[sotoasty]

The only thing I can spot right off hand would be to add.

combo1.clear

At the top of you fillClientCombo sub.

[jp140768]

Have you tried:

commandHolder = "SELECT Distint clients.*, accounts.clientID FROM clients, accounts WHERE clients.clientID = accounts.clientID AND accounts.accountType = 'Workers Compensation' ORDER BY lastName"


HTH

JP

[JonnyPoet]

Have you tried:

commandHolder = "SELECT Distint clients.*, accounts.clientID FROM clients, accounts WHERE clients.clientID = accounts.clientID AND accounts.accountType = 'Workers Compensation' ORDER BY lastName"

Hint: Do your query into the query section of the mdb and test the sql there directly Look if the items are twice therein. So you then will know if they are twice because of the sql or because we have overseen something in the logically steps like sotoasty mentioned clearind the combo before filling.

Have you checked to do left or right join as I mentioned in my post before ?

Jonnny Poet

[twistedfrog]

Hey guys - thank you very much.

The final solution was adding DISTINCT to the sql query. Something screwy happened in the WHERE clause that doesn't make sense to me, but all is well now. Gracias!

[exterminator]

Hey guys - thanks for the help. I've got 2 recordsets working and things are much simplified and more efficient I think.

This is a solution but not a good solution and cannot be more efficient ever. I would suggest going ahead with what JonnyPoet suggested. What you are doing here is seperating out a query (using joins as shown in Jonny's post) into two fragments. Now, you are needing two recordsets. See the expense you have to pay for this:
1. You need to hit the database twice when you could have had done this with a single query. And the database has to return resultsets twice as well.
2. You will have to loop through both the recordsets when using the join solution you could have had done this in a single looping over one single recordset object.
3. If query 1 succeeds and query 2 fails, you will have to take preventive measures and manage transactions and in this case you will un-necessarily cause network traffic out of the first queries resultset that could have had been avoided. I mean they would be meaninglessly be there in the memory when you don't have the complete corresponding records for them that you should have had got after the 2nd query. (Managing transactions is something you should do anyway)

Using the join is the right way to go. What kind of join you have to use (INNER or OUTER) depends on your requirements. I hope I did not misunderstand your questions and needs/requirements. Regards.

//EDIT: Using collection classes for Clients and Accounts could prove helpful. Also, if the list of clients and accounts is not that huge then its better to store them in memory rather than hitting the DB again and again for different clients to get their account details.

[sotoasty]

This is a solution but not a good solution and cannot be more efficient ever. I would suggest going ahead with what JonnyPoet suggested.

Actually, if I understand what he is doing, it is the more correct solution.
Based on the info that has been given, let's take a look at the table structures.

Client:
ClientID INT autonumber
ClientName Text;

Accounts:
AccountID INT autonumber,
AccountName text;
AccountType Text;
ClientID INT

Our Data:
Client Data:

ClientID ClientName
1 C1
2 C2
3 C3
4 C4

Account Data:
AccountID AccountName AccountType ClientID
1 Test1 WC 1
2 Test2 WC 1
3 Test3 IN 1
4 Test4 IN 2
5 Test5 WC 2
6 Test6 WC 2
7 Test7 IN 3
8 Test8 WC 4

Now our results, using the following Query.

SELECT DISTINCT Clients.*, Accounts.*
FROM Clients RIGHT JOIN Accounts ON [Clients].[ClientID]=[Accounts].[ClientID]
WHERE ((([Accounts].[AccountType])='WC'));

CID CN AcctID AcctN AcctType Accot.CID
1 C1 1 Test1 WC 1
1 C1 2 Test2 WC 1
2 C2 5 Test5 WC 2
2 C2 6 Test6 WC 2
4 C4 8 Test8 WC 4


Notice we still have Multiple Client Name (C1, C2)

What you are doing here is seperating out a query (using joins as shown in Jonny's post) into two fragments. Now, you are needing two recordsets. See the expense you have to pay for this:

1. You need to hit the database twice when you could have had done this with a single query. And the database has to return resultsets twice as well.

That's what database are designed for. So you can quickly and easily query data to get the results you want.

2. You will have to loop through both the recordsets when using the join solution you could have had done this in a single looping over one single recordset object.

We are looking at 2 different combo boxes. You are going to loop twice anyway. Once to fill the Client Combo, then once the client is selected, once to fill the account combo.

3. If query 1 succeeds and query 2 fails, you will have to take preventive measures and manage transactions and in this case you will un-necessarily cause network traffic out of the first queries resultset that could have had been avoided. I mean they would be meaninglessly be there in the memory when you don't have the complete corresponding records for them that you should have had got after the 2nd query. (Managing transactions is something you should do anyway)

You should be checking for failures anyway. And if you have everything in memory, you can't do alot with the data if the network/database fails anyway. Also, as I pointed out earlier, when you start to add clients and accounts, the data you are going to start pulling across the network will far outway the traffic of the queries. Not to mention, you will now have the latest data. And you won't have to worry about those pesky refreshes. More network traffic.

Using the join is the right way to go. What kind of join you have to use (INNER or OUTER) depends on your requirements. I hope I did not misunderstand your questions and needs/requirements. Regards.

//EDIT: Using collection classes for Clients and Accounts could prove helpful. Also, if the list of clients and accounts is not that huge then its better to store them in memory rather than hitting the DB again and again for different clients to get their account details.

Although your clients/accounts data my not be very large, events many times overtake the original estimates. Be optimistic in your data projections.

[JonnyPoet]

...Notice we still have Multiple Client Name (C1, C2)

Yes but thats really easily to handle with an if statement in the loop checking if client has changed or not, so to get it multiple is not the problem.

...You should be checking for failures anyway.

Thats what I can agree with.

... And if you have everything in memory, you can't do alot with the data if the network/database fails anyway.... More network traffic.

I think both solutions are possible and OK. Its depending on prediction how the database will exeed and the prediction of how many data will be the result of a query, which solution you should choose. So if you are sure you have lots of data to manage within your query then two querys are maybe the best solution and if you only await to get a few data as a result then one query will be better. For example I have a database with 26000 customers where every customer has only a few bills and the customers are also in groups of different interests then in a special query you maybe will only await 8 customers and all of them have aproximativ 6 bills maximum this will result in 48 records then you maybe are better with one query if in the same database you do a query which will result in 500 customers with also 6 bills this will end up in 3000 recordsets so its maybe better to have two different querys even when you think that the second query has to be done 500 times ( for each customer ). Reason: Better timemanagement to part the big data-amount in parts and less data to be in memory. But what I have had my attention on (in an earlier post) was, that if you do two queryies, you dont need all data of both tables ( accounts and clients ), so dont do :

SQLCode = "SELECT clients.*, accounts.*, ...

its unnecessary then to have all the data of both available thatfor I said in my post (24.10.05 )

...If you dont want to do this looping then you need to use two sqls, but then it is not necessary to have all data from account in the first sql where you only nned to find out the clients data.
So only use

SQLCode = "SELECT Distinct clients.ClientName, clients.clientID 
FROM clients left join accounts ON clients.clientID = accounts.clientID 
WHERE accounts.accountType = 'Workers Compensation'" 
ORDER By clients.clientsID

So IMHO I think make your assumptions about the possible output of your query and then decide how you want to design it.

Jonny Poet