trying to read a query that has a variable amount of output fields

[sparks]

I have a crosstab query that should generate a matrix with 3 columns.
But IF you are just starting off there won't be enough days in it and
you might only have 1 or 2 columns.

This is used in another query that expects the crosstab to have 3
columns.

Is there a way to get empty columns to show in the query or the query
reading them to only use col2 or col3 if they exist?


I hope this makes since its hard to describe

 

[John W. Vinson]

I have a crosstab query that should generate a matrix with 3 columns.
But IF you are just starting off there won't be enough days in it and
you might only have 1 or 2 columns.

This is used in another query that expects the crosstab to have 3
columns.

Is there a way to get empty columns to show in the query or the query
reading them to only use col2 or col3 if they exist?


I hope this makes since its hard to describe

You can use the IN clause in the PIVOT expression to force columns to be
included whether or not they have data, e.g.

PIVOT Actions.Description In ("Adopted","Returned to Owner","Returned to
Wild","Transferred","Escaped","Died in Care","Euthanized");

You can also enter these in the query properties, on the "Column Headings" row
of the properties box.


--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com