joining on dates

[joemeshuggah]

is it possible to join on dates?

for example

inner join mytbl on
date() >= mytbl.eff_dt and date() <= nz(mytbl.exp_dt,date())

 

[Jeff Boyce]

Are you trying to join two copies of your table? The two you provided in
your example are both "mytbl" ... and if you only have one table, you don't
need to 'join'.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Barrows [MVP]]

is it possible to join on dates?

for example

inner join mytbl on
date() >= mytbl.eff_dt and date() <= nz(mytbl.exp_dt,date())

Joining on dates is certainly possible, but
1. As jeff says, a join does not seem relevant here, unless you are
deliberately doing a self-join?
2. If you are joining on date/time fields that store data other than
midnight, results might not be what you expect.

In any case, have you tried your idea? Did it fail? What symptoms led you to
believe it failed?
I.E., what prompted you to ask this question?

 

[joemeshuggah]

i wanted to add the date expression to the join as opposed to the where...

SELECT
M.MODULE_NM,
R.SPRT_REP_NM,
A.ACCT_GRP_NM,
RP.PROFILE_ID

FROM

(((((REP R INNER JOIN REP_PROFILE RP ON R.SPRT_REP_ID=RP.SPRT_REP_ID)
INNER JOIN PROFILE P ON RP.PROFILE_ID=P.PROFILE_ID)
INNER JOIN ACCOUNT A ON A.ACCT_GRP_ID=P.ACCT_GRP_ID)
INNER JOIN ACCOUNT_MODULE AM ON A.ACCT_GRP_ID=AM.ACCT_GRP_ID)
INNER JOIN MODULE M ON AM.MODULE_ID=M.MODULE_ID)

WHERE

DATE() BETWEEN R.EFF_DT AND NZ(R.EXP_DT,DATE()) AND
DATE() BETWEEN RP.EFF_DT AND NZ(RP.EXP_DT,DATE()) AND
DATE() BETWEEN P.EFF_DT AND NZ(P.EXP_DT,DATE()) AND
DATE() BETWEEN A.EFF_DT AND NZ(A.EXP_DT,DATE()) AND
DATE() BETWEEN AM.EFF_DT AND NZ(AM.EXP_DT,DATE()) AND
DATE() BETWEEN M.EFF_DT AND NZ(M.EXP_DT,DATE())

how can i incorporate the where portion of the sql to the joins?

 

[Bob Barrows [MVP]]

i wanted to add the date expression to the join as opposed to the
where...

SELECT
M.MODULE_NM,
R.SPRT_REP_NM,
A.ACCT_GRP_NM,
RP.PROFILE_ID

FROM

(((((REP R INNER JOIN REP_PROFILE RP ON R.SPRT_REP_ID=RP.SPRT_REP_ID)
INNER JOIN PROFILE P ON RP.PROFILE_ID=P.PROFILE_ID)
INNER JOIN ACCOUNT A ON A.ACCT_GRP_ID=P.ACCT_GRP_ID)
INNER JOIN ACCOUNT_MODULE AM ON A.ACCT_GRP_ID=AM.ACCT_GRP_ID)
INNER JOIN MODULE M ON AM.MODULE_ID=M.MODULE_ID)

WHERE

DATE() BETWEEN R.EFF_DT AND NZ(R.EXP_DT,DATE()) AND
DATE() BETWEEN RP.EFF_DT AND NZ(RP.EXP_DT,DATE()) AND
DATE() BETWEEN P.EFF_DT AND NZ(P.EXP_DT,DATE()) AND
DATE() BETWEEN A.EFF_DT AND NZ(A.EXP_DT,DATE()) AND
DATE() BETWEEN AM.EFF_DT AND NZ(AM.EXP_DT,DATE()) AND
DATE() BETWEEN M.EFF_DT AND NZ(M.EXP_DT,DATE())

how can i incorporate the where portion of the sql to the joins?

JetSQL, unlike Transact-SQL, only allows linking expressions to be part of
the joins. The Rushmore query optimizer that Jet uses is smart enough to
apply the criteria in the WHERE clause to limit the data from each table
before it makes the joins. You can see this for yourself by turning Showplan
on in the registry - see
http://articles.techrepublic.com.com/5100-10878_11-5064388.html

You could of course, use subqueries to do this, but you should be able to
avoid going to that trouble ... unless Showplan reveals that Rushmore is
failing to find this optimization.

FROM (((((
(
SELECT SPRT_REP_ID,SPRT_REP_NM FROM REP
WHERE DATE() BETWEEN EFF_DT AND NZ(EXP_DT,DATE())
) As R ...