The horrors of Data Shaping

[bubarooni]

I am trying to do this in the data environment (vb 6 sp 5) using parent and child commands. Each command is a sql statement.

I have four tables (simplified here for clarity):

Injections
injID
mdID
patID
hhcaID

Doctors
mdID
mdFName
mdLName

HHCA
hhcaID
hhcaName

Patients
patID
patFName
patLName

There are a bunch of other fields to boot but these are the ones I am after I think. The Injections table is the only place where all info is tied together through the ID's.

Anyway, I am after a report that will show me:

Each Doctor and under Doctor I need each HHCA (home health care agency) associated to the doctor and under HHCA each patient associated to the hhca.

kinda like this:

Doctor1
HHCA1
Patient1
Patient2
HHCA2
Patient3

Doctor2
HHCA2
Patient4
HHCA3
Patient5
Patient6

I can get close but the HHCA's will repeat themselves over and over. My command objects don't have the right relationships defined in the relationship tab.

I have three commands:
Parent = Doctor Info (Doctors Name and injID)
Child1 = HHCA Info (HHCA Name, hhcaID, injID)
relation to Parent = injID
Child2 = Patient Info (Patient Name and hhcaID)
relation to Child1 = hhcaID

What should the sql code be (roughly) and the relationships.


I have been stuck on this since summer and think maybe data shaping is beyond my ability to comprehend.

Anyone willing to try and point me to the promised land???

Thanks In Advance,



PS. If it helps, here is the entire horrid ado hierarchy and the shape command as shown under Hierarchy Info for the parent command:

rsDocHHCAPat
Doc2hhca
HHCA2Child

SHAPE {SELECT DISTINCT [Doctors].[mdID],
[Doctors].[mdFName], [Doctors].[mdLName], [InjectSchedule].[isDoctor], [InjectSchedule].[isChartNo] AS ChartNo1, [InjectSchedule].[isHHCA] AS DocToHick
FROM Doctors INNER JOIN InjectSchedule ON [Doctors].[mdID] =[InjectSchedule].[isDoctor] ORDER BY [Doctors].[mdLName]} AS DocPatHHCA APPEND (( SHAPE {SELECT DISTINCT [HHCA].[hName], [InjectSchedule].[isDoctor], [InjectSchedule].[isHHCA], [InjectSchedule].[isChartNo] AS ChartNo2
FROM HHCA INNER JOIN InjectSchedule ON [HHCA].[hID] =[InjectSchedule].[isHHCA] ORDER BY [HHCA].[hName]} AS Doc2hhca APPEND ({SELECT DISTINCT [InjectSchedule].[isChartNo] AS ChartNo3, [InjectSchedule].[isPatID], [InjectSchedule].[isHHCA] AS Child2Hick, [InjectSchedule].[isDoctor] AS Doc, [Patients].[pFName], [Patients].[pLName]
FROM Patients INNER JOIN InjectSchedule ON [Patients].[pID] =[InjectSchedule].[isPatID] ORDER BY [Patients].[pLName]} AS HHCA2Child RELATE 'isHHCA' TO 'Child2Hick') AS HHCA2Child) AS Doc2hhca RELATE 'ChartNo1' TO 'ChartNo2') AS Doc2hhca

 

[SpiderBear6]

Sorry mate.. I can't help you but I have to say that the subject of thread brought a smile to my face.

"Data Shaping - the devil reborn"

 

[bubarooni]

Well, misery does love company! Thanks for the sympathy.

 

[avanderlaan]

bubarooni,

i have 2 questions.

1) are you ONLY interested in patients who have received injections?

2) do you only want 1 ENTRY per patient no matter how many injections a patient may have received?

 

[bubarooni]

Hi avanderlaan,

1. Actually, I want anyone who has an injection record created. Sometimes they will pull out of the program before they receive their first injection. I still need them to show though because I need to know who referred them to us.

2. Yes, I only want 1 entry for them.

Is that where my problem is?

Thanks

 

[pbxdave]

There are a few ways to accomplish what you are after but here is where I would start:

You will need 3 record sets.

1 for the doctor
2 for the HHCA
3 for the paitent


Select Doctor_ID from doctors

While not eof rs.doctors

Select HHCA_ID from HHCA where Doctor_ID = rs.doctors!doctor_id
While not eof.hhca

Select Patient_ID from Paitent where HHCA_ID = rs.hhca!hhca_id
While not eof paitent
Do work here!!
Now you have each paitent for that doctor and hhca etc…
Wend
Wend
wend

close rs.doctor
close rs.hhca
close rs.paitent


The concept can be saved in a SQL stored procedure using temp tables

I could go on and on but let me know if this helps.

david

 

[pbxdave]

One more thing..
What database are you using?

david

 

[pbxdave]

Just wipped this in access:
And this works.

SELECT injections.injID, patients.patFname, patients.patLname, doctors.mdFname, doctors.mdLname, hhca.hhcaName

FROM ((injections INNER JOIN patients ON injections.patID = patients.patID) INNER JOIN doctors ON injections.mdID = doctors.mdID) INNER JOIN hhca ON injections.hhcaID = hhca.hhcaID;

Where (what ever you want.. hhca or md or paitent ets)

Order by (what ever you want..)

I will email you the mdb if you want.

david

 

[bubarooni]

I am using Access 2000. I will go ahead and try these suggestions first.