FN-FORUM: Access database query to add new columns from related table

date posted 1st September 2006 09:41

My client has come to me with an access database from an Actinic(which
is a piece of shite) E-Commerce site. They would like me to export
customer orders into a spreadsheet for them so that there is one line
per order which is simple until I get to the point of adding order
items. My query so far is below but obviously creates a new line per
order item rather than one line per order with each order item in its
own column. Can anyone advise on how to get the order items for the
order into their own columns so that I eventually end up with columns
like OrderItem1, OrderItem2, OrderItem3 etc. It is safe to say that
there are no orders with more than 10 items.

SELECT Order.[Order Number], Person.Name, Person.Company,
Person.[Address Line 1], Person.[Address Line 2], Person.[Address Line
3], Person.[Address Line 4], Person.[Postal Code], Person.[Address
Country], Person.[Phone Number], Person.[Email Address],
OrderDetail.ProductReference, ProductLinked.[Short description],
OrderDetail.QuantityOrdered, Order.[Total Cost], Order.[Date Ordered]
FROM (([Order] LEFT JOIN OrderDetail ON Order.[Order Sequence Number] =
OrderDetail.OrderSequenceNumber) LEFT JOIN Person ON
Order.InvoiceContactID = Person.ContactID) LEFT JOIN ProductLinked ON
OrderDetail.ProductReference = ProductLinked.[Product reference];


Hopefully my explanation is clear enough - if not please ask.

--
Cheers, Simon