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

date posted 1st September 2006 15:27

Dudley Trueman wrote:
>
> If I understand you correctly, I think you want to collate multiple
> order item rows into a single order row.

That is correct.

>
> 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.
>
> --------------------
>
> 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;

Woosh straight over my head......I need to sit down and try and get my
head around your query and also take on board your comments. Thanks for
taking the time on this and I will let you know how I get on. I feel a
large amount of trial and error will be required on this one.