Summing Daily into Weekly

[Rita Palazzi]

Office 2000
MS Windows XP Professional


I have a table that collects one value for each day of the week for a
specific location. The table is simply two columns, Date and Total.
Some locations have 5-day weeks (Mon-Fri), others have 6-day weeks
(Mon-Sat). Currently, I'm analyzing the table in Excel and physically
adding the days to obtain the weekly total. Is there a way to write a
query that will add the appropriate number of entries to obtain a weekly
value for each location?

Thanks in advance for any help you can provide!!

Rita Palazzi
Senior Engineer / Global Trade Services
FedEx Express

 

[Gary Walter]

I think you are asking for:

SELECT
Sum([Total])
FROM yurtable
GROUP BY
DatePart("ww",[Date])

Also, it might be a good idea to change
name of field for "Date" since Date
is an Access reserved name

 

[Rita Palazzi]

Thanks, those are not the actual column names... hypotheticals, sorry if
I confused you. Also, I forgot to mention that the date comes in as
text i.e. YYYYMMDD, which is a real pain because "DatePart" won't work
on it. I guess I could try to parse it into an actual date field. Will
work on that...
Thanks for your input!!

I think you are asking for:

SELECT
Sum([Total])
FROM yurtable
GROUP BY
DatePart("ww",[Date])

Also, it might be a good idea to change
name of field for "Date" since Date
is an Access reserved name

Office 2000
MS Windows XP Professional


I have a table that collects one value for each day of the week for a
specific location. The table is simply two columns, Date and Total. Some
locations have 5-day weeks (Mon-Fri), others have 6-day weeks (Mon-Sat).
Currently, I'm analyzing the table in Excel and physically adding the days
to obtain the weekly total. Is there a way to write a query that will add
the appropriate number of entries to obtain a weekly value for each
location?

Thanks in advance for any help you can provide!!

Rita Palazzi
Senior Engineer / Global Trade Services
FedEx Express