Crosstab query

[Sirocco]

Here's a classic: I have a list of phone numbers, several per customer. I
want to arrange these phone numbers in multiple columns (titled phone1,
phone2 - just 2 phone cloumns), rather than appear as a list in a single
column. How can I do this? It seems a crosstab won't do this, since the
value of a text field can't be used to populate the cells in a crosstab
query.

Many thanks in advance.

 

[Guest]

If you really want them in separate columns then you need to be able to
generate ranking numbers for each customer to use as column headings. This
would be created with an expression like:
"Phone" & DCount("[Phone]","tblCustPhones","CustID =" & [CustID] & " AND
[Phone]<='" & [Phone] & "'")
You can then use First() against the Phone field as the Value.

 

[Sirocco]

But why would I rank the customers? I need to rank the phone numbers (by
their received date, so that the one received last is ranked first). This
way I can have an actual column for the most recent phone number (don't
worry about the other phone numbers). Is this structure possible?

Thanks.

If you really want them in separate columns then you need to be able to
generate ranking numbers for each customer to use as column headings. This
would be created with an expression like:
"Phone" & DCount("[Phone]","tblCustPhones","CustID =" & [CustID] & " AND
[Phone]<='" & [Phone] & "'")
You can then use First() against the Phone field as the Value.
--
Duane Hookom
Microsoft Access MVP

Here's a classic: I have a list of phone numbers, several per customer.
I
want to arrange these phone numbers in multiple columns (titled phone1,
phone2 - just 2 phone cloumns), rather than appear as a list in a single
column. How can I do this? It seems a crosstab won't do this, since the
value of a text field can't be used to populate the cells in a crosstab
query.

Many thanks in advance.

 

[Guest]

You would be ranking "within" customers. You didn't mention anything about
"received date" in your prior message. You would need to replace the [Phone]
field in the DCount() with the date. This would require changing the
delimiter from a single quote to #. You would probably also need to change
the <= to >= to get the most recent dates ranked first.

--
Duane Hookom
Microsoft Access MVP

But why would I rank the customers? I need to rank the phone numbers (by
their received date, so that the one received last is ranked first). This
way I can have an actual column for the most recent phone number (don't
worry about the other phone numbers). Is this structure possible?

Thanks.

If you really want them in separate columns then you need to be able to
generate ranking numbers for each customer to use as column headings. This
would be created with an expression like:
"Phone" & DCount("[Phone]","tblCustPhones","CustID =" & [CustID] & " AND
[Phone]<='" & [Phone] & "'")
You can then use First() against the Phone field as the Value.
--
Duane Hookom
Microsoft Access MVP

Here's a classic: I have a list of phone numbers, several per customer.
I
want to arrange these phone numbers in multiple columns (titled phone1,
phone2 - just 2 phone cloumns), rather than appear as a list in a single
column. How can I do this? It seems a crosstab won't do this, since the
value of a text field can't be used to populate the cells in a crosstab
query.

Many thanks in advance.