Microsoft Access - Crosstab typed incorrectly or is too complex

Asked By Heidi Boucher on 06-Dec-12 03:58 PM
Thank you for reading my post! Apologies in advance - I am expanding into the world of crosstab queries and don't completely have my head around it.

So I've pasted the sql that was created through the wizard. What I've used is a union query dumped into a select query as my source.
The union query is made from 3 select queries that include parameters and calculations.

Is there a way to get around the complex part? The field you see in the sql may be the culprit COSTNOTCOVERED. It's is a calculation of two fields -
[COST]-Nz([PD])


I see "try simplifying by assigning parts of the expression to variables" - but I don't know how to do that


PARAMETERS [Forms]![Switchboard]![sfPeriodNow]![CHARGEOFFDATEB] Text ( 255 ), [Forms]![Switchboard]![sfPeriodNow]![CHARGEOFFDATEE] Text ( 255 );
TRANSFORM Sum(CODList1.[COSTNOTCOVERED]) AS SumOfCOSTNOTCOVERED
SELECT CODList1.[tblCust31212_ACCT], CODList1.[AcctName], Sum(CODList1.[COSTNOTCOVERED]) AS [Total Of COSTNOTCOVERED]
FROM CODList1
GROUP BY CODList1.[tblCust31212_ACCT], CODList1.[AcctName]
PIVOT CODList1.[INVNO];


What I'm trying to accomplish is take the union query that has Acct numbers and account names with their invoices and (COSTNOTCOVERED is an) amount.
12345 ABC Company IN099999997 $100.00
12345 ABC Company IN099999998 $150.00
12345 ABC Company IN099999999 $200.00

I need a query that will give me the Acct number and name once and it's invoice and amount in column succession. (I used to re-type the invoices in fields named inv1, amt1, inv 2, amt2.)
to look like this:
12345 ABC Company IN099999997 $100.00 IN099999998 $150.00 IN099999999 $200.00

this query is exported to a word mail merge template

Is there a way to get around this error ?
is crosstab going to give me what I'm looking for?
Pat Hartman replied to Heidi Boucher on 30-Dec-12 04:33 PM
The parameters are defined as text and their names indicate that they are dates.

Does the union query work by itself?

If fixing the parameter data type doesn't fix the problem, try making the union into a temptable and run the crosstab on the temp table.  This isn't a great solution because temp tables cause bloat and you have to make sure you compact regularly but it may get you moving.