MS-Access query


I need some assistance with modifying a query in MS-Access.  Please find attached sample database.  It contains the following:

1. "tblSourceData_1" with the following fields: [ID], [Time], [Owner]:
   - Table contains 5 records

2. "tblSourceData_2" with the following fields: [ID], [Time], [Owner]:
   - This table is a duplicate of table #1.
   - For record #4, I modified the time.  That is, I changed Joe's time from "33.1424" to "34.1111"

3. "qryTest":
   - I am using a join on Time.
   - Given that Joe's time was changed in table #2, the fourth record is not displayed (as expected).

Here's what I need some help with.  Modify the query so that the fourth record is *still shown* AS LONG as it within + / - 7.5 seconds.   So, as long as the time value for Joe's (in table #2) is between 25.6424 and 40.6424, the query should pick it up.  Alternatively, if Joe's time was, e.g., <= 25.6423 or >= 40.6425, the query should NOT pick up the fourth record.

Does anyone know how to modify the query to achieve this?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this query

SELECT A.ID, A.Time, C.Time
FROM tblSourceData_1 AS A INNER JOIN tblSourceData_2 AS C ON A.ID = C.ID
WHERE (((A.Time) Between [C].[Time]-7.5 And [C].[Time]+7.5));

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExpExchHelpAnalystAuthor Commented:

Thanks... this looks good.   Is there a way to NOT use "aliases" though?

Dale FyeOwner, Developing Solutions LLCCommented:
I prefer to use the ABS() function in these cases:

SELECT A.ID, A.Time, C.Time
FROM tblSourceData_1 as A
INNER JOIN tblSourceData_2 as B
WHERE ABS(A.Time - B.Time) <= 7.5

But this will only give you those records where the IDs match and the A.[Time] and B.[Time] values are within 7.5 seconds of each other.

You could also do this with a LEFT JOIN, which would give you all of the records from A and only the matches from B (based on your criteria).  Unfortunately, you cannot perform this non-equi join in the query grid, you can only do it in the SQL View.

SELECT A.ID, A.Time, C.Time
FROM tblSourceData_1 as A
LEFT JOIN tblSourceData_2 as B
AND ABS(A.Time - B.Time) <= 7.5
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ExpExchHelpAnalystAuthor Commented:
Perfect solution!!!
Rey Obrero (Capricorn1)Commented:
SELECT tblSourceData_1.ID, tblSourceData_1.Time, tblSourceData_2.Time
FROM tblSourceData_1 INNER JOIN tblSourceData_2 ON tblSourceData_1.ID = tblSourceData_2.ID
WHERE (((tblSourceData_1.Time) Between tblSourceData_2.[Time]-7.5 And tblSourceData_2.[Time]+7.5));
You need to use a non-equi join.

Where tblA.Owner = tblB.Owner AND tblB.[Time] Between dateadd("s", -7.5, tblA.[Time]) AND DateAdd("s", 7.5, tblA.[Time])

Once you change the join, you will no longer be able to view the query in QBE view and if you attempt to do so, Access, will break the join to accede to your request so be very careful.

PS Time is a reserved word and shouldn't be used as a column name.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.