How can I simulate vlookup between 2 tables using a query in Access 07?
I want to update a payment weekending field based on the check date using an update query. If I were doing this in excel, I would simply use vlookup (check_date, tblweeks, 1, TRUE) where true gives me the next highest value in the table. I have used numerous iterations of DLOOKUP to attempt to return the weekending value based on the check_date. Since the check_date and weekending value will only sometimes match, I cannot figure a way to create a simple relationship that will do the trick. Here is the relevant table information:
Table 1: Actuals
Field: check_date formatted as date/time (has a check date)
Field: pay_week formatted as date/time (to be updated)
Table 2: tblWeeks
Field: weekbegin (formatted as date/time)
Field: weekending (formatted as date/time)
Field: weeknumber (formatted as number)
I can't use the date (ww) function because my days end on Friday, not Sunday, unless that can be configured too. I'd be open to using the weeknumber if I can decide what day the week ends and which week is week 1.
Thanks!
Table 1: Actuals
Field: check_date formatted as date/time (has a check date)
Field: pay_week formatted as date/time (to be updated)
Table 2: tblWeeks
Field: weekbegin (formatted as date/time)
Field: weekending (formatted as date/time)
Field: weeknumber (formatted as number)
I can't use the date (ww) function because my days end on Friday, not Sunday, unless that can be configured too. I'd be open to using the weeknumber if I can decide what day the week ends and which week is week 1.
Thanks!
Try making a query something like this and see if you get a result. If it's something you like, modify it to be an UPDATE query to modify your field.
SELECT check_date, weeknumber
FROM Actuals, tblWeeks
WHERE check_date >= weekbegin
AND check_date <= weekEnding
select a.*, w.weekending
from actuals as a inner join tblweeks as w
on a.check_date between w.weekbegin and w.weekending
from actuals as a inner join tblweeks as w
on a.check_date between w.weekbegin and w.weekending
ASKER
Scott, your select can be done in the grid but the logic fails because I want it to update using the next possible weekending date based on the check date and this only limits the check numbers to those within my full range of dates.
dqmq: I keep getting the error Between Operator without AND in query expression "a.check_date.....
Since I'm not a SQL programmer, I don't understand the statement enough to fix the problem.
dqmq: I keep getting the error Between Operator without AND in query expression "a.check_date.....
Since I'm not a SQL programmer, I don't understand the statement enough to fix the problem.
Forgive me for misunderstanding, but can you provide an example where you'd have a record with a check_date that doesn't fall within one of your weeks? Are your "weekbegin" and "weekending" values the corresponding Monday and Friday (instead of Sun-Sat or Sat-Fri, etc)? That may help to give an answer that fits your specific needs.
ASKER
I think the attached might help. It's how I would update payment week if I was using Excel.
access-vlookup.xlsx
access-vlookup.xlsx
Ok, based on the sample data, try this query.
SELECT check_date, weekEnding AS [Pay Week]
FROM Actuals, tblWeeks
WHERE (check_date+1) BETWEEN weekbegin And weekEnding
ASKER
Ok so that works great as Select..how do I convert the syntax to update Pay Week to the value you calculated?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So you ruined my perfectly hideous workaround. This worked beautifully. Perhaps learning SQL backend and VBA backend in the same month is too much for this shadow IT person. Maybe if you so desire you can explain or point me to a page that explains how you worked that out...I see how it looks in the grid but my brain isn't wrapping around it.
Thanks!!!
Thanks!!!
ASKER
It worked great...now I just have to figure out how it worked.
First, I created a sample database with the data from the spreadsheet that you posted. Then, I ran my first query (from #31383110) against your data and pasted the results back into Excel so that I could compare the results. I noticed that it worked for all cases except when the check date was the same as the week end date.
After realizing the basis of the problem, I added one day to the check date to make sure that there was at least one day to process the check. That's where the "([check_date] + 1) BETWEEN [weekBegin] AND [weekEnding]" came into play. (Although I do have to give credit to dqmq for using the BETWEEN; it's much more reader-friendly than the >= and <= that I had originally used.)
Anyways, that was the logic behind WHY I did it the way that I did. Are you asking more about how the SQL works specifically?
After realizing the basis of the problem, I added one day to the check date to make sure that there was at least one day to process the check. That's where the "([check_date] + 1) BETWEEN [weekBegin] AND [weekEnding]" came into play. (Although I do have to give credit to dqmq for using the BETWEEN; it's much more reader-friendly than the >= and <= that I had originally used.)
Anyways, that was the logic behind WHY I did it the way that I did. Are you asking more about how the SQL works specifically?
ASKER
More regarding the trick of using a dummy field to carry criteria while somehow getting the query to realize that I wanted the appropriate weekending. I see your point about the +1, and I was wondering about that, but more of how you got the query to identify the right weekending date and then populate it in the pay week field. If you don't hear back from me for a bit, I'm on vacation as of an hour ago and will check back Monday to see what words of wisdom you can offer.
ASKER