More

How Can I Pivot a Table in Microsoft Access?

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:

  1. NumberID (Primary Key)
  2. Contacts ID (Foreign Key)
  3. Description (eg: ‘Mobile’, ‘Home’, ‘Work’)
  4. 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?

Steffy Alen

Share
Published by
Steffy Alen

Recent Posts

Seward Alaska Lodging Guide: Best Places to Stay Near Kenai Fjords

Seward, Alaska, is a wide ranging coastal city recognised for its stunning landscapes, rich natural…

6 days ago

Discover the Best Family Hotels in Patong for Your Perfect Family Vacation

Patong, placed within the coronary heart of Phuket, is a destination that gives the precise…

1 week ago

Top Coffee Shops in Barcelona: A Traveler’s Guide to the Best Brews

Barcelona, the cosmopolitan capital of Catalonia, is renowned for its beautiful architecture, vibrant lifestyle, and…

2 weeks ago

How to Get from Naples Airport to Sorrento: Transfer Options Explained

Naples International Airport (NAP), moreover referred to as Capodichino Airport, is the number one gateway…

2 weeks ago

Top 10 Things to Do in Rome – The Eternal City’s Must-See Attractions

Rome, the Eternal City, is a captivating mixture of ancient facts, paintings, life-style, and mouth…

4 weeks ago

Equity Financing: A Comprehensive Guide to Raising Capital for Your Business

Introduction Equity financing is a popular method for businesses, both new and established, to raise…

1 month ago