Stevenj2
asked on
Heirarchial query with MSHFLexGrid not working
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 !!!
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 !!!
Last Comment
ASKER
The patient ID is actually a client ID just called patientID. I made it that way a long time ago and then changed direction. the correlation between the 2 is patientID and PetID, one patient can have many pets with unique pet id's. and I used those numbers only to hard code it into the query to make it work.
And Normally I would use some other input like a text box or something. they are valid PatientID's and Pet ID's.
The only thing I guess i didnt make clear was that I dont kow how to do inner joins.thats why Im here loking for help.
And Normally I would use some other input like a text box or something. they are valid PatientID's and Pet ID's.
The only thing I guess i didnt make clear was that I dont kow how to do inner joins.thats why Im here loking for help.
ASKER
I attached this table to show the patient and pet id's in both tables. think of the patientID being the owner and the petID being the ID's of all the pets owned by a specific owner. I would like to show that in a MshFlexgrid.
Person-and-Pet-database-tables.JPG
Person-and-Pet-database-tables.JPG
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I do appreciate the responses, however i am not experienced in this at all and was looking for some more detailed examples as I stated in my response.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
If there IS a correlation, just use an inner join on the related fields to pull out all pets for a specific patient.