Using CrossTab Queries as Report Source 2

[LarryDeLaruelle]

General question on how to handle changing results when using cross tab queries as the source for reports.

I encountered a problem with one of my reports which tracks residents receiving any of several medications by date prescribed. The queries' column setting is the field that contains the medication name; row is the resident name; the prescription date is returned as a value under the medication column.

When the report was first created at least one resident was receiving at least one of the seven tracked meds and each was included in the detail line.

The problem now is that one of the tracked meds is not being administered to any resident (but may be again at any time in the future) and the cross tab does not contain a column for that med even though the report still has the original seven. As a result I get a message that Access does not recognize the name of the report's control for that med. (Hope that wasn't too confusing.)

Is there a way to trap for this error in the On Open or On Format events? And, if so can anyone help me with the syntax?

Thanks much.



Larry De Laruelle
larry1de@yahoo.com

 

[Quehay]

Larry,

Do you have a dedicated table for the med types? This would allow doing an outer join of your Pts table with Med types, thus permitting a crosstab with entries in the col headers that will be independent of whether or not there are entries in the them.

 

[buckleb]

As you only have seven meds, it might be worthwhile just entering the names (exactly as they appear in the table) in the query's column headings e.g. valium,prozac,etc.

 

[hhenry]

I have this same issue -- my column names are years and I want the report to still work when the years change in the underlying data.

This is a tricky issue...