Convert Multiple Rows of Data into 1 Row (1 Viewer)

[Robecca]

I am designing a bead ornament database for my mom to track inventory of beads, inventory of finished ornaments and cost/pricing.
I have a table that joins the Item and bead part with the quantity of each bead part needed, it has the following fields: ItemMatlID, ItemID, BeadPartID, Qty
So I have multiple rows of ItemID’s for all the BeadPartID & Qty’s.
Now I need a query with 1 line per Item and all BeadParts and their quantities. However, I need the BeadPart to be a row data and not a column heading. So a crosstab is out, I believe since it wants to make the BeadPart the column heading and not data in the query.

 

[plog]

Can you povide a sample of your data in the format it is currently in and then what you want it to ultimately look like?

 

[Robecca]

Since my data is all numbers from foreign fields due to being a join table to bring the item and bead parts together. I’ve substituted my numbers with the data.

I realize that I can’t have repeating column headings with same, but that is part of my not knowing what to do …

Thank you for taking time to look at this.

 

[plog]

Sorry, I can't think of a way to accomplish exactly what you want. Or even come close. You might have to spit this out to Excel and maybe pivot table it.

 

[Robecca]

Thank you for your time!

 

[redalert]

Hi Robecca

I have developed a small database, see attached, to do what I think you need.

You will need to get your data into the Item,Beadpart and Quantity fields in the tbl_BeadOrnament table. Ignore the other fields.

Open the form "frm_ProcessData" and select the "Process Data" button.

The code will create a table with the required number of columns in it and convert the data for you.

Let me know if it needs changing in any way.

 

[Robecca]

That does it perfectly. Thank you! I'll have to dive into the code to see how you got it to work. I really appreciate this!

 

[redalert]

I'm glad that it did the trick.

It uses a number of techniques which might be useful to you in the future.