SQL Star Join Question?

Hi All,
The problem i've got is that i've got 2 tables with a load of dates in them and the dates are broken down into seconds. I need to do a join on the dates to get records that have a date/time that is within a second of each other.

So for example in table 1 i have a date 14/04/2004 18:00:00
and in table 2 i need to see if there are any matches that are equal to that time or 14/04/2004 18:00:01

I've heard that there is a way by using a star join or something but the below SQL statement doesn't work as it says it can't recognise my table name even though it is definitely correct. :confused:

SELECT PPsuspenseContactExtractMod.BillAccount, PPsuspenseContactExtractMod.Time_Stamp, PPsuspenseMonetaryActivityMod.Time_Stamp
FROM PPsuspenseContactExtractMod INNER JOIN PPsuspenseMonetaryActivityMod ON (PPsuspenseContactExtractMod.Time_Stamp >= PPsuspenseMonetaryActivityMod.Time_Stamp And PPsuspenseContactExtractMod.Time_Stamp < PPsuspenseMonetaryActivityMod.Time_Stamp + #00:00:01#);

Can anyone Help?? :D

Looking at this outside of code, why would you ever need to find records created within one second of each other? What are you trying to accomplish?

Looking at this outside of code, why would you ever need to find records created within one second of each other? What are you trying to accomplish?

This is because i'm trying to find records that have been created at the same time in a database. Its hard to explain but i'll give it a shot.

The system processes two tasks at once. One task logs the action and the other processes the action, Those are my 2 tables. So the only field that connects the two tables is the time stamp created by the processing of the two tasks. The thing is that out of over 200,000 records i can only find 37,000 that match the exact time, therefore i want to see if there is a time delay between the processing of the two actions. :huh:

Hope that makes sense,
Thanks :D

What database are you using? SQL Server, Access, Oracle? From the initial #'s in your query, I will assume Access for now.

The problem is, there is going to be a small margin of error when looking at log times, I can't remember off hand what it is, but when you say you need to compare "exact" time, going down to the same millisecond, you are going to have to trouble, especially with Access. Remember, these values are being stored as doubles, and it is quite diffifcult to monitor any internal rounding that is occuring.