MS Access query question - joining effective dated tables

Factual Questions
1

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?

2

The folks over at Utter Access might be able to help. There’s a bunch of experts over there.

3

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

4

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

5

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));

6

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 :slight_smile: