Crosstab without column heading field

[Sharon]

I would like to do a crosstab on a table that does not have a field that can
be used as the column heading. My table has loan number and collateral
address, where several loans have multiple collateral addresses. I want the
dataset to show all collateral addresses for each loan on one line. Thanks.

 

[John Spencer]

If you need them all in separate fields, you will need to use a ranking
query to assign them a number for 0 to n

SELECT A.LoanNumber, A.Address, Count(B.LoanNumber) as Rank
FROM SomeTable as A LEFT JOIN SomeTable As B
On A.LoanNumber = B.LoanNumber
AND A.Address < B.Address
GROUP BY A.LoanNumber, A.Address

Now you can use that as the source for the crosstab
Transform First(Address) as TheAddress
SELECT LoanNumber
FROM TheAboveSavedQuery
GROUP BY LoanNumber
PIVOT Rank

If you need to display the data in a report, you can use a sub-report
with across then down orientation for the addresses and avoid the
cross-tab completely.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================