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.
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
I think it may be because of my mess of bracketing or something, I need someone smarter than me, Hope you can help !!!