Can Excel reproduce an SQL outer join with a formula?? 1

[TomYC]

I think I am not alone in working in an organization that has contracted for a "web-based" database that appears to be unable to handle relational database items well. Thus, a work-around is needed. Right now I am exporting the report results, running a query in Access, then re-importing to my Excel-based reporting schema. I would prefer to do it all in Excel if possible, not least so that I may leave a simple process to my successor(s)! The database outputs Parent and Child ID fields, the former named Parent ID an the latter Unique ID. I need to link up adjacent records where the Parent IDs are the same. The following query appears to work, requiring that I create a duplicate of the table YTD and name it YTD_1, and join the Unique ID (i.e. Child Primary Key) from one to the Parent ID (i.e. Primary Key).

SELECT YTD.[Date], YTD_1.[Parent ID], YTD.[MRN], YTD.[Unit], YTD_1.[Location], YTD.[Risk], YTD_1.[Stage], YTD.[EventType], YTD_1.[When]
FROM YTD RIGHT JOIN YTD AS YTD_1 ON YTD.[Unique ID] = YTD_1.[Parent ID]
WHERE (((YTD.[Event Type])="Pressure Ulcer") AND ((YTD_1.[When])="Acquired"));

Here is a shortened example of some data, from which I'm trying to get output showing that two events are associated with the same patient (MRN="medical record number").

Date Parent ID Unique ID MRN Unit Where Risk When Stage Event Type
03/15/13 EVD16940 NA 12345 4 DSU NA Yes NA NA Pressure Ulcer
03/15/13 EVD16940 af4b8c NA 4 DSU Heel NA Acquired DTI Pressure Child
03/16/13 EVD16940 af4b8d NA 5 DSU Nose NA Acquired Stage 3 Pressure Child

Access gives me the following output, which is exactly what I want:
Date Parent ID MRN Unit Location Risk Stage
1/31/13 EVD16940 12345 4 DSU Nose Yes Stage 3
1/31/13 EVD16940 12345 4 DSU Heel Yes DTI

In Excel (that's why I am posting in the Excel area) I have made some headway, using IF and VLOOKUP formulas; but I don't want to leave anything out. So the basic question is: can Excel handle this parent/child relationship?
T.Y.

 

[zelgar]

the following code should work:

Sub TomYC()
Dim parent As String, risk As String
Dim data As Worksheet, output As Worksheet
parent = "EVD16940"
irow = 2                                                             ' First Row of Data
Set data = Worksheets("Sheet1")                                      ' Name of data sheet
Set output = Worksheets("Sheet2")                                    ' Name of output sheet
o = 2
Do Until IsEmpty(data.Cells(irow, 1))
    If data.Cells(irow, 2) = parent And data.Cells(irow, 4) <> "NA" Then
        unit = data.Cells(irow, 4)
        risk = data.Cells(irow, 8)
    End If
    If data.Cells(irow, 2) = parent And data.Cells(irow, 11) = "Child" Then
        Range(data.Cells(irow, 1), data.Cells(irow, 2)).Copy _
            Destination:=output.Cells(o, 1)
        output.Cells(o, 3) = unit
        Range(data.Cells(irow, 5), data.Cells(irow, 7)).Copy _
            Destination:=output.Cells(o, 4)
        output.Cells(o, 7) = unit
        output.Cells(o, 8) = data.Cells(irow, 10)
    End If
    irow = irow + 1
Loop
End Sub

 

[TomYC]

Thank you zelgar!
I'm going to ponder this one over the next week, and try to implement it for a larger data set than for the example I provided. Thus, I can't specify "parent" in the way that your example suggests.
T.Y.