determine if date in one table is between two dates in another ms access table

Question

I have 2 Access tables - an address table and an event table.

The address table has a client id, address effective date, zip code and county.

The event table has the client id and an event date.

Both tables can have multiple entries for each client id.

I need to create a report that shows what zip code/county was associated with a client ID at the time an event occurred.

So I need to determine if an event date occurred between 2 consecutive address effective dates and then return the zip code and county for the earlier address effective date for each client id.

But I also need to determine if an event date occurred after the latest address effective date and return the zip code and county for that latest address effective date.

The data would look something like this:

address table		event table
cid	addr_eff_dte	zip	county		cid	event_dte
12345	1/1/1995	11111	abc		12345	10/5/1992
12345	6/6/2004	22222	def		12345	9/4/2007
12345	4/16/2009	33333	ghi		12345	11/20/2016
12345	5/10/2015	44444	jkl

I have no idea where to start or if this is even possible within Access.   Any help offered would be much appreciated.

Answer 1

Both your requirements are the same.  You need to determine address row with the latest (MAX) date prior to or equal to the event date. You can use two subqueries in the outer query's SELECT clause, each of which is restricted to the relevant date by a further subquery which is correlated with the outer query, e.g.

SELECT Events.cid, Events.event_dte,

    (SELECT county

     FROM Addresses

     WHERE Addresses.cid = Events.cid

     AND Addresses.addr_eff_dte =

        (SELECT MAX(addr_eff_dte)

          FROM Addresses

          WHERE Addresses.cid = Events.cid

          AND Addresses.addr_eff_dte <= Events.event_dte)) AS EffectiveCounty,

    (SELECT zip

     FROM Addresses

     WHERE Addresses.cid = Events.cid

     AND Addresses.addr_eff_dte =

        (SELECT MAX(addr_eff_dte)

          FROM Addresses

          WHERE Addresses.cid = Events.cid

          AND Addresses.addr_eff_dte <= Events.event_dte)) AS EffectiveZip

FROM Events;

I've copied and pasted the dummy data you posted into two tables and can confirm that the above query does return the correct values.

Answer 2

First you need a Primary Key field in both tables so as to order the records.  

To do this create a new table with an Autonumber field as primary key and append the 'address table' records sorted by CID and addr_eff_dte.

Answer 3

Yep, that worked perfectly.   Thanks Ken !