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

Prioritizing Health and Comfort in a Fast-Paced World

In our constantly connected, excessive-stress society, many humans locate themselves walking from one undertaking to…

5 days ago

Why Gut Health Matters for Dogs: Vet-Approved Tips for a Healthy Tummy

When it involves a dog’s ordinary proper-being, intestine health often goes disregarded. Many pet proprietors…

1 week ago

10 Travel Packing Hacks to Make Trekking Easier

Packing for a trekking adventure in Nepal can be a daunting task. With the range…

2 weeks ago

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…

4 weeks 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 month 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…

1 month ago