Problem with Many to One Query

  • Thread starter Thread starter mj
  • Start date Start date
M

mj

Hi. I have a small table
called "tblProductsCustomCategories". It has about ten
entries. Its only column is labeled "ProductsLevelCustom".

I also have a query called "qryShipped" that contains lots
of sales data grouped by State and "ProductsLevelCustom".
The problem is that, since some states didn't sell all ten
Products, the query doesn't show zero dollars sold. I
tried using a one to many query by joining
tblProductsCustomCategories and qry Shipped but it still
doesn't include all products. I'm basically trying to show
all products in all states and, if there was no product
sold, have a zero value. Any suggestions would be great.
Thanks!
 
Hi. I have a small table
called "tblProductsCustomCategories". It has about ten
entries. Its only column is labeled "ProductsLevelCustom".

I also have a query called "qryShipped" that contains lots
of sales data grouped by State and "ProductsLevelCustom".
The problem is that, since some states didn't sell all ten
Products, the query doesn't show zero dollars sold. I
tried using a one to many query by joining
tblProductsCustomCategories and qry Shipped but it still
doesn't include all products. I'm basically trying to show
all products in all states and, if there was no product
sold, have a zero value. Any suggestions would be great.
Thanks!
Click to expand...

Select the Join line in the query design window and change it to an
outer join - "Show all records in tplProductsCustomCategories and
matching records in qryShipped". This will show a NULL for those
values of ProductsLevelCustom that weren't shipped; you can use NZ()
to convert this null to a zero.
 
Back
Top