...efficient method of finding closest date between unrelated tables

[Need2Know]

Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated. Thank you all in advance.

SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
FROM tbl1, tbl2
WHERE tbl1.[SomeDte] < tbl2.[SomeOthDte] And tbl2.[SomeOthDte]
Between tbl2.[SomeOthDte] And tbl1.[SomeDte]
ORDER BY tbl1.[SomeDte] DESC;


tbl1 tbl2

SomeDte SomeOthDte
IgnoreDte Field1
Field2
Field3

 

[Marshall Barton]

Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated. Thank you all in advance.

SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
FROM tbl1, tbl2
WHERE tbl1.[SomeDte] < tbl2.[SomeOthDte] And tbl2.[SomeOthDte]
Between tbl2.[SomeOthDte] And tbl1.[SomeDte]
ORDER BY tbl1.[SomeDte] DESC;


tbl1 tbl2

SomeDte SomeOthDte
IgnoreDte Field1
Field2
Field3

How do you define "closest"? Until you can specify that,
all we can do is guess what you want.

Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do. It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".

Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).

Your example is too vague to help me clarify whatever you
are trying to say.

After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.

 

[Need2Know]

Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.

   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;

tbl1                tbl2

SomeDte             SomeOthDte
IgnoreDte           Field1
           Field2
           Field3

How do you define "closest"?  Until you can specify that,
all we can do is guess what you want.

Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do.  It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".

Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).

Your example is too vague to help me clarify whatever you
are trying to say.

After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

My apologies... and thank you for responding. To clarify, I'm trying
to compare the date columns between tbl1 and tbl2 to find the closest
matching date in tbl1 then display the resulting date in the CloseDate
field.

tbl1 tbl2 CloseDate
12/31/2008 8/1/2009 7/31/2009
1/31/2009 8/12/2009 7/31/2009
2/28/2009 8/5/2009 7/31/2009
3/31/2009 8/10/2009 7/31/2009
4/30/2009 4/25/2009 3/31/2009
5/31/2009
6/30/2009
7/31/2009

* tbl1 consists only of dates (all entries are unique)
* tbl2 consists of activity detail in addition to dates (some dates
are repeated).

 

[KARL DEWEY]

TRy this --
SELECT TOP 1 [TBL1].[Date close], Min(Abs(DateDiff("d",[TBL1].[Date
close],[TBL2].[Date close]))) AS Expr1, [TBL2].[Date close]
FROM [TBL2], [TBL1]
GROUP BY [TBL1].[Date close], [TBL2].[Date close]
ORDER BY [TBL1].[Date close] DESC , Min(Abs(DateDiff("d",[TBL1].[Date
close],[TBL2].[Date close])));

 

[Marshall Barton]

Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.

   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;

tbl1                tbl2

SomeDte             SomeOthDte
IgnoreDte           Field1
           Field2
           Field3

How do you define "closest"?  Until you can specify that,
all we can do is guess what you want.

Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do.  It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".

Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).

Your example is too vague to help me clarify whatever you
are trying to say.

After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.

My apologies... and thank you for responding. To clarify, I'm trying
to compare the date columns between tbl1 and tbl2 to find the closest
matching date in tbl1 then display the resulting date in the CloseDate
field.

tbl1 tbl2 CloseDate
12/31/2008 8/1/2009 7/31/2009
1/31/2009 8/12/2009 7/31/2009
2/28/2009 8/5/2009 7/31/2009
3/31/2009 8/10/2009 7/31/2009
4/30/2009 4/25/2009 3/31/2009
5/31/2009
6/30/2009
7/31/2009

* tbl1 consists only of dates (all entries are unique)
* tbl2 consists of activity detail in addition to dates (some dates
are repeated).

This example seems to imply that the "closest" date is the
latest date in tbl1 that is before the tabl2 record's date.

In that case, I think you should try:

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
FROM tbl1
WHERE tbl1.datefield < tbl2.datefield
) As ClosestDate
FROM tbl2

 

[Need2Know]

Need2Know wrote:
Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.
   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;
tbl1                tbl2
SomeDte             SomeOthDte
IgnoreDte           Field1
           Field2
           Field3
How do you define "closest"?  Until you can specify that,
all we can do is guess what you want.
Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do.  It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".
Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).
Your example is too vague to help me clarify whatever you
are trying to say.
After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.

My apologies... and thank you for responding. To clarify, I'm trying
to compare the date columns between tbl1 and tbl2 to find the closest
matching date in tbl1 then display the resulting date in the CloseDate
field.

tbl1                tbl2            CloseDate
12/31/2008  8/1/2009                7/31/2009
1/31/2009           8/12/2009       7/31/2009
2/28/2009           8/5/2009                7/31/2009
3/31/2009           8/10/2009               7/31/2009
4/30/2009           4/25/2009               3/31/2009
5/31/2009
6/30/2009
7/31/2009

* tbl1 consists only of dates (all entries are unique)
* tbl2 consists of activity detail in addition to dates (some dates
are repeated).

This example seems to imply that the "closest" date is the
latest date in tbl1 that is before the tabl2 record's date.

In that case, I think you should try:

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
                                                                FROM tbl1
                                                                WHERE tbl1.datefield < tbl2.datefield
                                                                ) As ClosestDate
FROM tbl2

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thank you Marsh! This is exactly what I was looking for. Again my
apologies for the vague post and my deppest thanks for your time and
patience.

 

[Need2Know]

TRy this --
SELECT TOP 1 [TBL1].[Date close], Min(Abs(DateDiff("d",[TBL1].[Date
close],[TBL2].[Date close]))) AS Expr1, [TBL2].[Date close]
FROM [TBL2], [TBL1]
GROUP BY [TBL1].[Date close], [TBL2].[Date close]
ORDER BY [TBL1].[Date close] DESC , Min(Abs(DateDiff("d",[TBL1].[Date
close],[TBL2].[Date close])));

--
Build a little, test a little.

Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.

   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;

tbl1               tbl2

SomeDte            SomeOthDte
IgnoreDte          Field1
           Field2
           Field3
.- Hide quoted text -

- Show quoted text -

Thank you for the response Karl. Marsh's post was exactly what I was
looking for.

 

[Need2Know]

Need2Know wrote:
Good Morning All, Is there a more efficient method of finding closest
date between unrelated tables? Any assistance provided is greatly
appreciated.  Thank you all in advance.
   SELECT TOP 1 tbl1.[SomeDte], tbl2.[SomeOthDte]
   FROM tbl1, tbl2
   WHERE tbl1.[SomeDte] <  tbl2.[SomeOthDte] And  tbl2.[SomeOthDte]
Between  tbl2.[SomeOthDte] And  tbl1.[SomeDte]
   ORDER BY tbl1.[SomeDte] DESC;
tbl1                tbl2
SomeDte             SomeOthDte
IgnoreDte           Field1
           Field2
           Field3
How do you define "closest"?  Until you can specify that,
all we can do is guess what you want.
Your query does not appear to help explain much of anything,
because I can't figure out what it is trying to do.  It
seems to be nonsense to me because it just selects the
latest date in tbl1 that is less than any date in tbl2,
which would be a strange definition of "closest".
Note that the Between condition in the Where clause will
always be True (unless tbl2.SomeOthDte is Null).
Your example is too vague to help me clarify whatever you
are trying to say.
After we understand what you want the query to do, then
maybe(?) we can look at its efficiency.
My apologies... and thank you for responding. To clarify, I'm trying
to compare the date columns between tbl1 and tbl2 to find the closest
matching date in tbl1 then display the resulting date in the CloseDate
field.
tbl1                tbl2            CloseDate
12/31/2008  8/1/2009                7/31/2009
1/31/2009           8/12/2009       7/31/2009
2/28/2009           8/5/2009                7/31/2009
3/31/2009           8/10/2009               7/31/2009
4/30/2009           4/25/2009               3/31/2009
5/31/2009
6/30/2009
7/31/2009
* tbl1 consists only of dates (all entries are unique)
* tbl2 consists of activity detail in addition to dates (some dates
are repeated).

This example seems to imply that the "closest" date is the
latest date in tbl1 that is before the tabl2 record's date.

In that case, I think you should try:

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
                                                               FROM tbl1
                                                               WHERE tbl1.datefield < tbl2.datefield
                                                               ) As ClosestDate
FROM tbl2

- Show quoted text -

Thank you Marsh!  This is exactly what I was looking for. Again my
apologies for the vague post and my deppest thanks for your time and
patience.- Hide quoted text -

- Show quoted text -

One more question Marsh,

If I now wanted to check against a third table could I apply the same
logic?

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
FROM tbl1
WHERE tbl1.datefield < tbl2.datefield
) As ClosestDate,(SELECT max(tbl3.[datefield])
FROM tbl3
WHERE tbl3.[datefield] between tbl2.[datefield] and ClosestDate
) As XtraDte
FROM tbl2;

Thanks again.

 

[Marshall Barton]

If I now wanted to check against a third table could I apply the same
logic?

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
FROM tbl1
WHERE tbl1.datefield < tbl2.datefield
) As ClosestDate,(SELECT max(tbl3.[datefield])
FROM tbl3
WHERE tbl3.[datefield] between tbl2.[datefield] and ClosestDate
) As XtraDte
FROM tbl2;

Depending on what you mean by "check against a third table",
it might do what you want. Try it and see what you get.

If you want to do the same thing with tbl3 that you did with
tbl1, then it would be:

SELECT tbl2.*, (SELECT Max(tbl1.datefield)
FROM tbl1
WHERE tbl1.datefield < tbl2.datefield
) As ClosestDate,
(SELECT Max(tbl3.datefield)
FROM tbl3
WHERE tbl3.datefield < tbl2.datefield
) As XtraDte
FROM tbl2