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:

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


Popular Posts