# MS Access query question - joining effective dated tables

**URL:** <https://boards.straightdope.com/t/ms-access-query-question-joining-effective-dated-tables/287627>\
**Category:** Factual Questions\
**Created:** [January 31, 2005, 6:07pm UTC](https://boards.straightdope.com/t/ms-access-query-question-joining-effective-dated-tables/287627 "2005-01-31T18:07:26Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![joemama24\_98](https://avatars.discourse-cdn.com/v4/letter/j/ce7236/32.png) [@joemama24\_98](https://boards.straightdope.com/u/joemama24_98)\
**Post date:** [January 31, 2005, 6:07pm UTC](https://boards.straightdope.com/t/ms-access-query-question-joining-effective-dated-tables/287627/1 "2005-01-31T18:07:26Z")

</div>

Can anyone help me with writing a query in MS Access using effective-dated tables. For example:

Table A = master employee transaction file  
Table B = lookup file (for example, contains department codes and names, by date)

Lets say table A, which contains employee records, has a department code attached to each record. Table B has the department code names/descriptions.  
The nature of the tables is such that anytime an employee’s data is updated, a new row is added to table A, which contains an effective date. Everytime time a new department is added, or department descriptions change, a new row is added to table B, again with the effective date. The two types of transactions can occur independently of eachother. So,

Table A contains:

Employee ID Deptcode Effective Date (of last employee update)

Table B:

DeptCode Dept Descr Effective Date (of last dept update)

I need to join table B to table A, and only pick the Department description relavent to the record I select from table A. Not only must I use DeptCode as my key, but also effective date (in case the department description has changed, I want to pick the record relavent to the date of my employee record, not the current one. The Dept table would contain both records with effective dates - I want the one relative to my A table. In Oracle/PL SQL, the following SQL would work:

SELECT A.EMPLID, A.DEPTID, B.DESCR  
FROM TABLEA A, TABLEB B  
WHERE B.DEPTID = A.DEPTID  
AND B.EFFDT =  
(SELECT MAX(B\_ED.EFFDT) FROM PS\_DEPT\_TBL B\_ED  
AND B.DEPTID = B\_ED.DEPTID  
AND B\_ED.EFFDT \<= A.EFFDT) )

Any way to do this in MS Access?

---

<div class="post-metadata">

**Author:** ![RumMunkey](https://avatars.discourse-cdn.com/v4/letter/r/b19c9b/32.png) [@RumMunkey](https://boards.straightdope.com/u/RumMunkey)\
**Post date:** [January 31, 2005, 7:16pm UTC](https://boards.straightdope.com/t/ms-access-query-question-joining-effective-dated-tables/287627/2 "2005-01-31T19:16:32Z")

</div>

The folks over at [**Utter Access**](http://www.utteraccess.com/) might be able to help. There’s a bunch of experts over there.

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [January 31, 2005, 9:50pm UTC](https://boards.straightdope.com/t/ms-access-query-question-joining-effective-dated-tables/287627/3 "2005-01-31T21:50:03Z")

</div>

I believe that you’re looking for the DMAX function…

---

<div class="post-metadata">

**Author:** ![erislover](https://avatars.discourse-cdn.com/v4/letter/e/71e660/32.png) [@erislover](https://boards.straightdope.com/u/erislover)\
**Post date:** [February 1, 2005, 1:01am UTC](https://boards.straightdope.com/t/ms-access-query-question-joining-effective-dated-tables/287627/4 "2005-02-01T01:01:22Z")

</div>

Try activating SQL mode in a new query. Does that syntax not work for Access?

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [February 1, 2005, 1:34am UTC](https://boards.straightdope.com/t/ms-access-query-question-joining-effective-dated-tables/287627/5 "2005-02-01T01:34:44Z")

</div>

No; I don’t think it will work in Access; I think you need something along the lines of:

SELECT table1.\*, DMax("[field1]","[table2]","[ClientNum] = 102") AS Expr1  
FROM table1  
WHERE (((DMax("[field1]","[table2]","[ClientNum] = 102"))\>100));

---

<div class="post-metadata">

**Author:** ![joemama24\_98](https://avatars.discourse-cdn.com/v4/letter/j/ce7236/32.png) [@joemama24\_98](https://boards.straightdope.com/u/joemama24_98)\
**Post date:** [February 1, 2005, 3:38pm UTC](https://boards.straightdope.com/t/ms-access-query-question-joining-effective-dated-tables/287627/6 "2005-02-01T15:38:12Z")

</div>

Hi,

Thanks for the replies. The SQL did not work, but I will try the DMAX syntax (thanks for the suggestion).

I did accomplish what I needed via 5 queries:

query1: JOIN both tables on the common keys, ignoring the dates (produced duplicates)

query2: SELECT \* FROM query1 WHERE where B.EffDt \<= A.EffDt

query 3: GROUP all fields in Query2, selecting MAX(B.EffDt) as the aggregate for that field.

query4: Join results of query 3 back to Table A, bringing in the MAX(B.EffDt)

query5: Join table B to query 4, using common keys and Query4.MAX(B.EffDt) = B.EffDt

DMAX looks a lot easier 🙂