Re: Help with an SQL join.
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.
Re: Help with an SQL join.
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 ...
Re: Help with an SQL join.
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
Code:
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
Quote:
...
#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
Code:
' 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
Code:
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
Re: Help with an SQL join.
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.
Re: Help with an SQL join.
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.
Re: Help with an SQL join.
Quote:
Originally Posted by sotoasty
....
Select ClientID, ClientName from Clients, Accounts WHERE Clients.ClientID = Accounts.ClientID AND Accounts.AccountType = 'Workers Compensation'";....
:D We did nearly the same in the same time in an totally other place of the world ! :thumb: :wave: Jonny
Re: Help with an SQL join.
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
Re: Help with an SQL join.
The only thing I can spot right off hand would be to add.
combo1.clear
At the top of you fillClientCombo sub.
Re: Help with an SQL join.
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
Re: Help with an SQL join.
Quote:
Originally Posted by 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"
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
Re: Help with an SQL join.
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!
Re: Help with an SQL join.
Quote:
Originally Posted by twistedfrog
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.
Re: Help with an SQL join.
Quote:
Originally Posted by exterminator
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)
Quote:
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.
Quote:
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.
Quote:
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.
Quote:
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.
Re: Help with an SQL join.
Quote:
Originally Posted by sotoasty
...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.
Quote:
Originally Posted by sotoasty
...You should be checking for failures anyway.
Thats what I can agree with.
Quote:
Originally Posted by sotoasty
... 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 :
Code:
SQLCode = "SELECT clients.*, accounts.*, ...
its unnecessary then to have all the data of both available thatfor I said in my post (24.10.05 )
Quote:
...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
Code:
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
Re: Help with an SQL join.
Quote:
Originally Posted by sotoasty
That's what database are designed for. So you can quickly and easily query data to get the results you want.
Yes, that is what databases are designed for but frequent hits to the database can slow up the application. Especially, when the database server is not the same as the one on which the application is running. Now, since this much has been talked of upon the performance issues, I would even suggest using stored procedures in this case instead of using dynamically built queries so that they would help in the faster processsing of the data on the DB server (compilation of query skipped, execution plan once generated re-used etc.)
Quote:
Originally Posted by sotoasty
You should be checking for failures anyway.
Yes :) . I fully agree to it (and also mentioned this in my last post).
Quote:
Originally Posted by sotoasty
And if you have everything in memory, you can't do alot with the data if the network/database fails anyway.
They are sometimes very helpful and fast up the processing on the client side. This is one of the benefits that we extract out of the ADO.NET Dataset objects and the concept of offline databases(Apologies for bring ADO.Net reference in the VB 6.0 forum but the logic holds true.)They are very helpful as far as web-applications go and the concept can be used here as well in a typical client server model. I mean, what is the problem if I load the related data for a form until the time the form lives?
Quote:
Originally Posted by sotoasty
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.
For such serious troubles, yes possibly it won't help but for minor short lived disturbances they can be helpful. Also, when the network traffic on the database increased due to increase in the expected number of client users interacting with the DB. That may be short lived..right? And hence can even lead to deadlocks (I know I being very pessimistic here but yes..there can be occurences of these).
sotoasty, I agree to you on most of the points. But, IMO creating collections of Accounts and Client data seems to be very appropriate (I did not expand much on it but this was the best way IMO and I even mentioned in the end) and we even used this kind of methodology a lot in my last VB 6.0 project that I did about 2 years ago. They are always appreciated in terms of performance. But, yes as JonnyPoet says that these two are the possible solutions and it depends on the OP's understanding which one suits his/her application the best :) . Best Regards.
