Unmatched Query with multiple criteria

R

rocketman70443

New Member
Joined
Dec 11, 2010
Messages
37
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?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
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
 
Upvote 0
alansidman said:
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
Click to expand...

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.
 
Upvote 0
rocketman70443 said:
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.
Click to expand...

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.
 
Upvote 0
boblarson said:
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.
Click to expand...

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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
boblarson said:
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.
Click to expand...

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!!
 
Upvote 0
rocketman70443 said:
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?
Click to expand...

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

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

Key fields are in bold.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Having a problem in the expression builder, very new to Access. It thinks I'm trying to use Automation objects?
2
Replies
15
Views
948
Micron
M
G
  • Solved
Modifying templates and linking to new tables
2
Replies
13
Views
1K
gshock
G
tourless
  • Question Question
Pain in the Access
Replies
6
Views
889
alansidman
A
dreid1011
  • Question Question
Pre filter data or add custom value with a query within another query?
Replies
2
Views
685
dreid1011
dreid1011
JenniferMurphy
  • Question Question
How to structure a variable product rating database
Replies
5
Views
932
mdmilner
M

Forum statistics

Threads
1,224,518
Messages
6,179,261
Members
452,901
Latest member
LisaGo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top