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.
help
Cross Tab Query on Form DataBase
I have a cross tab query that is displayed on a form tied to the table that houses the raw data for the cross tab query. Each line in the cross tab query comes from 3 lines in the table
cross tab query DataBase
, cross, tab, query" / > (<> ) why this sign appears in cross tab query Access Queries Discussions Hookom (1) Duane (1) This is a result of one or
I am using access 2007. I have the following cross tab query which supports a dynamic cross tab query reprot that I created. In the cross tab query, I generate teh field "Overall
Forms with parameters to pass to cross-tab queries DataBase
Hi, I have a cross-tab query built on another query. I have created a form to display the cross-tab query. Now, I would like to restrict the results of the cross-tab query by
Using automation to load a cross tab query to Excel DataBase
Is there an easy way of using automation to load a cross tab query to Excel? I have a cross tab query with the following SQL: PARAMETERS [forms]![MyForm]![Start] DateTime, [forms]![MyForm]![End] DateTime, [Forms
Cross-Tab Query DataBase
I have a cross tab query that I want to only pull info forma parameter on a form. On the form ir will ask you for 6 months and I want the cross tab query to only pull those 6 months, however they will change all the time so
Report based on cross-tab query DataBase
Have a report based on a cross-tab query that doesn't always have data for each variable. List of offices with sales by 22, 321 08388 Feb-06 52, 338 09212 Jan-07 14, 987 etc. Have a cross-tab query that arrays data like this: Office Dec-06 Jan-06 Feb-06 08338 48
Ordering Columns in a Cross Tab Query & Dividing a Value DataBase
Ordering Columns in a Cross Tab Query & Dividing a Value DataBase Hello, First question, is there a way to specify what order the columns appear in a cross tab query? Second, is there a way to distribute a value in one column of a cross tab query across new columns? For example, if I have a total value of a contract
Cross-Tab Query - Report DataBase
I have a cross-tab query that works as desired. I can get it to post to a report. My problem can I get the report to go beyond the 12 items? The data in the cross-tab query will take multiple pages to print all the data. Is there a way to
Cross Tab Query with two values DataBase
I have a table with to key fields: Hours Worked and Revenue. I would like to build a cross tab query that uses each as that value with month as the column header. How do I put two values into a cross tab query. I can't make revenue a calculated field because sometimes revenue and hours are