Solved

Query to find what "hasn't" ordered

Posted on 2014-01-30
4
249 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

696 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