displaying multiple data types

[planned]

My database has multiple phone types which each have their own ID. Some
records have one type, others can have up to 12 phone types. Queries show
multiple phone types as multiple rows, one row for each phone type. Reports
show multiple entries for each individual, one for each phone type.

Is there a way to create something that will display all phone types with one
entry per individual?

I created a table with individual phone types. I'm considering creating a
macro that would update this table's phone types, i.e. in original table -
for data type = home, add data from original query field to Home Phone field
in new table; for data type = email, add data from original query field to
Email field in new table; etc.

How would I do this? Is there a better way?

 

[Allen Browne]

So you have related records for the phone numbers (which is correct
relational structure), and you want to present them as a single long string
of phone numbers, one after the other?

Here's the code to do that kind of thing:
Concatenate values from related records
at:
http://allenbrowne.com/func-concat.html

 

[Michel Walsh]

If your actual table is like:


CustomerID, phoneNumber, phoneType ' fields
1010 123-456-7899 home
1010 123-987-0912 cell ' data sample



Then a crosstab can be of some use:


TRANSFORM LAST(phoneNumber)
SELECT customerID
FROM yourTableNameHere
GROUP BY customerID
PIVOT phoneType


and use that query, not the original table, were you need the spreadsheet
look.


Hoping it may help,
Vanderghast, Access MVP