Unmatched Query with multiple criteria

[rocketman70443]

1. I have a table called 1q2011
2. I have another table called catID

CatID has all of the categories of products my company sells, it has a field for: cat. 1q2011 has fields for: customer ID, category, case, price.

I'm trying to run an unmatched query to see what categories have zero sales. My original database could do this, because the queries looked at the data for only 1 customer.

The problem comes in when I'm looking at all 512 of our customers. How can I set up a query that will return the categories with zero sales for each restaurant?

 

[alansidman]

I would create an aggregate query, grouped on the customer and summed on either the quantity or sales ($) field. Save this query and then run another query that has the first query as the record source. Now in the criteria for the field that has the summed amounts, put IsNull. This should give you all customers with no sales.

Alan

 

[rocketman70443]

I would create an aggregate query, grouped on the customer and summed on either the quantity or sales ($) field. Save this query and then run another query that has the first query as the record source. Now in the criteria for the field that has the summed amounts, put IsNull. This should give you all customers with no sales.

Alan

But, if there are no sales, there isn't a "zero" sales amount, the category isn't listed under that customer.

The problem isn't total amount of sales, it's that most customers don't have sales in every category. I still need to show those categories as zero sales on a report Im creating.

Thanks for your help though. But I'm still stuck.

 

[boblarson]

But, if there are no sales, there isn't a "zero" sales amount, the category isn't listed under that customer.

The problem isn't total amount of sales, it's that most customers don't have sales in every category. I still need to show those categories as zero sales on a report Im creating.

Thanks for your help though. But I'm still stuck.

You use an outer join to get that information. You create the query that Alan mentions which gives you the total sales for each customer for each category. And by linking up the category table again with that query in a new query and using an OUTER JOIN from the category table to the other query and you use the Category from the category table in that query and then the sales in the other it will then return all categories and show a null where there is no sales. So you then, instead of using the sum field as is, you put:

Sales:Nz([SumOfSales], 0)

which will put a zero if there are no sales for that category.

 

[rocketman70443]

You use an outer join to get that information. You create the query that Alan mentions which gives you the total sales for each customer for each category. And by linking up the category table again with that query in a new query and using an OUTER JOIN from the category table to the other query and you use the Category from the category table in that query and then the sales in the other it will then return all categories and show a null where there is no sales. So you then, instead of using the sum field as is, you put:

Sales:Nz([SumOfSales], 0)

which will put a zero if there are no sales for that category.

Ive created the query that alan mentioned. Each customer now has all of their sales totaled, in those categories with a sales amount greater than zero.

After that, it gets a bit over my head. I can create fairly simple databases, but linking tables & creating relationships is still a trial & error/crapshoot. I can see how your directions make perfect sense, but my brain is kinda muddled on it still.

I guess the big thing right now is, where do I place Nz([SumOfSales],0)?
I either get zero results, or only 1 result. I know both have to be false. Also, when I try to run a query with your formula, I'm being prompted to enter a variable for sumofsales

 

[alansidman]

I guess the big thing right now is, where do I place Nz([SumOfSales],0)?

Put that in a new field (column) in the QBE (grid)

Alan

 

[rocketman70443]

This is driving me nuts!!!

I made a simple query, getting the customerID, category, and case from the main table. I have it grouping the case by sum. The end result of this query is that I can see a customer total for each category where cases sold was >0.

Then I created a new query, which pulled the results from the query in the first paragraph, along with the category field from the CatID table. I added another field that says Nz([SumOfCase],0). This is resulting in a column that has the same figures as the sumofcase column.

Im pretty handy with Excel, but Access is a new beast to me, at least in this level of detail. So don't assume I know anything beyond what table, query, and report means.

Thanks so much for your help.

 

[boblarson]

Can you email a copy of your database with some bogus records in it? We can then see what you are doing and make the necessary adjustments.

I can PM you an email address you can use if you wish to do that.

 

[rocketman70443]

Can you email a copy of your database with some bogus records in it? We can then see what you are doing and make the necessary adjustments.

I can PM you an email address you can use if you wish to do that.

That would be super. I'll have to pare it down or build a mockup. What version of Access do you have? I'm using 2010, but I'll save it in whatever format you need.

Thanks so much!!

 

[Aladin Akyurek]

1. I have a table called 1q2011
2. I have another table called catID

CatID has all of the categories of products my company sells, it has a field for: cat. 1q2011 has fields for: customer ID, category, case, price.

I'm trying to run an unmatched query to see what categories have zero sales. My original database could do this, because the queries looked at the data for only 1 customer.

The problem comes in when I'm looking at all 512 of our customers. How can I set up a query that will return the categories with zero sales for each restaurant?

Can we have the relevant tables in standard notation like in:

CUSTOMERS(CID,Cname,Ccity)
ORDERS(OID,CID,Odate)

Key fields are in bold.