Crosstab query

  • Thread starter Thread starter Jim Jones
  • Start date Start date
J

Jim Jones

In my crosstab queries I use the nz function to return
zeros rather than blanks. However, if there are no
occurences in a row, the row is not returned. Is there
any way in which I can return a row in which all the
values are zero?

Any help would be much appreciated.

Jim Jones
 
You probably need an outer join to the reference table that has ALL of the
values.

For example, a category table might have

Cat1
Cat2
Cat3

If Cat2 had no sales during a timeperiod then the inner join query would not
return any rows with Cat2 in it. So, making an outer join to the Category
table will allow you to show all categories, as well as their sales for the
timeperiod.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
Back
Top