Vlookup Question?

[jjm3066]

Hi,



I am very familiar with Excel, and now just getting into Access, so please bear this in mind when you answer my question. :-)

I have imported lots of data into multiple tables in Access. For each line item, I have a Sale Date. Our Sales Dates need to be organized by a predetermined Week Number and Period Number. I have an excel file with a list of each date for the year with what Week it falls in, as well as what Period. How can I add this to Access for a new column with all my data, and then when new data comes in each data, it assigns it automatically.

Would I use Vlookup?

FYI- there is no way I can export this data into excel and use vlookup and reimport, I have over 1 million rows of data.

[alansidman]

In a query based upon your table, add a new field and in the top row of the grid for that field type this-- Week: DatePart("ww",[SalesDate])

where SalesDate represents the field holding the sales date. This will give you the week of the year for that SalesDate.

[jjm3066]

Thank you for your reply. The only problem is that my weeks are not Calendar week dates. The are unique to our customer's calendar. How do I link my vlookup to the reference of those dates to the week?

Thanks!

[alansidman]

Since this is a unique situation and nor boilerplate, I suggest you post a sanitized version of your db with sample data. Based upon the sample data, indicate what you expect the results of the query to look like. It is difficult to imagine what you are describing now. There is no vlookup function in Access. It all happens using queries and linking on common fields. Be sure to show the correlation of the periods to the weeks from your xl spreadsheet.