display results in columns

[Guest]

hi,

I have the following query:

SELECT tbl_account_count_history.popenterdt,
tbl_account_count_history.status, tbl_account_count_history.countofstatus AS
[status count], Count(tbl_masterpop.[Loan Acct #]) AS [previous day status
count]
FROM tbl_account_count_history LEFT JOIN tbl_masterpop ON
tbl_account_count_history.status=tbl_masterpop.Status
GROUP BY tbl_account_count_history.popenterdt,
tbl_account_count_history.status, tbl_account_count_history.countofstatus
ORDER BY tbl_account_count_history.popenterdt,
tbl_account_count_history.status;

basically, it displays the query results as follows:
popenterdt status status count previous day status count
6/1/2006 BK_CURR 789 3998
6/1/2006 BK_PDB1-30 5666 6580
6/1/2006 BK_PDC31-60 3253 3285
6/1/2006 BK_PDD61-90 1312 1464
6/3/2006 BK_PDB1-30 921 6580
6/3/2006 BK_PDC31-60 2 3285
6/5/2006 PDF121+ 3 2786

I would like to know how I can amend the query so that the results are
displayed more like a crosstab query result, with the [status] in rows, and
the [popenterdt] in columns. I tried a crosstab query, but I got an error
messgae that said something like "tried to execute a query that does not
include the specified expression... as part of an aggregate function". Maybe
it is requiring a sum for tbl_account_count_history.[status count], but I am
not needing to sum or count this because the single values are already in
that table.

Please help!

Thanks in advance,
geebee

 

[Michel Walsh]

Hi,



Run the crosstab wizard over the query you already have, ie, using the query
you already have AS IF it was the table on which you run the crosstab.


Hoping it may help,
Vanderghast, Access MVP