Skip to main content

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

Comments

Popular posts from this blog

Migrating QuickBooks to Sage One Cloud accounting - Part 1 Exporting the data

Some notes Sage means sage one online accounts, wherever we say Sage we mean Sage one. The QuickBooks we used was version 2012 Professional. But most of the information is similar. What you need > You need the templates from Sage and the data from QuickBooks, see below for how to do these. Get Import Templates from Sage To get the templates for the items go to help.accounting.sageone.co.za/en_za/accounting/from-your-previous-accounting-system.html Although you can construct the templates from the information in this post you can download samples of the templates need. Below are the links they provide: Use the following downloads which are referenced in the guide: General Ledger Accounts Import Template Customer Import Template Customer Outstanding Invoices Import Template Supplier Import Template Supplier Outstanding Invoices Import Template Item Import Template   Another useful page that is hard to find on their system is: help.accounting.sageone.co.za/en_za/ac

Bitcoin / Cryptocurrency – what is it and how can I benefit

What is it I started investigating Bitcoin when it was worth just over $1000 a bitcoin. I was interested in what it was and how it worked. A lot of people are saying we missed the boat, but I believe that everyone should at least try put a little money in now, or at least use a faucet (see below) to make a little micro-currency. You can read a Wiki article about bitcoin and its history etc. But what you need to know is that it is a currency, that is independent of country. No one really knows who invented the concept of a cryptocurrency since the person who published the paper used a nom de plume. All new cryptocurrencies work more or less the same way as Bitcoin. So as I explain below I interchange these terms. Bitcoin is the original cryptocurrency. How Bitcoin works The currency releases a coin based on a mathematical formula. There will never be more than 21 million bitcoins (other cryptocurrencies do not work like this). Each bitcoin can have divided into one hundred mil

Mindfullness Meditation and Depression - in a pod cast

Over the last 3 years I have found that meditation has really assisted me, in resolving the depression I have had. I have planned to write about my experience for a while, and hopefully will get around to doing that. However today having listen to the latest podcast by Dan Harris on 10% happier, so many things just clicked in to place, so I want to share it. To understand what is covered in the podcast I would recommend you understand what mindfulness meditation is, and what the default mode network in the brain is (see links below). Listen to the pod cast - but here are some extracts (which I do not have permission to publish - and will remove if asked). Link to podcast: tumello.com/listen/H11a5NYJf; or itunes.apple.com/us/podcast/10-happier-with-dan-harris/id1087147821 Chuck Raison, a psychiatrist and a professor of psychiatry at the University of Wisconsin-Madison School of Medicine and Public Health, and Vlad Maletic, a clinical professor of neuropsychiatry and behavior sc