attempting to get a max date where dates fit

[sergi117]

Essentially I have a table of data where actions occur on certain dates. There is a separate table with ranges that these dates fit in. a date start and date end. I want to show next to the information of these people, the starting date where their action fits in and the highest ending date where their action fits in, but the issue is this: The highest ending date can't be greater than the next effective date assuming the next person is the same name. Now if both dates share a common max end date then it can show the same date.

I'm having trouble in terms of writing something such as this. I've already found an easy solution for the starting date, but not the end. I attached an example database with two tables of data and a third called result which is essentially what I'm trying to see in the end. If anyone has any ideas, it would really help!

Example.zip



Hope I explained it well enough, but let me know if there are any questions.

edit: Next name and name column may have gotten switched when i wrote the results table.

edit2: My attempt at a solution was placing this iif statement in the criteria, but of course that would exclude records.

IIf([next name]=[name],IIf([Next Date]>=[effective date]+14,[end]<=[next date] And [end]>=[effective date],[end]>=[effective date]),[end])

Attempting to place it within the max() itself just results in 12:00:00 in the record rather than getting the outcome I'm looking for in result

[rpeare]

This is poorly designed, I don't know whether it's just a sample of what you're trying to do but you should not be storing names as anything to key off of. If it's employees you should have an employee table with and store the employee's primary key where you currently have the 'next name' and 'name' fields.

You're using a cartesian query as well which will get incredibly bogged down if you end up with a lot of data

With your current data, what are you expecting to see as an end result?

name	effective date	Next Name	Next Date	Expr1
Joe	1/16/2018	John	1/13/2018	2/11/2018
John	1/15/2018	John	1/22/2018	12:00:00 AM
John	1/22/2018	John	1/30/2018	12:00:00 AM
John	1/30/2018			2/11/2018

This is what's currently showing in your result query

Should there be null values in all of these except for the first line? or what?