Simple (for y'all) Access/Excel question

Factual Questions
1

Access/Excel because they’re the tools I have…

I have a table with the following fields:

First Name
Last Name
Phone Number

(Other fields too, but these are the important ones for this exercise)

I then have the following data pattern throughout the table:

John
Smith
210-555-1234

Mary
Smith
210-555-1234

Cecil
Adams
210-000-1111

Jane
Adams
210-000-1111

Kid
Adams
210-000-1111

I essentially want to create a table/report that combines the names according to the field “Phone” so that I end up with:

Name1, Name2, Name3 Phone

John Smith, Mary Smith, (blank), 210-555-1234
Cecil Adams, Jane Adams, Kid Adams, 210-000-1111

I don’t have a problem doing this in either program, just whatever you think is easier/better.

Thanks in advance for your help!

2

A PivotTable is a super fast way to do this as long as:

  1. The formatting can be a bit flexible (like phone number, last name, firstnames) instead of the way you listed it
  2. Your phone numbers are all formatted the same way so that 123-123-1234 is the same as (123) 123-1234

Example:

3

(I edited it after posting, so just refresh it to see the latest version as of 12:34 PM PST)

4

Man, there’s nothing I can do that makes my table look like that. For some reason, once I make “Phone” my Row Label, all my entries become column headers… and the various instructions I see on the web show the same result.

So instead of getting column headers like “Phone”, “Name”, “Address” I get “Phone”, “John Smith”, “Mary Smith”, “Cecil Adams”, “Jane Adams”, etc… Actually, “Phone” doesn’t even appear as a column header.

5

Everything should be row headers. All the other fields (value, columns, filters) should be blank. Phones goes on top, and then last name, and then first name. Then you should see them all grouped in sort of an outline form. In the PivotTable toolbar up top, you can then change the display to “Tabular” mode to have it more horizontal and less outline-y.

If that doesn’t make sense, try posting your file somewhere so we can take a look.

6

That’s it! Thank you!

7

It worked? Hurray!

8

Wait, so you didn’t need all the names on one line, with the phone number following? That’s how I read the request.