Between Date Range

[joe789]

Hi Folks,

I am wondering if this is possible with a simple criteria in a query, and if so; any help on how to stucture that criteria would be greatly appreciated.

Briefly, this is for a non-profit governmental organization. I have two tables:

The first table contains a unique identifier, a flag indicating if the individual in the said span is Medicaid or Non-Medicaid, and a start and end date of the span (if the span is currently opened, no end date is populated). The primary key in the database is the Unique Identifier and the Start date of the span.

The second table contains a unique identifier which is linkable to the table above along with a date. In this table, the primary key is the unique identifier and the date.

Besides linking the unique identifier from table #1 and the unique idenfier from table #2 together in a join for a query, I also need to somehow find a way to structure the query criteria so that if the date in table #2 is between the start and end date in the span in table #1, a match exists whereby I can find out if the individual is Medicaid or Non-Medicaid.

Any help would be greatly appreciated.

Thank you very much,

Joe

 

[FoFa]

How about this?

Select Col1, Col2, Col3
FROM Tbl1
INNER JOIN Tbl2 on Tbl1.ID = Tbl2.ID
WHERE Tbl2.Date >= Tbl1.Start
AND (Tbl2.Date <= Tbl1.End OR Tbl1.End IS NULL)