Join two fields on date/time but include all records within six hours prior to time on left

[keun]

The table on the left has a date time field. The table on the right has a date time field. I can join the two where those fields are equal. In other words, return records from right table which match the date time of the left table.

How do I return records from the right table which are equal to or within six hours prior to the left table?

 

[dhookom]

Can you provide the SQL view of your existing query joined on the times?
You can go to the SQL view and change the "=" in the join to "BETWEEN".

What happens if there are multiple records within six hours prior to the left table?

Duane
Hook'D on Access
MS Access MVP

 

[keun]

I realized that I asked the question incorrectly and that I am not joining on date, rather joining on two other fields and using date as a HAVING criteria. I am going to try putting BETWEEN in my HAVING criteria; I think that will work.

As for the multiple records question - I am essentially doing a self join and this is my way of identifying interesting records and returning the items recorded in the six hours prior to the interesting records.

 

[SkipVought]

How about posting your current SQL and explain how this code fails to meet your requirement.

 

[keun]

Here is the code. I added the BETWEEN and things are working.

SELECT tblMerged.ID, tblMerged.user_name, tblMerged.item_name, tblMerged.DateTimeMerge, tblMerged.qty, tblMerged.xact_dati, tblMerged.xfer_type
FROM qryWasting INNER JOIN tblMerged ON (qryWasting.item_name = tblMerged.item_name) AND (qryWasting.user_name = tblMerged.user_name)
WHERE (((qryWasting.DateTimeMerge) Between [tblMerged].[DateTimeMerge] And DateAdd("h",-6,[tblMerged].[DateTimeMerge])))
GROUP BY tblMerged.ID, tblMerged.user_name, tblMerged.item_name, tblMerged.DateTimeMerge, tblMerged.qty, tblMerged.xact_dati, tblMerged.xfer_type
ORDER BY tblMerged.user_name, tblMerged.item_name, tblMerged.DateTimeMerge;

 

[keun]

After some head scratching I discovered that my code above is NOT working. So here I am to ask the question again.

In essence here is what I am looking for - I have a log which records events for users. It records Name, Date, Item, and Activity. What I want to do it identify all records where a specific Activity is logged. Then I want to return that record and every record which matches the Name, Item, and is within six hours prior to the date/time of the record that matched the Activity criteria.

I think this can be accomplished with a self-join but I am not adept at those so I created a query to identify the records with the specific Activity (QryFirstQuery) and I am trying to relate that query back to my original table based on the criteria. It it not working. It is getting close but I am getting repeated records.

The table (tblMyTable) has the fields: Name, Item, TheDate, Activity

The first query is:

SELECT tblMyTable.Name, tblMyTable.Item, tblMyTable.TheDate, tblMyTable.Activity
FROM tblMyTable
WHERE (((tblMyTable.Activity)="The Other"));

The last piece, where I hope to get the relevant records is:

SELECT tblMyTable.Name, tblMyTable.Item, tblMyTable.TheDate, tblMyTable.Activity
FROM QryFirstQuery INNER JOIN tblMyTable ON (QryFirstQuery.Item = tblMyTable.Item) AND (QryFirstQuery.Name = tblMyTable.Name)
WHERE (((tblMyTable.TheDate) Between [qryFirstQuery].[TheDate] And DateAdd("h",-1,[tblMyTable].[TheDate])));

Here is my data, I will describe what the colors are (I did this manually so I might have done it wrong):
Yellow means that the record got picked up by the first query.
Orange means that the record got picked up because it matches Name and Item and is six hours prior to a yellow cell.

id 1 gets picked up bc it is The Other. Nothing prior to it gets picked up bc there is nothing matching Name and Activity.
id 1 also gets picked up bc it is withing six hours prior to id 5 and matches names and activity
id 3 because matched id 5 Name, Activity and is within six hours
id 5 because The Other
id 11 because The Other
id 3 because matched id 14 Name, Activity and is within six hours
...

 

[PHV]

You missed Activity in the JOIN.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[keun]

If I add activity the results seem just as wonky

SELECT tblMyTable.Name, tblMyTable.Item, tblMyTable.TheDate, tblMyTable.Activity
FROM QryFirstQuery INNER JOIN tblMyTable ON (QryFirstQuery.Activity = tblMyTable.Activity) AND (QryFirstQuery.Item = tblMyTable.Item) AND (QryFirstQuery.Name = tblMyTable.Name)
WHERE (((tblMyTable.TheDate) Between [qryFirstQuery].[TheDate] And DateAdd("h",-6,[tblMyTable].[TheDate])));