Creating a query looping through records and summing the values 1

[Sherylj]

I have a report I'm trying to build that they want to take the first seven records, add them together (sum) then continue counting the next 7 records, sum that, and so on. Is it possible to do this in Access? I'm not sure this is the best way to do this.

They have a record for each day of the week and they are trying to get a weekly total for each week, within a monthly report.

Thanks,
Sherylj

 

[sko]

The report wizard should give you a place to start.

Base the report on your table or query. Bring in the date and value fields. The wizard will ask if you want any grouping levels. Bring the date field in twice. It will default to "Month". Use the grouping options button to change one to "Week". Add your summary options.

 

[tlbroadbent]

Queries aren't made to loop through records. However, they can do an excellent job of summarizing data. In SQL, we have to stop thinking procedurally and start thinking in terms of sets of related data.

The concept of the first 7 records isn't a relational concept. What you have described is a summary by week. In a relational database, there is no guarantee that the "first" 7 rows will all belong in the same week, nor the next 7 and so forth. (However, Access does break relational rules in this regard.)

I assume your table has a date on it. From that date you can derive a week. Quantities can then be aggregated or summarized by week.

Example:

Select
Year(DateColumn) As TheYear,
Datepart("ww",DateColumn) As TheWeek,
Sum(Amount) As TotAmount
From YourTable
Group By
Year(DateColumn),
Datepart("ww",DateColumn)

Hopefully, this example will provide some help. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.