RE: FN-FORUM: Access database query to add new columns from related table
date posted 1st September 2006 13:25
If I understand you correctly, I think you want to collate multiple order
item rows into a single order row.
I have created a query (below) that does this (note - it is a cut down
version of your own), but please read my comments as I've made a few
assumptions to get this query.
regards,
Dudley
Dudley Trueman
http://www.freelancers.net/dudleytrueman
--------------------
SELECT
[Order Number],
Name,
Company,
Max(Order1ProdRef) AS MaxOfOrder1ProdRef,
Max(Order1ProdDesc) AS MaxOfOrder1ProdDesc,
Max(Order1Qty) AS MaxOfOrder1Qty,
Max(Order2ProdRef) AS MaxOfOrder2ProdRef,
Max(Order2ProdDesc) AS MaxOfOrder2ProdDesc,
Max(Order2Qty) AS MaxOfOrder2Qty
FROM
(SELECT
Order.[Order Number],
Person.Name,
Person.Company,
switch(OrderDetail.OrderSequenceNumber=1, OrderDetail.ProductReference) AS
Order1ProdRef,
switch(OrderDetail.OrderSequenceNumber=1, ProductLinked.[Short
description]) AS Order1ProdDesc,
switch(OrderDetail.OrderSequenceNumber=1, OrderDetail.QuantityOrdered) AS
Order1Qty,
switch(OrderDetail.OrderSequenceNumber=2, OrderDetail.ProductReference) AS
Order2ProdRef,
switch(OrderDetail.OrderSequenceNumber=2, ProductLinked.[Short
description]) AS Order2ProdDesc,
switch(OrderDetail.OrderSequenceNumber=2, OrderDetail.QuantityOrdered) AS
Order2Qty,
Order.[Total Cost]
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]
) AS vw_OrderItems
GROUP BY[Order Number], Name, Company;
The important points to note are:
* This is a cutdown version of your original query and does not contain all
10 columns you mention in your email. You will need to add to this query for
your purposes.
* I create a column for each order item field using a SWITCH statement - so
the first order item appears in the first order item column (the other order
item columns for this row are left blank - i.e. contain NULL)
* The order items must be numbered sequentially (1,2,3...) for each order -
I used [Order Sequence Number] in my example
* The outer query uses MAX() and GROUP BY to *squash* the multiple rows into
a single row
NOTE OF CAUTION
- the order items MUST be numbered sequentially for each order number for
this to work. I used [Order Sequence Number], making the assumption that
this holds these values. If this field doesn't contain sequential values
(starting at 1) for each order then you'll need to create this sequential
numbering yourself before running the query - i.e. use a temporary table,
vba code, additional queries, etc.
- MS-Access may not be able to handle the complexity and size of a single
query when it is extended to 10 columns, so you may need to split the
processing into multiple queries and use macros/vba to run each part of the
process in turn. Unfortunately, it's a case of try it and see.
I hope this helps.
Dudley.
--- Previous message ---
From: Simon Dingley [EMAIL REMOVED]
Reply-To: [EMAIL REMOVED]
To: "FN-FORUM / [EMAIL REMOVED] [EMAIL REMOVED]
Subject: FN-FORUM: Access database query to add new columns from related
table
Date: 1 Sep 2006 08:56:24 -0000
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.
