Null Values

[Ray]

I have 6 make table queries that run consecutively from a macro. What they
do is count individual member types all from the same table and create
individual tables with one row containing the type and the count.
I have a separate query used as a data source for a report that pulls all
the types and counts in.
This last query has no criteria, it just deaws every type and count in
example like this

Col1
Tableaid Ordinary
Type: OR
Count of Type: 54

Col1
TableUnpaidaid Ordinary
Type: OR
Count of Type: 21

etc for 5 member types

My problem is this.

If the make table query finds no records that match the criteria, it does
not create a row in the destination table but just creates an empty table.

Then when the separate cumulatibe query runs, it crashes bevause there is no
data in the

TableUnpaidaid Ordinary
Type: OR
Count of Type: 21

column.

I have tried using criteria around NULL and NOT NULL but since there is no
record at all this does not work.

1. How can I get the Make Table query to creat a record with a "0" count?
OR
2. How can I make the cumulative query use a "0" count if no record exists?

Any help appreciated.

Ray

 

[Dale Fye]

Ray,

I try to avoid temporary tables (make tables) in favor of querys whenever
possible. Having said that, if I am going to use the same query over and
over again in different aspects of my application, and I know it will only
rarely change, I will use local tables, but almost always define the table
(fields, data types, etc) ahead of time and use a combination of delete and
append queries rather than a make-table query. This way, if you append zero
records, you will still have a table with zero records, rather than no table.

HTH
Dale

 

[Ray]

Thanks a lot. I replaced my queries with delete/Appends and everything works
great.

Much appreciated

Ray