I need advice I don't know how to achieve this task, though I am certain it can be done.
I have two tables. The goal is to create a query that shows me either Approve or BGC_Req next to each name.
tbl_bgc, has "name" and "bgc_date" columns
tbl_rooms, has "name", "book_date", and several other fields.
What I need to do is a calculation/results query. First is to find the most recent date in tbl_rooms.[book_date] field for each name. the challenge is that this field has all "booked dates" for the year, I need the one prior to or equal to todays date (i.e. if booked every month, what was the most recent date BEFORE or Including today.)
Then I take that date and see if it's GREATER than tbl_bgc.[bgc_date]. if greater than move to next part of formula with that value, IF NOT... I need it to use the tbl_bgc.[bgc_date] for the next part of the formula.
now the hard parts... I need to take the results of the prior formula and subtract that date from the "next scheduled date" in the tbl_rooms.[book_date] field for that name.
I.e. take John Smith for example... tbl_bgc.[bgc_date] = 8/14/2014, and the prior book date was 3/1/15 (earlier or including today) then use 3/1/15. Next... use "next date" 4/1/15 - 3/1/15 (results from prior math) = 30... IF results < 90 days then "approved", else "bgc_req".
The goal is to see which is greater, the bgc_date or the most recent book_date, use that result and then see if it's more than 90 days ago, if older than 90 days say "BGC Required" if not say "approved"
I know this is a tough "how to" but I simply cannot find anything online that discusses how to build this type of query. I am sure it uses several inner joins, and I think it will involve "IIF" function but.... I cant figure out how.
Any advice is welcome.