Complicated Access Query Help

Hello,
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.
Brian
Database2.accdb
KollBrianAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
to start out with, get the most recent [book_date] and next book_date for each name. I believe the following should work.

SELECT [Name], Max(iif([Book_Date] <= Date(), [Book_Date], NULL)) as Recent
MIN(iif([Book_Date] > Date(), [BookDate], NULL as NextBooking
FROM tbl_rooms
GROUP BY [Name]

If that gives you a good result then we can move on.

Sent from my iPad
0
KollBrianAuthor Commented:
HI Dale Fye,

So far I am hitting a "duh" wall.  The access program is coming up with "the select statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect error.
When I click off the error it seems to always land on the MIN part of the equation.  if I delete that line the query works.

I have put the query into notepad and blown it up to font 30 to compare lines to make sure I don't have a missing comma or something but I cant find any fault with your code...

Thoughts?
Brian
0
PatHartmanCommented:
There are missing parentheses in the example.

SELECT [Name], Max(iif([Book_Date] <= Date(), [Book_Date], NULL)) as Recent
 MIN(iif([Book_Date] > Date(), [BookDate], NULL)) as NextBooking
 FROM tbl_rooms
 GROUP BY [Name]
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dale FyeCommented:
Thanks, Pat, for adding that. I've been out of the office all day.

Brian,

Hopefully, the fix Pat made will take care of the first part of the problem.  Assuming it does, you would use that as a subquery, like:

Select mySub.[Name], mySub.Recent, mySub.NextBooking, tbl_bgc.bgc_date
, iif(mySub.Recent <=tbl_bgc.bgc_Date, tbl_bgc.bgc_Date, mySub.NextBooking) as Computed
FROM (
SELECT [Name]
, Max(iif([Book_Date] <= Date(), [Book_Date], NULL)) as Recent
. MIN(iif([Book_Date] > Date(), [BookDate], NULL)) as NextBooking
 FROM tbl_rooms
 GROUP BY [Name]) as mySub
LEFT JOIN tbl_BGC on mySub.[Name] = tbl_BGC.Name

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.

I've added a couple of fields to the outer query to help you compare the Recent, bgc_date, NextBooking, and the computed column.  You don't probably need those extra columns.  You might however, need to change the JOIN from a LEFT Joint to an INNER JOIN if you are only concerned with individuals how have a reservation in the past and one in the future.  You might also have to make changes if tbl_bgc contains more than one record per name (I cannot tell because I am working from my iPad and cannot open your database).

Dale
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KollBrianAuthor Commented:
Hi Gang,
Sadly this stupid Access is tossing the syntax error, I had noticed the parentheses as well but still erred.
let me bang my head over the weekend, maybe I can figure out what MS Access syntax issue is.

:)
Brian
0
Dale FyeCommented:
Brian,

I'll try to download your database when I have access to my computer and take a closer look at your data and see whether I can get that query to work the way I expect.

Dale
0
KollBrianAuthor Commented:
Thanks Dale,

I didn't make any headway on the syntax error yesterday.  there doesn't seem to be an obvious reason for a failure... :(

Brian
0
KollBrianAuthor Commented:
Hi Gang,

Ok, we are almost to the promised land :)
Many thanks for all the help.  

Current copy of the working Access SQL:
qry_approval_required:
Select mySub.[Name], mySub.Recent, mySub.NextBooking, tbl_bgc.bgc_approval_date
 , (iif(mySub.Recent >= tbl_bgc.bgc_approval_date, tbl_bgc.bgc_approval_date, mySub.NextBooking)) as STATUS
FROM ( SELECT [Name],
Max(iif([Book_Date] <= Date(), [Book_Date], NULL)) as Recent,
MIN(iif([Book_Date] > Date(), [Book_Date], NULL)) as NextBooking
FROM tbl_rooms
GROUP BY [Name]) as mySub
LEFT JOIN tbl_BGC on mySub.[Name] = tbl_BGC.variety_act;

I am using this query to build the final query which will tell someone in plain speak "do an BGC or not to".

Thanks Guys,
Brian
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.