Crosstab Queries Driving Me Crazy!! 1

[Ed2020]

I have created a crosstab query that summarises data for each of my company's customers. Each customer has one row and a number of columns representing another field.

I want to display the crosstab on a report for printing purposes and this is where my difficulties start!

If each of the columns has at least one value everything works fine, but if a particular column has no records that fall into that summary category the column is not displayed and on opening the report I get the error message "Microsoft Access Jet database engine does not recognice [X] as a valid field name or expression." (X being the name of the column that is not displayed.).

Somebody please help me with this. It is driving me to despair. I promise to vote you lots of purple stars!!

Ed Metcalfe.

 

[mdthornton]

There are a couple of ways round this but it depends on the nature of your column headings. If you have a report which handles all the possibilities I'll assume there are a manageable number of summary categories. You can fix your problem using the "Column Headings" property in the crosstab query. As an example, suppose you had a weekday report where there might be data on one or more days. Set the ColumnHeadings property to be "Monday","Tuesday",etc.
This forces the crosstab to return values for all columns.
NB - you have to make sure that your query returns values that exactly match this list.

Hope this helps....
Mike T

 

[mscraig]

I had hair before cross tabs, I'm bald now.
You might try making a MakeTable Query using the CrossTab query as the source (a query of a query). Run your report from the resulting MakeTable.

 

[Darkshade]

did you try to insert a null value in your field instead of leaving it blank? so you won't receive the error msg.

I know it's how I solved a few problems last time I worked with cross tables.

hope this help

 

[JeffH]

I have a report that is pretty helpful when dealing with xtab queries. It's a "generic" report, simply call a procedure, pass in a title for the report, and the xtab query name as parameters, and it opens it in a report. This might fix your problems--I haven't used it in a couple of years, but I believe it would make your life with xtab queries a bit more bearable.

Please let me know if you want a copy of it...

Jeff
jthammond@onebox.com

 

[SuzieQQ]

Try this: Go into the SQL view of your query and add the last line as follows:

PIVOT tbPriceGroup.nr In ("SGS","GS","CGS","CLGS LFO","CLGS HFO"

where the list of the fields in parentheses are the columns you want displayed, whether they contain data or not.

 

[SuzieQQ]

just to further clarify my example...my column headings are various values of the PriceGroup table. I want them to show whether they have any data or not. So I explicity name them in the last line of the query SQL as:

PIVOT <nameoftable.columnname> in (&quot;<listofvaluesyourlooking forwhetherthey'reemptyornot>&quot

So say you're looking for the account balances for each of your customers, and some don't have a particular account:

your TRANSFORM sql with the last line:
PIVOT tblCustomerAccounts.Balance in (&quot;Chequing&quot;,&quot;Savings&quot;

 

[Ed2020]

Thanks to everybody who has replied.

Jeff - Could you send me a copy of that report please? - ed_metcalfe_work@hotmail.com

Ed Metcalfe.