Desperate 4 help - Parent-Child relationship qry 1

[kchernak]

No, my kid ain't misbehavin'. I have account records that I need to associate with the parent record. the records have a unique record id (12345-26) and a parent ID (12345-18). The parent ID can also be 0 (no parent record) or it could refer to a parent ID that no longer exists. These relationships can go as deep as 6 or more "generations". Here is sample a RAW data set:

parent, child
12345-18, 12345-0
12345-26, 12345-18
12345-34, 12345-26
12345-48, 12345-18
12345-64, 12345-56

I need to ID the ultimate parent in order to have a third column field that reads 12345-18 for the first 4 records above, and 12345-64 for the last record (parent gone)

Is this a VBA routine or a union query or custom function? I cannot get past the 2nd generation level.

Anybody have a clue to help me out?

oh - and spanking didn't help.

Thanks!

 

[GingerR]

this will be complicated, so i have a question first:

when you say a parent can also be zero, what exactly is in the field? just a 0?

my next question, quite smart-assed, is "why on earth do you delete records?!!!" you end up with orphans!!! doesn't seem like a very good design.

in any case, slap me around, answer question #1, and we'll get to it........

 

[GingerR]

wait--another question.
you say -18 is the ultimate parent for the first four records.
but it is a child of -26, which is a child of -34.
so isn't -34 the ultimate parent of the first row?

OR is -48 the ultimate parent, since -18 is a child of -48 also?

 

[willir]

kchernak

GingerR has a point. He / she always does. Watch out for your orphan records.

So is this like the boss is an employee issue?

All employees have their own boss, so you have a "boss" or manager field for the employee table. What makes this relationships different from other "normal" relationships is that the boss or manager field points back to the employee table since the boss is also an employee. The CEO or the president would be his own boss. This scenario is discussed in various relational database and Access books.

If this design fits your needs, you may want to use it.

The caveat to this is if you can have more than one boss. Then you have to add relational table. This table can contain just the father / son foreign keys or add more profile information.

tblWhoIsMyBoss

fld1_boss_id
fld2_employee_id

Both keys point back to the employee key. Both keys together form the primary key for this join table (to prevent duplicates). Of course, your table would be more apptly named tblWhoIsMyDaddy ;-)

Once you have got your relationships worked out, you may find establishing the query a little more easy.

Hope this points you in the right direction.

Richard

 

[kchernak]

Thanks GingerR and willir

I don't think I did a good job of labeling or explaining the relationships - the 1st number (12345-18, 12345-0) I called the parent because it is the unique record identifier - however, I should have labeled the list Child, Parent:

child, parent:
12345-18, 12345-0 (this "child-18" has no parent (i.e. = 0)so it is considered a parent or 'prime' record
12345-26, 12345-18 (this child-26's parent is 18)
12345-34, 12345-26 (this child-34's parent is 26)
12345-48, 12345-18 (this child-48's parent is 18)
12345-64, 12345-56 (this child-64's parent is 56)

So, the presence of a "-0" as in 12345-0 signifies that the record does not roll up to a higher "parent". Thus, 18 is an ultimate/prime parent, 18 is a parent to 26 and 48, while 26 is a parent to 34, and 56 is a parent to 64. There is no record with 12345-56, 12345-0 (referencing ultimate parent). Note: the 18,26,34,48,56 types only represent sequencial record identifiers to the 12345 group, thus any of these 18,26,34, etc could have a parent number of 12345-0.

This 12345-56, 12345-0 record no longer exists - it is not provided by the 3rd party that maintains the accounting system for my employer - we hate them - they do not 'delete' the record, they simply do not provide it - it would make life easier to have it but thousands of dollars and major tooth pulling would be required, and I agree with you - record deletion is an offense punishable by death.

All "child" records can have only one "parent". The child becomes the defactto "parent" if the true parent is gone.

In the end I need to be able to create a table that has a 3rd column containing the parent/primary number as follows:

child, parent, PRIME
12345-18, 12345-0 12345-18
12345-26, 12345-18 12345-18
12345-34, 12345-26 12345-18
12345-48, 12345-18 12345-18
12345-64, 12345-56 12345-56

Any way to do this?

thanks for taking time to help!

 

[willir]

kchernak

Okay, I think I see the big picture better. Basically, you want to assign a value to "PRIME" which will become your new parent reference ID. And I may be wrong, but I suspect you may looking at Accounts and sub Accounts type of thing.

You may be able to accomplish some of this through SQL. I like to preview my data before running an update or select into statement may work where the update uses the highest ID that is lower than your child ID.

And SQL geru may be able to work out a niffty nested series of SQL statements for you, but I prefer to use DAO vb coding to walk through the data...

Dim dbs As DAO.Database, rstA As DAO.Recordset, rstP As DAO.Recordset
Dim strSQL as String, strParent as string, intTest as Integer

Set dbs = CurrentDb()
strSQL = "select * from tblAccounts"
Set rstA = dbs.OpenRecordset(strSQL)

'some code in here if you want

rstA.movefirst

do while not rstA.eof
'logic to grab current ID file
'logic to build SQL statement to find parent
'strSQL = "select min parent_id where ... parent_id > account_id
'For this cursor or record set, you will need to use the "LIKE" to grab the prefix - 1st five characters in your example
'Set rstP = dbs.OpenRecordset(strSQL) 'second record set
'rstP.movefirst
'intTest = rstP.recordcount
'if intTest > 0 then
' logic to find strParent
'else
' lgoic to find strParent
'end if

'logic to test if you found a parent - assign
'failed - used child ID as parent with your suffix "0"

rstA.edit
rstA.Prime = strParent 'as determined from above
rstA.update

rstA.movenext
loop

You can use variations in the loop, but basically go through each record. Use the account prefix to create a 2nd record set and then determine if you have a parent for the current set.

Needless to say, backup the table or database before proceeding.

Richard

 

[GingerR]

ok here you go:
1. make a table called ChildTable. two fields: ChildID and MaxParentID, both text.
2. i'm assuming your main table is called tblAccounts, with fields ChildID and ParentID (text).
3. this function fills up the ChildTable with a unique ChildID and it's MaxParentID. you can then use this table in regular queries (joined on ChildID) to get the max parentID for each row.
4. you can put this function in a module. you have to reference Microsoft DAO 3.6 library. tweak the function as necessary with correct table/field names, and run it.

Function FindMaxParent()
Dim dbs As DAO.Database
Dim rstParent, rstA, rstFinal, rstP As DAO.Recordset
Dim strSQL, strIDToFind, strSQLParent, strSQLFinal As String
Dim intTest As Integer
Dim bolDone As Boolean
bolDone = False
CurrentDb.Execute ("DELETE * FROM ChildTable;")


Set dbs = CurrentDb()

'This opens the final recordset (tblChild) where we'll put all our info
strSQLFinal = "Select ChildTable.* from ChildTable"
Set rstFinal = dbs.openrecordset(strSQLFinal)

'First get unique ChildID's from field ChildID in table tblAccounts
strSQL = "SELECT tblAccounts.ChildID FROM tblAccounts GROUP BY tblAccounts.ChildID;"
Set rstA = dbs.openrecordset(strSQL)

'For each ChildID, search for its parent
While Not rstA.EOF
    rstFinal.AddNew
    rstFinal!ChildID = rstA!ChildID
    strIDToFind = rstA!ChildID
        'Keep looping thru
        Do While bolDone = False
            strSQLParent = "SELECT tblAccounts.ChildID, tblAccounts.ParentID from tblAccounts "
            strSQLParent = strSQLParent & "WHERE tblAccounts.ChildID = '" & strIDToFind & "';"
            
            Set rstParent = dbs.openrecordset(strSQLParent)
                'MsgBox rstParent.RecordCount
                If rstParent.RecordCount = 0 Then
                    bolDone = True
                    GoTo FinishLoop
                End If
                
                If rstParent.RecordCount = 0 Or Right(rstParent!ParentID, 2) = "-0" Then
                    bolDone = True
                Else
                    strIDToFind = rstParent!ParentID
                    bolDone = False
                End If
        Loop
FinishLoop:     rstFinal!MaxParentID = strIDToFind
                rstFinal.Update
                rstA.MoveNext
                bolDone = False

Wend


End Function

 

[GingerR]

oops forgot to close the recordsets--put this before End Function:


rstA.Close
rstFinal.Close
rstParent.Close

also you can get rid of DIM for rstP and intTest. i dont use them--was just copying Richard's stuff to get started.

 

[kchernak]

Thanks willir and GingerR -
I just wanted to thank you both very much for your suggestions even before I try them out. I manually ran a series of unmatched, make table and append queries to get a table of records with the Prime parent so I could at least have something to begin working with to buy me a few days and keep my boss at bay. The tblAccounts is a monthly flat file with about 50 thousand records, and I'll ultimately need to do the same thing going back 20 months and each month going forward, so I'm anxious to work with the suggested sollutions you were kind enough to provide (particularly since I didn't do a very good job of documenting my manual solution in the 1st place).

I'll let you know how I make out.
Thanks!

 

[kchernak]

GingerR

I tried working with your function solution as a function and as a sub routine on a skinnied down test file to 16 records, but the results only produce 1 record in the ChildTable. It appears to end up in an infinate loop with the strIDtoFind stuck on the second record.

any ideas?

thanks

 

[kchernak]

GingerR

OK - my typo in referencing a child ID rather than the parentID when renaming with actual field names.


WORKS BEAUTIFUL!

BIG THANKS!