Query Results from an Empty Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a (Totals) Query that returns zeros in the SUM and COUNT fields when
there are no records in the table.

Tried the NZ( ) function around the SUM and COUNT commands but the Query
does not return anything.

Thanks for your help!

"New to Access"
 
Dear New:

You could create a UNION ALL of your table with a single row having
zero values in the columns you want to SUM. You will need to subtract
one to get an accurate COUNT.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom:

Are you referring to a single row from a dummy table that contains zeros?

Also not clear as to how I would reduce the Count by 1.

Again, Thanks for your help!

"New"
 
Dear New:

A dummy table may be necessary, depending on what database engine and
version you are using. If you are using Jet, working without a table
may not work. You could try it:

SELECT "Hello" AS Column1

If this works with no FROM clause, then you can construct a row like
this in the UNION ALL. If not, you may need a dummy table. However,
I'd not actually use the rows from that table, more like:

SELECT TOP 1 "" AS C1, "" AS C2, 0 AS C3 FROM Dummy

This will return 1 row even if there are more in Dummy, and will
return none of the actual values in that row. Thats safer.

You can simply subtract 1 from the COUNT:

COUNT(*) - 1

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top