Days to work weeks

[phd4212]

Hi,

All of my information is stored daily. I am looking to create a query to
extract and combine a week's worth of data. I have no clue how to do
this...anyone able to help?

Thanks

 

[John W. Vinson]

Hi,

All of my information is stored daily. I am looking to create a query to
extract and combine a week's worth of data. I have no clue how to do
this...anyone able to help?

Thanks

With an appropriate query. What's your definition of "a work week" (it may
differ from company to company)? In what way do you want to "combine"?

 

[Daryl S]

phd4212 -

Take a look at all the date functions - they can do a lot for you. The
basic way to group data by week based on a date would be something like this:

SELECT DatePart("ww",[YourDate]) AS WeekNumber, Sum(YourNumericField) AS
SumOfData, Count(YourPrimaryKey) AS CountOfRecords
FROM YourTableName
GROUP BY DatePart("ww",[ReadDate]);

This will give you the week number of the year. If you are crossing years,
you may want to include the year with it, something like this:

SELECT Year([YourDate]) & "_" & DatePart("ww",[YourDate]) AS WeekNumber,
Sum(YourNumericField) AS SumOfData, Count(YourPrimaryKey) AS CountOfRecords
FROM YourTableName
GROUP BY Year([YourDate]) & "_" & DatePart("ww",[ReadDate]);

Start with something like this, using your table and fieldnames. If you run
into issues, post your SQL and let us know what isn't working.

 

[Dirk Goldgar]

Hi,

All of my information is stored daily. I am looking to create a query to
extract and combine a week's worth of data. I have no clue how to do
this...anyone able to help?

The DatePart function can tell you what week of the year a date is in. For
example,

?DatePart("ww" ,Date)
14

Look it up in the online help for details.

Now, you did say "work weeks" in your subject. If you want your weeks to
run from Monday to Sunday, you may need to provide an extra argument to
DatePart, specifying the start day. For example (noting that #4/4/2010# is
a Sunday),

?DatePart("ww" ,#4/4/2010#)
15
?DatePart("ww" ,#4/4/2010#, 2)
14