Solved

Query to find what "hasn't" ordered

Posted on 2014-01-30
4
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 58
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 85
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

627 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