It used to be possible to pivot tables in older versions of Microsoft Access (Access 2010). The option to pivot tables seems now to have been removed. Furthermore, the SQL PIVOT operator does not appear to be supported by MS Access. So what are my options if I wish to pivot my data in an Access database?
Specifically, my problem is this:
I have a ‘Contacts‘ table in my Access database which contains names, addresses, etc of my contacts, EXCEPT all phone numbers are stored in a separate ‘PhoneNumber‘ table. This is because I want to give descriptions to each number. So there are four columns in the PhoneNumbers table:
- NumberID (Primary Key)
- Contacts ID (Foreign Key)
- Description (eg: ‘Mobile’, ‘Home’, ‘Work’)
- PhoneNumber
The two tables are inner joined on contactID.
I wish to display all my records from the ‘Contacts‘ table together with the ‘Description’ field from the ‘PhoneNumbers‘ table appended to this dataset.
The entries from the ‘PhoneNumber‘ field of the ‘PhoneNumbers‘ table, I wish to be presented as record entries inside the Description field. This can be done using SQL PIVOT and using MAX(PhoneNumber) as the aggregating function -But, PIVOT does not appear to be supported in MS Access. Am I right? If so, how can I achieve my aim?