Minimum date difficult situation

[tcek]

i have two tables with multiple rows pertaining to various customers. Each
row has a multitude of information including a date field. The other table
has additional information about the customer (i.e purchas history). There is
a date field in the purchase history table but is not directly or exactly
associated with the date field in table 1. I am interested in finding the
minimum date associated with customer in table one and determinine what was
purchased within two days prior to the date in table two (purchase history).

Basic question. What was the mimimum date for a particular customer that
has multiple entries in table one. I want to review the itmes located in
table 2 of that same customer that were purchased the day of or the day
before the minimum date of that same customer contained in table 1.

thanks in advance

 

[John Spencer]

First query to get the minimum date by customer:
SELECT CustomerID, Min([DateField]) As EarliestDate
FROM [Table1]
Group By CustomerID

Second query to get the purchases
SELECT [PurchaseHistory].*
FROM [PurchaseHistory] INNER JOIN [Name of query above] as X
ON [PurchaseHistory].[PurchaseDate] > X.EarliestDate-2 and
[PurchaseHistory].[PurchaseDate] <= X.EarliestDate

If you don't know how to build queries in the SQL view, post back with the
actual names of the tables and fields.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

it would help if you had defined the structure for the two tables, but I'll
give it a shot.

Assuming that the CustomerID is the common field between the two, then
something like:

SELECT Table1.CustomerID, Table1.DateField,
Table2.ItemID, Table2.Quantity, Table2.PurchaseDate
FROM Table1 INNER JOIN Table2
ON Table1.CustomerID = Table2.CustomerID
WHERE Table1.DateField = (SELECT MIN(T.DateField)
FROM Table1 as T
WHERE T.CustomerID =
Table1.CustomerID)
AND Table2.PurchaseDate Between DateAdd("d", -2, Table1.DateField)
AND Table1.DateField

 

[KARL DEWEY]

I could not do it in subqueries ---
Two_Purchases --
SELECT Table1.CustomerID, Table1.DateField, Table2.PurchaseDate
FROM Table1 INNER JOIN Table2 ON Table1.CustomerID = Table2.CustomerID
WHERE (((Table2.PurchaseDate) Between DateAdd("d",-1,[DateField]) And
DateAdd("d",1,[DateField])))
GROUP BY Table1.CustomerID, Table1.DateField, Table2.PurchaseDate;

Two_Purchases_1 --
SELECT Two_Purchases.CustomerID, Two_Purchases.DateField
FROM Two_Purchases
GROUP BY Two_Purchases.CustomerID, Two_Purchases.DateField
HAVING (((Count(Two_Purchases.DateField))>=2));

Two_Purchases_2 --
SELECT Two_Purchases_1.CustomerID, Min(Two_Purchases_1.DateField) AS
MinOfDateField
FROM Two_Purchases_1
GROUP BY Two_Purchases_1.CustomerID;


SELECT Two_Purchases_2.CustomerID, Table1.DateField, Table2.PurchaseDate,
Table2.ItemID, Table2.Quantity
FROM (Two_Purchases_2 INNER JOIN Table1 ON (Two_Purchases_2.MinOfDateField =
Table1.DateField) AND (Two_Purchases_2.CustomerID = Table1.CustomerID)) INNER
JOIN Table2 ON Two_Purchases_2.CustomerID = Table2.CustomerID
WHERE (((Table2.PurchaseDate) Between DateAdd("d",-1,[DateField]) And
DateAdd("d",1,[DateField])));