Limit records by date difference

  • Thread starter Thread starter molly
  • Start date Start date
M

molly

I have a query that links two tables. I want to only distinct rows from the
second table when the date of the order is within three days of the date from
the first table. The current query does a left join from the first table to
the second table but there are multiple rows in the second table and I only
want to show one row and only if it meets a date criteria. Is there a way to
do this via the query?
Thanks,
 
You could use a subquery to see if such a record EXISTS in the other table:

SELECT Table1.*
FROM Table1
WHERE EXISTS
(SELECT ID FROM Table2
WHERE Table2.ID = Table1.ID
AND Table2.TheDate - Table1.TheDate Between -3 And 3);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
 
I have a query that links two tables. I want to only distinct rows from the
second table when the date of the order is within three days of the date from
the first table. The current query does a left join from the first table to
the second table but there are multiple rows in the second table and I only
want to show one row and only if it meets a date criteria. Is there a way to
do this via the query?
Thanks,
Click to expand...

Put a criterion on the second table's date field of

BETWEEN DateAdd("d", -3, [firsttabledate]) AND DateAdd("d", [secondtabledate],
3)

and set the query's Unique Values property to yes. You'll need to uncheck the
Show checkbox on any fields from the second table.

Since you're applying a criterion to a field from the second table, a left
join is inappropriate: if there is no matching record, then it certainly can't
be within three days of the date in the first record! Just use an Inner join,
or else clarify your logic.
 
I 've done what you outlined and this is what I get when I run the query. As
you can see below I am getting multiple rows. On PtNme Jane for Prn 461 I
want to get one row for order date 10/5 since this is the order_date that is
within three days of the event_date of 10/7. Same thing with John I want to
only see one entry for products 458 and 459 for the order_date of 10/5 since
that is the one that is within three days of the event_date. Is this
possible via a query?

Prod_Ord QRY
Event_Date Prd PrN PtNme Order_Date Test
10/1/2008 rbc 754 jane 10/1/2008 type
10/7/2008 rbc 461 jane 10/1/2008 type
10/7/2008 rbc 461 jane 10/5/2008 type
10/1/2008 ffp 123 john 9/29/2008 type
10/1/2008 rbc 456 john 9/29/2008 type
10/1/2008 rbc 755 john 9/29/2008 type
10/5/2008 ffp 459 john 9/29/2008 type
10/5/2008 ffp 459 john 10/5/2008 type
10/5/2008 rbc 458 john 9/29/2008 type
10/5/2008 rbc 458 john 10/5/2008 type


Thanks!

--
Molly


John W. Vinson said:
I have a query that links two tables. I want to only distinct rows from the
second table when the date of the order is within three days of the date from
the first table. The current query does a left join from the first table to
the second table but there are multiple rows in the second table and I only
want to show one row and only if it meets a date criteria. Is there a way to
do this via the query?
Thanks,
Click to expand...

Put a criterion on the second table's date field of

BETWEEN DateAdd("d", -3, [firsttabledate]) AND DateAdd("d", [secondtabledate],
3)

and set the query's Unique Values property to yes. You'll need to uncheck the
Show checkbox on any fields from the second table.

Since you're applying a criterion to a field from the second table, a left
join is inappropriate: if there is no matching record, then it certainly can't
be within three days of the date in the first record! Just use an Inner join,
or else clarify your logic.
Click to expand...
 
I am not familiar with subqueries. I appreciate the link to the intro to
subqueries and have printed the information so I can learn!!
Thanks!
 
Back
Top