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!
menaurielAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

menaurielAuthor Commented:
Correction to previous:  to do in Excel, it would be vlookup formula PLUS 7 because it returns the next LESS matching value.
0
ScottMorrisCommented:
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

Open in new window

0
dqmqCommented:
select a.*, w.weekending
from actuals as a inner join tblweeks as w
on a.check_date between w.weekbegin and w.weekending

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

menaurielAuthor Commented:
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.
0
ScottMorrisCommented:
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.
0
menaurielAuthor Commented:
I think the attached might help.  It's how I would update payment week if I was using Excel.
access-vlookup.xlsx
0
ScottMorrisCommented:
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

Open in new window

0
menaurielAuthor Commented:
Ok so that works great as Select..how do I convert the syntax to update Pay Week to the value you calculated?
0
ScottMorrisCommented:
It would be something like this:
UPDATE Actuals, tblWeeks SET Actuals.pay_week = [tblWeeks].[weekEnding]
WHERE ([check_date]+1) Between [weekbegin] And [weekEnding]

Open in new window

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
menaurielAuthor Commented:
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!!!
0
menaurielAuthor Commented:
It worked great...now I just have to figure out how it worked.
0
ScottMorrisCommented:
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?
0
menaurielAuthor Commented:
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.
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 Applications

From novice to tech pro — start learning today.