Shorter way to do this report?

[Torn39]

Thanks for your help....

I've got a cross-tab query that I want a report from, essentially a repair type per worker table.

Here's my problem...

I need to be able to show the recipients of the report the "column totals" where the values are "null" also..ie zero. When I go to generate my query, I get only the columns that there is an occurence for.

Example.

I've got two workers, Jim Bob and Bubba (I'm in Tenn, bear with me...). They have 3 different jobs they can do in a month. They each two "Job A's", one "Job B's", and no "Job C's" over the requested time period...

So I want my report to look like this...

Job Summary
Worker Job A Job B Job C Total
Jim Bob 2 1 0 3
Bubba 2 1 0 3
------------------------------------
Total 4 2 0 6

And right now, without "hard coding" the report, all I can create is this

Job Summary
Worker Job A Job B Total
Jim Bob 2 1 3
Bubba 2 1 3
----------------------------
Total 4 2 6

Is there any way for me to create the query with it requiring to show the "blank columns"? I've got a table in the database that lists all the possibilities (there is 9 'jobs').

Thanks for your help....

Torn

"...I award you no points..."

 

[Hillary]

This will only work if Jobs 1-9 are all reported on the same form...

Change the Default Value in the table design to 0. Also change the default value in the form design to 0. (They will both be blank) This will create values instead of leaving the fields null. This will only create 0's on new records. You will have to go into the old records and type a 0 for the jobs.

Good luck!

Hillary

 

[MichaelRed]

Hmmmmmmmmm,

Depends on how much work you are willing to do -in the design- and how much flexability you need.

Low flexability (and low work) is to set the column headings property of the XTab query (see ms. A.'s help for the topic). If this is sufficient, STOP HERE.

The other version is to generate a recordset with the desired column headings, Left outter join of that recordset with the 'real data' becomes the source for your XTab query. Use the new field in place of the current one for the col headings. Some extra work -but a lot more flexability.

If you don't understand the second approach, go back to the XTab columnheadings property.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over