• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Query to find what "hasn't" ordered

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));
  • 2
1 Solution
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
"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

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
sharpapproachAuthor Commented:
Yes, but the trick is I need to know "per product" who didn't order.
sharpapproachAuthor Commented:
Please close.  No one's answered worked, so had to write VB code to figure it out.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now