Solved

Query to find what "hasn't" ordered

Posted on 2014-01-30
4
242 Views
Last Modified: 2014-02-17
I have a query that figures out who has ordered during a time period that had active locations.  

I am trying to figure out who "hasn't ordered!"

I have three queries that I join together.  The screenshot shows the end results.  

***********************************************************************
Here is qryExtractGapProducts (Meaning all products that can be ordered during the allotted time.

SELECT DISTINCT Products.ProductID, Products_Pricing.CustCode, Products.Case_UPC AS [Case UPC], Products.Unit_UPC AS [Unit UPC], Products.Description, Manufacturers.ManufacturerName AS Manufacturer, Brands.BrandName AS Brand, lstProductClassifications.ProductClassification AS [Product RSL], Contracts.ContractNumber AS Contract, Products_Pricing.AsOfDate, Products_Pricing.EndDate
FROM ((Products LEFT JOIN Brands ON Products.BrandID = Brands.BrandID) LEFT JOIN ((((Products_Pricing LEFT JOIN lstUOM ON Products_Pricing.InvoiceBy = lstUOM.UOMID) LEFT JOIN lstProductClassifications ON Products_Pricing.ProductClassID = lstProductClassifications.ProductClassID) LEFT JOIN Contracts ON Products_Pricing.ContractID = Contracts.ContractID) LEFT JOIN lstPricingRegions ON Products_Pricing.PricingRegionID = lstPricingRegions.PricingRegionID) ON Products.ProductID = Products_Pricing.ProductID) LEFT JOIN Manufacturers ON Products.ManufacturerID = Manufacturers.ManufacturerID
WHERE (((Products_Pricing.CustCode) Like [CustCode:]) AND ((Products_Pricing.AsOfDate)<=[EndDate:]) AND ((Products_Pricing.EndDate) Between [BegDate:] And [EndDate:] Or (Products_Pricing.EndDate) Is Null) AND ((Products.ManufacturerID) Like [ManufacturerID:])) OR (((Products_Pricing.CustCode) Like [CustCode:]) AND ((Products_Pricing.AsOfDate)<=[BegDate:]) AND ((Products_Pricing.EndDate)>=[EndDate:]) AND ((Products.ManufacturerID) Like [ManufacturerID:])) OR (((Products_Pricing.CustCode) Like [CustCode:]) AND ((Products_Pricing.AsOfDate)<=[EndDate:]) AND ((Products_Pricing.EndDate)>=[BegDate:]) AND ((Products.ManufacturerID) Like [ManufacturerID:]));

***********************************************************************
Here is qryExtractGapDistrDeliveries (The actual sales/deliveries that occurred during the allotted time period:

SELECT Distributors_Deliveries.DistrDelID, Distributors_Deliveries.CustLocID, Distributors_Deliveries.CustCode, Distributors_Deliveries.TXDate, Distributors_Deliveries.Deldate AS [Invoice Date], Distributors.DistributorName AS Distributor, "EA" AS [Invoice By], [Qty]*[Products].[UnitsInCase] AS [Qty Invoice], Distributors_Deliveries.ContractID, Distributors_Deliveries_Details.ProductID, CDate([DelDate]) AS [Delivery Date]
FROM ((Distributors_Deliveries INNER JOIN Distributors_Deliveries_Details ON Distributors_Deliveries.DistrDelID = Distributors_Deliveries_Details.DistrDelID) LEFT JOIN Distributors ON Distributors_Deliveries.DistributorID = Distributors.DistributorID) LEFT JOIN Products ON Distributors_Deliveries_Details.ProductID = Products.ProductID
WHERE (((Distributors_Deliveries.CustCode) Like [CustCode:]) AND ((CDate([DelDate]))>=CDate([BegDate:]) And (CDate([DelDate]))<=CDate([EndDate:])))
ORDER BY Distributors_Deliveries.CustCode, CDate([DelDate]);

***********************************************************************
Here is qryExtractGapActiveLocations (all customer locations that were active during the allotted time period.)

SELECT DISTINCT Customers_Locations_ActiveLog.CustLocID, Customers_Locations.LocationName AS [Location Name], Customers_Locations.CustLocCode1 AS [Location Code1], Customers_Locations.CustLocCode2 AS [Location Code2], lstStoreInfo.StoreInfo AS [Store Info], lstLocationClassifications.LocClassification AS RSL, lstPricingRegions.PricingRegionName AS [Pricing Region]
FROM ((((Customers_Locations_ActiveLog INNER JOIN EOM ON Customers_Locations_ActiveLog.EOMID = EOM.EOMID) INNER JOIN Customers_Locations ON Customers_Locations_ActiveLog.CustLocID = Customers_Locations.CustLocID) LEFT JOIN lstStoreInfo ON Customers_Locations.StoreInfoID = lstStoreInfo.StoreInfoID) INNER JOIN lstPricingRegions ON Customers_Locations.PricingRegionID = lstPricingRegions.PricingRegionID) LEFT JOIN lstLocationClassifications ON Customers_Locations.LocClassID = lstLocationClassifications.LocClassID
WHERE (((EOM.OpeningDate)<=[BegDate:]) AND ((EOM.ClosingDate)>=[EndDate:]) AND ((Customers_Locations_ActiveLog.Active)=True)) OR (((EOM.OpeningDate)<=[BegDate:]) AND ((EOM.ClosingDate) Between [BegDate:] And [EndDate:] Or (EOM.ClosingDate) Is Null) AND ((Customers_Locations_ActiveLog.Active)=True)) OR (((EOM.OpeningDate)<=[EndDate:]) AND ((EOM.ClosingDate)>=[BegDate:]) AND ((Customers_Locations_ActiveLog.Active)=True));
sales.jpg
0
Comment
Question by:sharpapproach
  • 2
4 Comments
 
LVL 57
ID: 39822026
"who" as in customer?

Double click on the join line between customers and delivers.  Choose the option that gives you "All rows from the qryExtractGapActiveLocations"

This is an outer join.

 Now pull down one of the fields from the delivery or products, and put a criteria check of:

Is Null

Jim.
0
 
LVL 84
ID: 39822044
Which query shows what has been ordered?

Whatever query that is, then use that one with a LEFT JOIN to the Customer table, and then check for any NULL values in the ID field of that query. For example, if qryExtractGapDisDeliveries is a list of all orders, and tblCustomer is the table with your Customer listing:

SELECT DISTINCT Cust.Custcode FROM tblCustomers AS Cust LEFT JOIN qryExtractGapDistrDeliveries AS qDel ON Cust.CustCode=qDel.CustCode WHERE qDel.CustCode IS NULL

This would show all customers who do NOT have a record in qryExtractGapDistrDeliveries ...
0
 

Accepted Solution

by:
sharpapproach earned 0 total points
ID: 39822499
Yes, but the trick is I need to know "per product" who didn't order.
0
 

Author Closing Comment

by:sharpapproach
ID: 39864317
Please close.  No one's answered worked, so had to write VB code to figure it out.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now