UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Part Number Hierarchy (parent-child Relationship)    
 
   
tinkythomas
post Feb 6 2011, 10:13 AM
Post #1

UtterAccess Guru
Posts: 538
From: England



Hi,

I have a requirement by part number to display the Parent-Child relationship. The table structure (tblLocations) is as follows...

Field Description
PROJID............Project ID
IPP..................Project provisioning number
CAFIG.............Catalog figure number
ITM.................Item number
ISN.................Item sequence number
IND.................Indenture
MFC.................Manufacturers code
PNR.................Part number

The indenture field gives the level in the breakdown so for example the following...

BRH, K0058, 050001, 000, 00A, 1, U7941, EA3200B007-011
BRH, K0058, 050001, 001, 00A, 2, U7941, EA3200V005-041
BRH, K0058, 050001, 002, 00A, 3, U7941, EE350-01
BRH, K0058, 050001, 002, 00A, 3, U7941, EE350-05
BRH, K0058, 050001, 001, 00A, 2, U7941, EA3200V009-043

Part numbers EE350-01 and EE350-05 are children of EA3200V005-041.

I hope that makes sense? I have several thousand part numbers (Parents) and need to identify their child parts.

Not sure of the best way to approach this, so any help would be appreciated.

Regards,
Go to the top of the page
 
+
Jeff B.
post Feb 6 2011, 12:02 PM
Post #2

UtterAccess VIP
Posts: 8,548
From: Pacific NorthWet



Given that set of sample data, I didn't see how I'd know (from the data alone) that the third and fourth records "belonged" to the second.

If you were giving this data to an intern, how would you explain how these are connected?
Go to the top of the page
 
+
tinkythomas
post Feb 6 2011, 05:44 PM
Post #3

UtterAccess Guru
Posts: 538
From: England



Thanks for the reply. I'm sorry I have noticed an error in the sample data. The item number field (4th field) is incorrect, it should be...

BRH, K0058, 050001, 000, 00A, 1, U7941, EA3200B007-011
BRH, K0058, 050001, 001, 00A, 2, U7941, EA3200V005-041
BRH, K0058, 050001, 002, 00A, 3, U7941, EE350-01
BRH, K0058, 050001, 003, 00A, 3, U7941, EE350-05
BRH, K0058, 050001, 004, 00A, 2, U7941, EA3200V009-043

However to answer your question, the third and fourth records are children of the second because they are 1 indenture (3) below. The final record is indenture 2 which means it is a child of record 1, indenture 1.

Hope that makes sense?

Regards,
Go to the top of the page
 
+
Jeff B.
post Feb 6 2011, 11:53 PM
Post #4

UtterAccess VIP
Posts: 8,548
From: Pacific NorthWet



Sorry, still not following.

Is there a chance that you are taking the relative position of the records in your sample/example as part of the data? Remember that Access records in a table are NOT in any particular order. If the data that connects the records is not part of the records, they aren't connected.
Go to the top of the page
 
+
tinkythomas
post Feb 7 2011, 04:23 PM
Post #5

UtterAccess Guru
Posts: 538
From: England



QUOTE (Jeff B. @ Feb 7 2011, 04:53 AM) *
Sorry, still not following.

Is there a chance that you are taking the relative position of the records in your sample/example as part of the data? Remember that Access records in a table are NOT in any particular order. If the data that connects the records is not part of the records, they aren't connected.

Jeff, yes the sample data shows the records sorted by fields 2, 3, 4, 5 and 6. I don't know how else to explain this but will try. The data is essentially a BoM (Bill of Materials) that shows the levels in the breakdown (Indenture field). I want to query the table to return the assemblies, the sub-assemblies and piece parts.

The second record in the sample is an assembly and the third and fourth records are sub-assemblies. I know this because of the levels in the breakdown. Record 2 is level 2 and records 3 and 4 are level 3.

Sorry if this still doesn't explain what I'm trying to achieve.

Regards,
Go to the top of the page
 
+
Jeff B.
post Feb 8 2011, 09:41 AM
Post #6

UtterAccess VIP
Posts: 8,548
From: Pacific NorthWet



I understand the concept of 3 being below 2, and so on.

What happens when there's more than one "parent"? How do you know that a particular "3" belongs to a particular "2"?
Go to the top of the page
 
+
tinkythomas
post Feb 9 2011, 05:27 PM
Post #7

UtterAccess Guru
Posts: 538
From: England



QUOTE (Jeff B. @ Feb 8 2011, 02:41 PM) *
I understand the concept of 3 being below 2, and so on.

What happens when there's more than one "parent"? How do you know that a particular "3" belongs to a particular "2"?

Sorry for the late reply, I have been away on business. To answer your question "How do you know that a particular "3" belongs to a particular "2"?"...

The records are are ordered by field(4) the item number, which is basically the top-down order in the breakdown.

Regards,
Go to the top of the page
 
+
tinkythomas
post Feb 14 2011, 12:38 PM
Post #8

UtterAccess Guru
Posts: 538
From: England



Jeff,

I believe what I need to do for this to work is to add a field for the parent item number. So what I would end up with is...

BRH, K0058, 050001, 000, 00A, 1, U7941, EA3200B007-011, 000
BRH, K0058, 050001, 001, 00A, 2, U7941, EA3200V005-041, 000
BRH, K0058, 050001, 002, 00A, 3, U7941, EE350-01, 001
BRH, K0058, 050001, 003, 00A, 3, U7941, EE350-05, 001
BRH, K0058, 050001, 004, 00A, 2, U7941, EA3200V009-043, 000

The final field in the above example being the new field (parent item number) taken from the item number field(4).

I could then create a self-join query joining on fields(4) and (9). This would give me the parent and the children?

Could you please help out with code to create the new field? I think the easiest approach would be to iterate through a recordset?

Any help or suggestions would be greatly appreciated.

Regards,
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 19th April 2014 - 09:54 AM