Unanswered: Crosstab typed incorrectly or is too complex
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 -
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]
GROUP BY CODList1.[tblCust31212_ACCT], CODList1.[AcctName]
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?