PhoneNo's Crosstab

[ZEEq]

Hello Everyone!
i have Guardian tbl and their Phone Numbers like this
Phone Types Phone#
Mobile ############
Landline ############
what i want is make a crosstab query to show phone # as columns with phone Type headings
I used phone No's as Value and in Total used First and Last but but some Guardians have two or more Mobile and Landlines how can i show all phone numbers ?

 

[arnelgp]

you can use use "raw" table or use a simple select query:

select [name], [mobile], [landline] from yourtable

or you can put it in report, therefore you will need to "Hide Duplicates" on the name.

 

[ZEEq]

Thanks for your reply @arnelgp select query shows all numbers but i want the results to display as
GuardianID, GuardianName, MobileNo, LandlineNo (second Mobile or Landline No)

 

[arnelgp]

you can create a function to get each mobile1, mobile2, mobile3, landline1, landline2, landline3, etc.
see Query1 how the User-define-function is being called.

 

[ZEEq]

This is my Crosstab Query Code please look at this and tell me how to modify it to show all numbers

TRANSFORM Last(GuardianContactsQry.PhoneNumber) AS LastOfPhoneNumber
SELECT GuardianContactsQry.Guardian_ID, GuardianContactsQry.FirstName, GuardianContactsQry.LastName
FROM GuardianContactsQry
GROUP BY GuardianContactsQry.Guardian_ID, GuardianContactsQry.FirstName, GuardianContactsQry.LastName
PIVOT GuardianContactsQry.PhoneType;

 

[ZEEq]

@arnelgp any idea's to modify this sql ?