How to replicate Excel's Index and Match functions

[btansug]

Hi,
I am trying to automate a data match I've been doing in Excel into Access.

Basically, I have 2 spreadsheets:
1. First has a "Start Date" for each user
2. Second spreadhsheet is a lookup table with 2 fields: Week Start Date and Week #. In other words, Dec 30 - Week 1, Jan 6 - Week 2, Jan 13-Week3, etc

For a given date in the first spreadsheet (e.g. Jan 10th), I am trying to figure out which week this falls into.
In Excel I use this formula where Column A stores the week start dates in the lookup table spreadhseet and Column B stores the Week#, with F4 being the "Start Date" in my first spreadsheet.
=INDEX('Enrollment Week'!$B$1:$B$53,MATCH(F4,'Enrollment Week'!$A$1:$A$53,1))

Is there a way to do this in Access?

Thanks in advance
Burak

 

[namliam]

How about simply using a function to calculate the weeknumber?

 

[btansug]

Thanks Namliam for your reply.
I just don't want to do any processing in Excel and have all computations, lookups, etc built into Access.
If I understand what you are saying correctly, get rid of the week_count lookup table and have a function to append the week count in the same table, am I correct?
How can I do that in Access?

Thanks
Burak

 

[namliam]

Well, weeknumber basicaly is a calculated field as a result of any given date
In an access query you can use Format or Datepart to get the weeknumber of any date,

i.e.

format(date,"WW", vbMonday,vbFirstFourDays )

And to get the start of any given week, i.e. the monday

date - weekday(date,vbMonday) +1

 

[btansug]

Thanks again Namliam.
I forgot to mention one caveat I have in the my data.
My data starts on 9/9/2013, so my Week 1 is considered to be 9/9/2013, not the 1/1/2013.
Therefore, I have the week count info in a lookup table.

I believe a SQL query should be able to do this, but still can't pull it off:

This is what I am trying but it is not giving the week with most recent week start date where my order date exceeds the week start dates. In other words, it is not deduping the results based on the "order date > week.start_date" condition

SELECT Orders.Order_ID, Orders.[OrderDate], Week.Week_Count, max(Week.Week_Start)
FROM Orders INNER JOIN Week ON Orders.[Order Date] > Week.Week_Start
GROUP BY Orders.Order_ID, Orders.[OrderDate], Week.Week_Count;

 

[namliam]

to do this in sql you need to produce an end date as well, which is quite easy to do by doing date + 7

SELECT Orders.Order_ID, Orders.[OrderDate], Week.Week_Count
FROM Orders 
INNER JOIN Week ON Orders.[Order Date] >= Week.Week_Start
               and Orders.[Order Date] <  Week.Week_Start +7

Even so a so called "Fiscal year" is still quite easy to do in another way than to use a reference table, it kindoff depends on if you want to reference your fiscal year of 9/9 to 8/9 to this year or next year, i.e. 2013 or 2014... but still quite easy for sure...
i.e. this will need some more tweaking but ...
?Datepart("WW", date - datepart("Y", #9/9/2013#), vbMonday, vbFirstFourDays)
should return 15