Sum By Week ?????

  • Thread starter Thread starter Tim N
  • Start date Start date
T

Tim N

I've got a table, consisting of a part #, Quantity
Shipped, and Date (Date is Short Date).
I'm trying to SUM the quantities shipped by Week.
When I use the wizard, Week isn't in the Group Options.

Any thoughts????????

Thanks,
Tim
 
I am a novice, but try using a select query:

SELECT Part#, Quantity (COUNT)
FROM TableName
WHERE Date BETWEEN='6/14/2004' AND BETWEEN='6/20/2004'

Hope it works!
 
My apologies.. deleted the second BETWEEN=.
-----Original Message-----
I am a novice, but try using a select query:

SELECT Part#, Quantity (COUNT)
FROM TableName
WHERE Date BETWEEN='6/14/2004' AND '6/20/2004'

Hope it works!
.
Click to expand...
 
Laura,

Thanks for the reply. What I'm looking for though, is to
be able to run a query that SUMs each weekly total rather
than me extracting only the data for one week at a time.
Similar to what you'd do if you selected "Month" in the
Group options.
I suppose that I must first need to define what a week
is - Sunday to Saturday - Weekdays only - etc... But I'm
completely stumped on where to begin.

Tim
 
Laura,

Thanks for the reply. What I'm looking for though, is to
be able to run a query that SUMs each weekly total rather
than me extracting only the data for one week at a time.
Similar to what you'd do if you selected "Month" in the
Group options.
I suppose that I must first need to define what a week
is - Sunday to Saturday - Weekdays only - etc... But I'm
completely stumped on where to begin.

Tim
 
Hi Tim,

If you changed fieldname "Date" to a
non-reserved word like "ShipDate",

to sum *all parts* shipped by week:

SELECT
Sum([Quantity Shipped]) As SumShipped,
Max([ShipDate]) As SortDate,
DatePart('ww',[ShipDate]) & "-" & Year([ShipDate]) As WeekGroup
FROM
yourtable
GROUP BY
DatePart('ww',[ShipDate]) & Year([ShipDate])
ORDER BY
Max([ShipDate])

to break out sums for each part #:

SELECT
[part #],
Sum([Quantity Shipped]) As SumShipped,
Max([ShipDate]) As SortDate,
DatePart('ww',[ShipDate]) & "-" & Year([ShipDate]) As WeekGroup
FROM
yourtable
GROUP BY
[part #],
DatePart('ww',[ShipDate]) & Year([ShipDate])
ORDER BY
Max([ShipDate])

Please respond back if I have misunderstood.

Gary Walter
 
make that:
If you changed fieldname "Date" to a
non-reserved word like "ShipDate",

to sum *all parts* shipped by week:

SELECT
Sum([Quantity Shipped]) As SumShipped,
Max([ShipDate]) As SortDate,
DatePart('ww',[ShipDate]) & "-" & Year([ShipDate]) As WeekGroup
FROM
yourtable
GROUP BY
DatePart('ww',[ShipDate]) & "-" & Year([ShipDate])
ORDER BY
Max([ShipDate])

to break out sums for each part #:

SELECT
[part #],
Sum([Quantity Shipped]) As SumShipped,
Max([ShipDate]) As SortDate,
DatePart('ww',[ShipDate]) & "-" & Year([ShipDate]) As WeekGroup
FROM
yourtable
GROUP BY
[part #],
DatePart('ww',[ShipDate]) & "-" & Year([ShipDate])
ORDER BY
Max([ShipDate])

Please respond back if I have misunderstood.

Gary Walter


Thanks for the reply. What I'm looking for though, is to
be able to run a query that SUMs each weekly total rather
than me extracting only the data for one week at a time.
Similar to what you'd do if you selected "Month" in the
Group options.
I suppose that I must first need to define what a week
is - Sunday to Saturday - Weekdays only - etc... But I'm
completely stumped on where to begin.

Tim
Click to expand...
Click to expand...
 
Tim

I have had the same problem and came up with the solution by creating a
function as follows:

Function GroupByWeek(dtDate As Date) As String
'Input: A Date
'Purpose: To group dates by week and identify the start date and end date
of the week
'Returns: A string depicting the week i.e. "01/02/02 - 07/02/02"

Dim dtSunDate As Date
Dim dtSatDate As Date

dtSunDate = dtDate + 1 - WeekDay(dtDate)
dtSatDate = dtDate + 7 - WeekDay(dtDate)
GroupByWeek = dtSunDate & " - " & dtSatDate
End Function

So your query could look like SELECT GroupByWeek([YourDATE]) as Week,
Sum([Quantity]) As WeekTotals FROM YourTable GROUP BY
GroupByWeek([YourDATE]);

The GroupByWeek function can be changed to reflect different start and
finish days for the week.

Cheers

Mark
 
Back
Top