Solved

Query to find what "hasn't" ordered

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

705 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

21 Experts available now in Live!

Get 1:1 Help Now