Make the query work out the date inputs automatically?

[jwang036]

I have a big table with periods, daily number within each period, etc. I need
to calculate over 50 weeks' weekly sum. So I find what period that week is
in, extract the according daily number, then sum them up. I have set up 1
menu-macro with 3 queries (1. make table of 1st week. 2. repeat append into
that table. 3 sum up) to do that.

However, I still need to enter at least the start date (via query parameter
[Start Date]) of every week, so the query can go to find the according period
and I have to do it over 50 times every time I'm doing this.

The logic is very simple. As long as I enter the 1st start date, the 2nd
input is the 1st+7 days...I've tried many ways that I can think of, but
didn't work. Any idea? I'm not good at writing program in access, so I prefer
using queries to work around it. Thanks in advance.

 

[Michel Walsh]

SELECT DatePart("ww", yourDate), SUM(amount)
FROM yourTable
GROUP BY DatePart("ww", yourDate)



should sum your 'amount' per week, in just one step.

SELECT DatePart("ww", yourDate), SUM(amount), CDate(MIN(yourDate) + 1 -
DatePart("w", MIN(yourDate)))
FROM yourTable
GROUP BY DatePart("ww", yourDate)


should also return the first day of each week, again, in just one step (one
query). You type those statements in SQL view of a query. Change yourTable
for the right table name, yourDate and amount for the right fields.





Vanderghast, Access MVP

 

[jwang036]

I simplified what I'm doing. Actually the table is like:

Site Period Start Period End Daily Num
A 01/03/2008 31/05/2008 100
A 01/06/2008 30/09/2009 90
....
B
B

So if I want the week from 02/03/2008 the query will take 100*7 for each site.
if I want the week from 26/05/2008 the query will take 100*6+90*1 (because
the last date is in the next period). The queries do all those fine. My
trouble is that it doesn't work out the week start days just by following the
simple logic, say 02/03/2008 (my 1st input)+7*week number X and then just do
the whole thing again and again until it reachs the Xth (my input as well)
week.

SELECT DatePart("ww", yourDate), SUM(amount)
FROM yourTable
GROUP BY DatePart("ww", yourDate)



should sum your 'amount' per week, in just one step.

SELECT DatePart("ww", yourDate), SUM(amount), CDate(MIN(yourDate) + 1 -
DatePart("w", MIN(yourDate)))
FROM yourTable
GROUP BY DatePart("ww", yourDate)


should also return the first day of each week, again, in just one step (one
query). You type those statements in SQL view of a query. Change yourTable
for the right table name, yourDate and amount for the right fields.





Vanderghast, Access MVP

I have a big table with periods, daily number within each period, etc. I
need
to calculate over 50 weeks' weekly sum. So I find what period that week is
in, extract the according daily number, then sum them up. I have set up 1
menu-macro with 3 queries (1. make table of 1st week. 2. repeat append
into
that table. 3 sum up) to do that.

However, I still need to enter at least the start date (via query
parameter
[Start Date]) of every week, so the query can go to find the according
period
and I have to do it over 50 times every time I'm doing this.

The logic is very simple. As long as I enter the 1st start date, the 2nd
input is the 1st+7 days...I've tried many ways that I can think of, but
didn't work. Any idea? I'm not good at writing program in access, so I
prefer
using queries to work around it. Thanks in advance.