Data from multiple tables and rows to one row.

[kelemvor]

Looking for some help with an advanced Query I need to do for our Online Store.

Here's what I have:

TABLE1 is a list of orders
Data contains customer info (name, address, etc)
Primary field is ORDERID

TABLE2 is a list of Items ordered
Data contains item's ordered.
Contains the ORDERID field to link which item ordered goes with which order.
Primary field is ROW ID

TABLE3 is a list of options specified in the order
Data contains the ROW ID field and ORDERIDfields to link which options go back to which ROW and
ORDER.
Primary field is ROWOPTIONID

Here's what I want to accomplish.

I need to get some data into a spreadsheet format where I can search through the Items Ordered in
TABLE2. Any time I find a specific Product ID, I then want to grab the Customer's Info from TABLE1
using the Order ID field to link the two together.

Then (here's the part I don't know) I want to also grab any Options from TABLE3 that go with the ROW
ID for this order. Problem is there are usually 4 Options in TABLE3 for every one of these products
ordered in TABLE2. So there would be 4 different ROWOPTIONIDs for each ROWID in the table.


How can I get all 4 items from TABLE3 and line them up all on the same Row with the rest of the data?

If this doesn't make sense, let me know. I'd also be happy to converse via direct email if anyone can help me with this.

Thanks!!

 

[kelemvor]

Must not have been clear in that part...

I don't want to search a spreadsheet, I need the data to end up in a spreadsheet format (like the query results screen) once the data has been pulled from the table. Basically I want it to have:
Name, Address, Phone, Item, Option1, Option 2, Option 3...

I can get the first part because they are individual fields, but the Options are the same field in 4 separate records in the table. That's the part I don't know how to do.