INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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?

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

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

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

(OP)
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.

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

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

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

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

CODE -->

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; 

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

(OP)
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:

CODE -->

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:

CODE -->

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
...

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

(OP)
If I add activity the results seem just as wonky

CODE -->

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]))); 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close