Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Heirarchial query with MSHFLexGrid not working

Avatar of Stevenj2
Stevenj2 asked on
Microsoft AccessVisual Basic ClassicSQL
5 Comments1 Solution512 ViewsLast Modified:
Hello experts,
I am having a problem with a pretty complex query at least its complex to me.
I am using vb6 with an Access DB.    
There are 2 tables, one called Person and one called Pets.
The person table has 2 fields that I am using in the query, they are PatientID and ContactLastName.
The Pet table I am using has 2 fields called PetID and PetName.
I am trying to display in a MSHFlexgrid, one column showing the last Name and PatientID from the person table,  and the Pet name and PetID from the Pets table.  
this is a veterinary application and i want to display how many pets from the (Pets) table are associated to a particular PatientID from the (person) table.
dont be confused my the term patientID,  that is actually the owners of the pets. I guess I should have called it ClientID but I didnt.

Smith          Spot
                   Rover
                   Lassie

Jones          Max
                   Jack
                    Lady
something like that...
Here is the query that I tried,  but had no success:

Dim NameSearch As String     ' name of search string
NameSearch = shape(("SELECT ContactLastName, PatientID From person) WHERE PatientID = 329215917))APPEND((SELECT PetName,PetID FROM Pets WHERE PetID = 'KnoS712')) AS mshflexgrid1 RELATE PetID To PatientID"))
Adodc1.RecordSource = NameSearch
Adodc1.Refresh
End Sub

I think it may be because of my mess of bracketing or something,   I need someone smarter than me, Hope you can help !!!
ASKER CERTIFIED SOLUTION
Avatar of VBClassicGuy
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answers