Combining fields from multiple records into one record

[Eric@Gopher]

I have 55,000 rows of order data, some orders may have up to 8+ rows associated based on each item that was for that order. For example:

Order # Cust # Contact Name Date Order Type Item # Item Desc Qty List Price
3215616 10003 Will Biles 9/1/11 New Biz 68-55 Tuna Hook 1 114.1
3215616 10003 Will Biles 9/1/11 New Biz 68-57 Tuna Remover 1 72.32
3215616 10003 Will Biles 9/1/11 New Biz 68-64 Tuna Cleaner 4 8.57

What I want to do is compare the order number and then based on that move the item#, Item Desc, Qty, and List Price for each to a new column along with the Order #, Cust #, Name, Date, and Order Type to create on column of data for each order number but with all the items information. See Below

Order# *Cust#*Name*Date*Type *Item # *Item Desc* Qty* Price *Item# *Item *Desc *QTY *Price *Item# *Item Desc *QTY * Price

I would really appreciate the help! Thank you!

[orange]

You might want to write down in plain English what question(s) you are trying to answer.
You have an un-normalized concoction of Orders; OrderItems and Products.
The structure is typically seen with older non database systems. It appears that you have a text file or spreadsheet that you are trying to analyze.

Could you tell us the source of this info?
If you want to supply more info, I'm sure someone will assist. But if you're going to use Access with this data, I strongly suggest you put it into a designed database, and we can help with that.

Good luck with your project no matter what you decide to do.

You might get some insight from this (you may have to ignore Customer and a few other pieces)

http://www.databaseanswers.org/data_...ders/index.htm

[Eric@Gopher]

I want to append Item Number, Item Description, Quantity ordered, and List Price to an existing record. The order number is unique but there may be several instances of each order number: See Below for current view

Order # Cust # Contact Name Date Order Type Item # Item Desc Qty List Price
3215616 10003 Will Biles 9/1/11 New Biz 68-55 Tuna Hook 1 $114.10
3215616 10003 Will Biles 9/1/11 New Biz 68-57 Tuna Remover 1 $72.32

I Would like it to look like this:

Order # Cust # Contact Name Date Order Type Item # Item Desc Qty List Price Item # Item Desc Qty List Price

3215616 10003 Will Biles 9/1/11 New Biz 68-55 Tuna Hook 1 $114.10 68-57 Tuna Remover 1 $72.32

Is this clearer?

[orange]

The order number is unique but there may be several instances of each order number

This might make sense in a spreadsheet, but not in a database.
Why are you attempting to do this in Access if you don't intend to set up normalized tables?

The output you are wanting is un-normalized. You could get that output from a query, but you would have to use Normalized table structures.
see http://allenbrowne.com/func-concat.html