How do I check if there is a field in a query?

[Hennie]

Good day,

I need to run a number of crosstab queries where the column headings is
based on race. The row data will be either male or female, while the row
heading could be Faculty, department, ect. At the end of the day the male
and female data need to be combined into one report so that you would have
the number of males and females for each of the four races. In the second
last query I join the result of the crosstab with the race table to create a
field incase there is no data for one or two races as the report requires
all four races.

If there is no data for a race it creates a field <>.

How could I check if there is a field for say, Race2, if there is use the
data for Race2, if not create the heading or field for Race2.

Im using Access 2002.

Thank you for your time.

Hennie

 

[Michel Walsh]

Hi,


A crosstab will create a field <> if there is at least a NULL as result of
the PIVOT expression. If the PIVOT expression is made of a single field,
that may occur if that field allows null. To NOT allow that, change the
table design of the basic table used in the crosstab. You can force the
creation of an exhaustive list with an IN clause following the PIVOT as in:


....
PIVOT fieldName IN( "race1", "race2", "race3")


Doing that will create the three listed field, even if the fieldName has no
data about them, BUT will NOT create "race4", EVEN IF fieldName has data
about "race4". If you use the IN clause, you have to be sure to have ALL the
fields you need, since ONLY those will be created.

If you prefer the grid, you can use the crosstab query properties "Column
Headings" into which you type the list of wanted fields name.



Hoping it may help,
Vanderghast, Access MVP

 

[Hennie]

Thank you for the reply. I have made he changes and it works fine.

Once again, Thank you for your time and knowlegde.

Hennie