If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Minimum date difficult situation
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 |
#2
|
|||
|
|||
Minimum date difficult situation
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 tcek wrote: 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 |
#3
|
|||
|
|||
Minimum date difficult situation
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 ---- HTH Dale "tcek" wrote: 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 |
#4
|
|||
|
|||
Minimum date difficult situation
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]))); -- Build a little, test a little. "tcek" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|