Multi-table Lookup based on Date Range

[wtucker]

Hey guys,

I've got a tricky situation. I know how it all fits together, but I can't figure out the specifics. Any help is greatly appreciated!

Here are the tables:

Claims:
ClaimID (PK)


GroupNumber
ServiceDate
ClientName

Quotes:
QuoteID (PK)
Group Number
StartDate
EndDate
Carrier

I'm trying to look up what the Carrier is for a given GroupNumber during a given ServiceDate.

-For a single GroupNumber there are multiple quotes and multiple claims.
-A Quote is an annual period of coverage. So the Start and End dates could be 1/1/2018 and 12/31/2018, or 6/15/2017 and 5/14/2018.
-There is only one Quote per GroupNumber per StartDate, and StartDates do not overlap. They are sequential periods.
-A ServiceDate is a date within the Quote Start and End dates.

I don't know if I need a lookup table, or if I can just write a SQL query.
I've been trying to create a Calculated Control on a form that's based on the Claims table to show the carrier.

This is what I have so far. I know it's still pretty far off.

=DLOOKUP("[Quotes]![Carrier]","[Quotes]","[frmClaims]![GroupNumber]=[Quotes]![GroupNumber] and [frmClaims]![ServiceDate] between [Quotes]![StartDate] and [Quotes]![EndDate]")

[Gicu]

Can you try:

=DLOOKUP("[Carrier]","[Quotes]","[GroupNumber]=" & frmClaims.[GroupNumber] " AND [StartDate] <= #" & frmClaims![StartDate] & "# AND [EndDate]>= #" & frmClaims![EndDate] & "#")

Cheers,
Vlad