Force a zero if it does not exist in crosstab query

[SatanLuciferJones]

The picture shows more clearly my dilemma, but I'll try to explain.

I have roughly 10,000 sets of data and an if statement in a query that yields xW, xE, or xN, where the x is c, l, d, or s. I created some crosstab queries to count the number of each in each group. The problem is that for DWEN and LWEN, there were no xE values (shown in red). The SWEN & CWEN formulas work just fine, but I need to force a zero if the lE or dE heading do not exist.

 

[theDBguy]

Perhaps one way is to force them to show up as zeroes in the source crosstab first, so they would show up in the final query result.

 

[SatanLuciferJones]

Thanks for the response. I'm totally new to crosstabs, I only started experimenting with them yesterday! But how would I go about doing what you suggested?

 

[theDBguy]

Thanks for the response. I'm totally new to crosstabs, I only started experimenting with them yesterday! But how would I go about doing what you suggested?

Okay, if you're saying those values you want to show as zeroes were basically missing columns in the Crosstabs, then you can use the Column Heading property to specify all the columns you want to show up in the crosstab results. Even if there's no data for a column, that column will still display or be part of the result, if you specify it in the Column Headers property.

 

[plog]

I think you are fundamentally approaching this wrong. Unfortunately, I don't know what "this" is.

Crosstabs aren't an intermediate step, they are the finished results. Your crosstabs are sub-queries that are used to generate a further result. My guess is that you can accomplish the greater goal (whatever that may be) without using cross-tabs.

Perhaps you can demonstrate your entire issue with data. Give me 2 sets:

A. Starting data from your table(s). Include table and field names and enouh data to cover all cases.

B. Expected results of A. Show me the data you want your system to spit out when you feed it the data from A.

Again, 2 sets of data, starting and ending. I don't care about any intermediate steps you think are necessary, just starting and ending data please.