Compare Access tables w/ one variable as a >=

[The_Kurgan]

I have two tables. The first contains customer-level data, with a Zone, # of Units, and a date. I want to match the first table to the second (represented below) and retrieve the Limit amount. Obviously, matching based on just Zone and Units is easy.

Here's the tough part: I need the query to also look at the date from the first file and match it to the Effective date in the second whereas the Table_1 date must be greater than the Table_2 date.

For example:
If a Table_1 record has a Zone of 2, Units of 3, and date of 05/15/16, the Limit to be picked should be $974,950.
If a Table_1 record has a Zone of 1, Units of 1, and date of 01/01/17, the Limit to be picked should be $431,100.
... and so on...

If I do simple left-joins on those three fields, I get a record for each of the Limits where Zone and Units match. I'm at a loss. Any thoughts would be appreciated!



Table_2

Zone	Units	Effective	Limit
1	1	1/1/2013	$417,000
1	2	1/1/2013	$533,850
1	3	1/1/2013	$645,300
1	4	1/1/2013	$801,950
2	1	1/1/2013	$625,500
2	2	1/1/2013	$800,775
2	3	1/1/2013	$967,950
2	4	1/1/2013	$1,202,925
1	1	1/1/2016	$424,100
1	2	1/1/2016	$540,850
1	3	1/1/2016	$652,300
1	4	1/1/2016	$808,950
2	1	1/1/2016	$632,500
2	2	1/1/2016	$807,775
2	3	1/1/2016	$974,950
2	4	1/1/2016	$1,209,925
1	1	1/1/2017	$431,100
1	2	1/1/2017	$547,850
1	3	1/1/2017	$659,300
1	4	1/1/2017	$815,950
2	1	1/1/2017	$639,500
2	2	1/1/2017	$814,775
2	3	1/1/2017	$981,950
2	4	1/1/2017	$1,216,925

<tbody>
</tbody>

 

[Joe4]

There are a few ways of doing this.

One is to do it in a series of two queries, another is to use nested queries.
I will show the two query method, though the logic is the same.

The first is a query that gets the maximum Effective Date that is less than the date you are looking for.
The SQL for that query would look something like this:

SELECT Table1.Zone, Table1.Units, Table1.Date, Max(Table2.Effective) AS MaxOfEffective
FROM Table1 
INNER JOIN Table2 
ON (Table1.Units = Table2.Units) AND (Table1.Zone = Table2.Zone)
WHERE (((Table2.Effective)<=[Table1].[Date]))
GROUP BY Table1.Zone, Table1.Units, Table1.Date;

Let's call that Query1.

Now, we can lookup the associated limit with that date by linking Query1 to Table2, like this:

SELECT Query1.Zone, Query1.Units, Query1.Date, Table2.Limit
FROM Table2 
INNER JOIN Query1 
ON (Table2.Effective = Query1.MaxOfEffective) AND (Table2.Units = Query1.Units) AND (Table2.Zone = Query1.Zone);

 

[The_Kurgan]

Thank you, Joe! That works great!

If I may add a wrinkle... some records in Table1 have null values for dates. Can you see a way to retain those records and just assign the most recent Limit for that record's Zone/Units?

 

[Joe4]

Since that date is used Maxes and Joins, that is going to be problematic.
I would recommend creating a query on that first table, and use the NZ function to populate any missing dates (with the current date or some far off future date).
Then, use that Query in place of Table1 in our queries above.

 

[The_Kurgan]

I'm a little embarrassed to say I've never heard of an NZ function, but I will definitely look into that. I think I have this whole thing worked out now. There are a lot of other moving parts to this beast, but your ideas have helped tremendously. Thanks again!

 

[Joe4]

The NZ function is a handy little function to keep in your back pocket. See: https://www.techonthenet.com/access/functions/advanced/nz.php

So, if I had a field named EndDate, and I wanted any blank values to be populated with 1/1/2020, I could do so like this:

Nz([EndDate],DateSerial(2020,1,1))

However, note that the Nz function returns a Text value. So if we wanted to use this field in date comparisons or calculations, that could be problematic. However, we can simply use the DateValue function to coerce it back to a valid date value, i.e.

DateValue(Nz([EndDate],DateSerial(2020,1,1)))

 

[The_Kurgan]

Ooooohhh... I like that one!