• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1066
  • Last Modified:

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!
0
menauriel
Asked:
menauriel
  • 7
  • 5
1 Solution
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now