Help required with reverse crosstab query

Hi, I have an Access 2010 database and Im trying to display some data vertically rather than horizontally, Ive googled around and it seems some variation of a reverse crosstab query may work, but I cannot figure it out.

So I have 2 tables called OrderItem and MaterialRequirement that are joined by OrderId.  In the OrderItem table I need to display the [OrderId], [DateRequired] fields and from the MaterialRequirement table I need to display [MaterialType], [Depth], [Width], [LengthA], [LengthB], [LengthC], [LengthD], [LengthE] and [LengthF].  But I need it to display like this:

OrderID | DateRequired | MaterialType | Depth | Width | LengthType | Length

1 | 01/03/2018 | 1 | 100mm | 300mm | A | 6/824
1 | 01/03/2018 | 2 | 100mm | 200mm | B | 9/820
1 | 01/03/2018 | 3 | 100mm | 300mm | C | ANC
1 | 01/03/2018 | 4 | 100mm | 300mm | D | 4/1200
1 | 01/03/2018 | 5 | 100mm | 300mm | E | 6/824
1 | 01/03/2018 | 6 | 100mm | 300mm | F | CTRN
2 | 03/03/2018 | 1 | 500mm | 200mm | A | SWN
2 | 03/03/2018 | 4 | 450mm | 200mm | B | 812
2 | 03/03/2018 | 7 | 600mm | 300mm | C | 41
2 | 03/03/2018 | 8 | 200mm | 300mm | D | 211
2 | 03/03/2018 | 9 | 200mm | 300mm | E | 1334
2 | 03/03/2018 | 1 | 200mm | 300mm | F | 1300

Needs to be grouped on OrderID and sorted by LengthType

Hope you can help.

Thanks.
palsopAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
You need a UNION as core query. E.g.

SELECT Q.*
FROM
(
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthA], "A" AS LengthType FROM yourTable
    UNION ALL
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthB], "B" AS LengthType FROM yourTable
) Q
ORDER BY Q.OrderID,
    Q.LengthType;

Open in new window

0
 
palsopAuthor Commented:
Hi,

Thanks for getting back to me.  So should this work?

SELECT Q.*
FROM
(
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthA], "A" AS LengthType FROM OrderItem INNER JOIN MaterialRequirement ON OrderItem.OrderID = MaterialRequirement.ParentID;

    UNION ALL
    
SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthB], "B" AS LengthType FROM OrderItem INNER JOIN MaterialRequirement ON OrderItem.OrderID = MaterialRequirement.ParentID;

) Q
ORDER BY Q.OrderID,
    Q.LengthType;

Open in new window

0
 
ste5anSenior DeveloperCommented:
No. There is a severe syntax error: You terminate a SQL statement by using the semi-colon. But as we want a sub-query you need to remove them. E.g.

SELECT Q.*
FROM
(
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthA], "A" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
    UNION ALL
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthB], "B" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
) Q
ORDER BY Q.OrderID,
    Q.LengthType;

Open in new window


Please us always table alias names and specify them for all columns also. Can you explain your data model? Cause it looks like that the JOIN could be done outside the sub-query.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
palsopAuthor Commented:
Thanks, how would I add the other Lengths?  LengthC, LengthD, LengthE and LengthF?

By now you have realised that Im not very good at access.
0
 
ste5anSenior DeveloperCommented:
By repetition...

SELECT Q.*
FROM
(
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthA], "A" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
    UNION ALL
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthB], "B" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
    UNION ALL
    SELECT [OrderID], [MaterialType], [Depth], [Width], [LengthC], "C" AS LengthType
    FROM OrderItem OI
        INNER JOIN MaterialRequirement MR ON OI.OrderID = MR.ParentID
) Q
ORDER BY Q.OrderID,
    Q.LengthType;

Open in new window

0
 
palsopAuthor Commented:
Thanks.  Almost there, last thing is how do I get it to display it like the attached screenshot
0
 
palsopAuthor Commented:
Capture.PNG
0
 
ste5anSenior DeveloperCommented:
By adding the columns to each sub query in the union block.
0
 
PortletPaulfreelancerCommented:
For each length_type seen in that image, you need a subquery within the union query (A to J inclusive):
SELECT
      oi.*, Q.*
FROM (
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthA] AS Length, "A" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthB], "B" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthC], "C" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthD], "D" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthE], "E" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthF], "F" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthG], "G" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthH], "H" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthI], "I" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthJ], "J" AS LengthType FROM MaterialRequirement MR
     ) Q
INNER JOIN OrderItem OI ON Q.OrderID = OI.OrderID
ORDER BY
      Q.OrderID
    , Q.LengthType
;

Open in new window

If columns after [LengthF] don't exist, just output NULL instead
SELECT
      oi.*, Q.*
FROM (
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthA] AS Length, "A" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthB], "B" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthC], "C" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthD], "D" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthE], "E" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], [mr.LengthF], "F" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], NULL, "G" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], NULL, "H" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], NULL, "I" AS LengthType FROM MaterialRequirement MR UNION ALL
      SELECT [mr.OrderID], [mr.MaterialType], [mr.Depth], [mr.Width], NULL, "J" AS LengthType FROM MaterialRequirement MR
     ) Q
INNER JOIN OrderItem OI ON Q.OrderID = OI.OrderID
ORDER BY
      Q.OrderID
    , Q.LengthType
;

Open in new window

0
All Courses

From novice to tech pro — start learning today.