How do I check if a field exists in a query?

[henniec]

Good day,

I am creating several crosstab queries on race and gender. I have a table with four different race names that I will call Race1, Race2, Race3 and Race4. The row criteria could be Faculty, department, research ect.

In some cases there may be insufficient data to cover all 4 races. In the final report I still need to display all four even if there is no data for one field. I run a total of about six queries to sepearate male and female and then do a crosstab to count the different races for each row criteria. The final query is to combine male and female and change the field name to reflect race/gender.

Before I run the crosstab query I link the Race table with the data from the second query to add a missing field. The crosstab query create an extra field and name it
"<>" with a zero value.

How can I in the final query check if there is a field say "Race2" and if there is not, change the name "<>" to Race2, or if there is such a field enter the data for that field?

Thank you for your time.

Hennie

 

[PHV]

A common way (SQL code):
TRANSFORM ...
...
PIVOT yourPivotField IN ('Race1','Race2','Race3','Race4')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[henniec]

Thanks PHV for your reply. It did the trick.

Hennie