Adding Month Number in Database

R

RFJK65

Hello,

I have just finished building a database according to the week number. All
information is sorted this way and imported from excel. At this point in
time the database has over 600,000 records. What I was wondering is if
anyone knew how to incorporate adding in the month number as a column without
having to go through each record and adding it in? I have tried to make a
list and copy and paste apphend it in there like I do with the rest of the
data, but that does not work, and just copy and paste does not work either.
It winds up copying over data going across. Is there anyone out there that
can help me with this problem. Thanks.
 
W

Wayne Morgan

It could be done using an Update Query after you've created the new field in
the table; however, you shouldn't need to. If you have the date available in
each record you can compute the week, month, year, etc as needed. To do
this, create a query based on the table and add a calculated field to the
query. Use the Format or DatePart function to get the part of the date that
you want. Be aware that Format returns a text value that you may have to
convert back to numeric for proper sorting if you sort on the field.
 
R

RFJK65

No I do not have the exact date in the database. All I have is the week
number of the year. There is also a pivot table off of this database in
Excel. That is how I am calculating all of my figures. The database is just
mainly my storage area since there is so much data.

Thanks
RFJK
 
W

Wayne Morgan

The question then becomes, how do you want to handle weeks that span more
than one month (i.e. the first of December is a Wednesday)?

Assuming the month that the Sunday of the week is in, the following formula
will convert the week of year into the Sunday for that week (all on one
line, the newsreader has wrapped the formula):

DateAdd("d",((WeekOfYear-1)*7) - Weekday(DateSerial(YearRequested,1,1)) +
1,
DateSerial(YearRequested,1,1))

Once you have the date, getting the Month number is just a matter or using
the Month function.

Month(DateAdd("d",((WeekOfYear-1)*7) -
Weekday(DateSerial(YearRequested,1,1)) +
1,
DateSerial(YearRequested,1,1)))

You could use this formula in an Update Query to convert the week of year
field into the month that the week starts in. Assign this value to your
MonthNumber field. The formula is dependent on knowing the year as well.
 
Top