KollBrian
asked on
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
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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
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
I didn't make any headway on the syntax error yesterday. there doesn't seem to be an obvious reason for a failure... :(
Brian
ASKER
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,
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
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,
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
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