Table Organization

[Guitarcam87]

So I don't even know where to begin on this one so I figured I would go ahead and post to see what you folks may know about it! Here we go...

What I Have:

I have an excel table that provides product part numbers, Description, The components within the part number and the component part numbers. The problem is they are all imported in individual rows. (See Below)

Row 1: Mat#-123456, Desc-Product1, Comp#-987654, CompDesc-Comp1
Row 2: Mat#-123456, Desc-Product1, Comp#-147852, CompDesc-Comp2
Row 3: Mat#-123456, Desc-Product1, Comp#-369852, CompDesc-Comp3

What I Need:

A way for Access to search for a Mat# (123456) and return Desc(Prodcut1), and all the components and their descriptions (987654 Comp1, 147852 Comp2, 369852 Comp3)

Here's The Catch:

The number of components varies.

I have no idea where to begin so any help is appreciated! Thanks!

 

[Guitarcam87]

Oh I should probably mention how I need it to be organized!

I need each Mat# and all it's components to be organized as individual records.

ie: Mat#123456, Desc, Comp1#147852, Comp1Desc, Comp2#369852, Comp2Desc, etc

 

[Guitarcam87]

I need to be able to recall the components when selected on a form. For instance the user will select the Material # and I want to components to populate their approriate Field text boxes. I've done that with a database I created on a much smaller scale but each Material number was an individual record. I have to import this list (which comes out to about 5000 individual record for about 3000 unique material numbers) as it is and need it to do the same thing.

Thanks for your reply!

 

[Guitarcam87]

Thank you for the responses Pat I will give that a shot.

 

[Guitarcam87]

Pat

I am having trouble getting the result I need. This may be a lot to ask for but could you walk me through it? I'm still new to access and I'm struggling with this one.

 

[Guitarcam87]

I have attached a small portion of the database in question. There are 5 part numbers represented with varying numbers of components in each to make 19 total records. I want to convert this table in some way (be it query or whatever) to a format where each part number and it's components are represented by 1 record for a total of 5 records. Any help is appreciated.

 

[Guitarcam87]

Thanks for the advice I'll check it out and let you know how it goes!