Nulls to zeros in query

[vaiamo]

Hi!

I have searched the forum and can't find anything that totally matches my issue. I've downloaded some code to try but it seems like it should be a simpler problem/fix....

I am creating 2 queries on which to base a report on. First a select query which I then use to create a crosstab which then is the basis for a table in
a report. I am getting a count of events that occur by Quarter (4 ), State region (5) and Category (2).

I'm using DatePart() to get the quarter and 3 tables are involved. The events I am counting are the number of records that occur (for example count of records in quarter 1, region1, category 1 etc...)- I am doing a count on a PK field.

The problem: If there are no records for region one, category 1, and quarter one it just doesn't show up in the query (I'm missing region 1, category 1 entirely from the crosstab) which makes the report look ugly. I've tried NZ() on the count of events in the initial query to try and force the region and category to show up even if no records are there but no dice.

I've included a very ugly version of the table as brought into word to try and illustrate the problem better - Obviously the formatting does not translate well when brought into word which is troubling.... Why does everything have to be so complicated dag-on-it!!

Anyway, any help would be greatly appreciated! Thanks in advance.

stephanie

 

[Jon K]

Switch the crosstab query to SQL View and add Nz(...)+0 on the count i.e.

TRANSFORM Nz(Count([FieldName]))+0
.

 

[vaiamo]

That worked like a charm in putting zeros in the blanks but I'm still missing region 1 under the agriculture category completely. How do I get it to show up in the queries with 0's in each of the 4 quarters. It looks wierd to have Region 1-5 in one category and only 2-5 in the other.

 

[mresann]

I'm still taking a shot at your problem. Incidentally, you may consider installing Snapshot from your Office installation disk, if not already installed. You can export a report to a snapshot file, then send it as a separate document from word.

To create the document, the easiest method would be as follows:

1. Open the report in report view.
2. Right click, and choose "Export"
3. On file type, scroll down and choose "snapshot"
4. Click OK (or Export).

Hope this helps for future reference.

 

[Jon K]

You will need a table (or a query) of a unique list of VDGIFRegios and use an outer join (i.e. a LEFT or a RIGHT join) in a new select query to link the list with your original select query and then base the crosstab query on this new query.
.