I want query to run with summary of records weekly (not by unique.

[Guest]

I want to create query in which i needs totals of records to take place on
weekly basis. what i mean is if i have two fields date and quanity in table
and i want query which gives sum of quentity on week basis ( inbuild feature
has folloing options Unique Date/Time,Day,Month,Quarter,Year.
Any one can help??

 

[Guest]

Hi Sandip,
Here is one way. To convert you date to the # of week in that year and sum
that to get your results.
You probably could use one query to complete this, but to be clear I will
use two.

Query 1: SuggestedDateis your date field, quentity is your quentity

SELECT Year([SuggestedDate]) AS QtyYear, DateDiff("ww","1/1/" &
Year([SuggestedDate]),[SuggestedDate]) AS WkInYr, Table1.SuggestedDate,
Table1.quentity
FROM Table1;

Query 2:

SELECT Query1.QtyYear, Query1.WkInYr, Sum(Query1.quentity) AS SumOfquentity
FROM Query1
GROUP BY Query1.QtyYear, Query1.WkInYr;

The Query1 will give year, week in year, your date, and your quantity. The
reason for the year is just in case you have data expanding for more than 1
year. Then the quantity will not be included in the wrong year.

The Query2 is just sumamry of the query1 based on year and week in year.

Hope this will answer your question.