Pivot table / cross tab query or transform in SQL and Access

I have been working with and orders database and trying to move away from pivot table forms, since they are a little messy, so started playing with the SQL Transform command, which is how pivot tables are implemented in SQL OR perhaps where the original Pivot tables.

The problem I had is the Access only lets you do a Crosstab query with the query wizard on a table or query, and since I was trying to have a faster result I wanted to do just use one query. So I used the main table to create the query (Access automatically displays relation data, so on forms this would be enough) and then one by one I replace the fields with the relational fields I wanted.

So my original query looked like this

TRANSFORM Sum(OrdersTbl.QuantityOrdered) AS SumOfQuantityOrdered
SELECT OrdersTbl.RoastDate, OrdersTbl.CompanyID AS DeliverTo, OrdersTbl.DeliveryById, OrdersTbl.RequiredByDate AS DeliveryDate
FROM OrdersTbl
WHERE (OrdersTbl.Done=False)
GROUP BY OrdersTbl.RoastDate, OrdersTbl.CompanyID, OrdersTbl.DeliveryById, OrdersTbl.RequiredByDate
ORDER BY OrdersTbl.RequiredByDate, OrdersTbl.DeliveryById
PIVOT OrdersTbl.ItemID;

I then started look around on the Internet on how to do the JOINS, and found two great sites:

• SQL - MS Access CrossTab query - across 3 tables - Stack Overflow


• Creating Cross Tab Queries in SQL

Which helped me create the following

TRANSFORM Sum(OrdersTbl.QuantityOrdered) AS SumOfQuantityOrdered
SELECT OrdersTbl.RoastDate, CompanyTbl.CompanyName AS DeliveryTo, OrdersTbl.RequiredByDate, PersonsTbl.Abreviation
FROM PersonsTbl INNER JOIN (CustomersTbl INNER JOIN (OrdersTbl INNER JOIN ItemTypeTbl ON OrdersTbl.ItemTypeID = ItemTypeTbl.ItemTypeID) ON CustomersTbl.CustomerID = OrdersTbl.CustomerId) ON PersonsTbl.PersonID = OrdersTbl.ToBeDeliveredBy
WHERE (OrdersTbl.Done=False)
GROUP BY OrdersTbl.RoastDate, OrdersTbl.RequiredByDate, PersonsTbl.Abreviation, CompanyTbl.CompanyName
ORDER BY OrdersTbl.RequiredByDate, PersonsTbl.Abreviation
PIVOT ItemTypeTbl.ItemDesc;

The thing to not here is that the web sites I found had the syntax of the cross tab wrong for Access, I kept on getting Syntax Error (missing operator) in query expression ....

A few things to notice is that the field names in the select and the feild names in the order by are not the aliases.

So I actually used the designer, deleted the links that where not required, and was able to create the query above. So now I am going to use it programatically. So why did I blog this, perhaps some poor sole will be saved some work if they find it (perhaps a future me for example ;))