Cross Tab query problem (2000)

[armitageshanks]

Hi,
I have various reports based on various queries & cross tab queries. These queries drag out data from a previous month & collate information dependant upon what criteria is met, i.e. Ethnicity, Gender, etc. This criteria is taken from a list held in a table, ie TblEthnicity, which details all the possible choices. The user selects these from a combobox and this value is then stored in the main table.

When I try to run specific reports i get the following error message:

The Microsoft Jet Engine Database does not recognize 'Field Name' as a valid field name or expression.

I have thoroughly gone through each query and crosstab query to see if the data that its pulling is not there. In a sense its not there as specific fields values have not been chosen, but the actual source of the data (the table) is not wrong. The problem lays that the report is not displaying any of the data that relates to the query, not just the data that is missing. For instance:

TblMain, field Ethnicity, for the month of September has 42 entries for Black, 12 for Asian, 19 for Irish, 92 for white, 0 for Afro-Caribbean. Totalling 165.
In relation to this, TblMain also has field of Game, of which there are 120 entries for paper, 45 entries for scissors, but 0 for stone.

Any ideas?

[HansV]

You can specify the column headings in a crosstab query explicitly, in the query properties window. The column headings you list there will be displayed even if there are no data (and column headings you omit from the list will not be displayed even if there are data). See screen shot.

[armitageshanks]

How would this be implimented in a cross query though. Below is a screenshot of my crosstab query, the field ethnicity is where the problem is as a report specifically displays data relating to each ethnic origin. As I understand it, what you suggest enforces the query to display the column names in the query but not the content. Or am I misunderstood?

The crosstab query is based on a main query that list everything. It isn't until this query is then crosstabbed that the totals of each ethnicity is displayed. In the report each ethnicity is displayed in a text box. Obviously as there are entries missing, the text box that relates to that query in the crosstab is throwing up the error.....................I think that makes sense.

[HansV]

The situation is different from what I had guessed. Your problem is not missing column headings, but missing rows. To solve that, you need a table listing all ethnicities. If you don't have one, create it first.

The create a new query, based on this table and on the crosstab query.
Join them on the Ethnicity field.
Double click the join line, and select the option to display ALL records from the ethnicity table.
Add the Etnicity field from the table, and the other fields from the query, or *.
This query will list all values for Ethnicity, even if there are no records for them.

Use the new query as record source for the report.

[armitageshanks]

Thanks Hans, After about 2 hours of going through all the queries & cross tab queries I finally think that its been solved. Naturally your suggestion worked a treat!
Thanks for your help! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>