I'm looking for some help writing a rather complex query against my Access database. I have no dedicated SQL experience, only advanced Excel/Access experience.
I have a single database setup that has orders going back 6 years.
I'm trying to compare the Historical orders with the Current orders and flag the Current Orders that fall within a -120 & +90 date window around the expected contract End Dates from the Historical Orders. Problem is that for large customers, they can have hundreds of orders, all with different date ranges, and they all don't need to be flagged. Only the ones that fall within the window in which we expect the next order to be placed.
I've created unique customer ID's for all customers so that part is not the problem. Only the date window piece.
Any help would be much appreciated. I'm open to exploring all options as well, this doesn't necessarily need to be done via SQL if there is a better known way.