Variable number of Field to add up

[hcabs99]

Hi

I have a data table which will hold several weeks worth of data , fields marked as week 1, week 2 up to and including week 9

I need to add up the data in these fields, but the number of weeks that I need to add up will need to be variable based on an entry that a user keys into a form.

So, user enters in 2, I need the database to total up the data for week 1 and week 2 ,

I know I could write 9 separate queries which would run based on the selection, but I would like to know if there is some clever coding way round this.

Any suggestions?

Cheers

 

[Joe4]

If you have a numeric field for week number (i.e. 1, 2,...,9), then you can simply use a Parameter Query to get what you want, i.e. the Criteria under the week number field should look like:

<= [Enter max week number]

Then whenever you run this query, it will prompt you for the maximum week number, and return all records less than or equal to the number entered.

 

[james_lankford]

I think he's saying his table has 9 fields
Field_1
Field_2
...
Field_9

and if the person enters a 2 then he wants to do
select Field_1 + Field_2

but if the person enters a 4 then he wants to do
select Field_1 + Field_2 + Field_3 + Field_4

 

[Joe4]

I think he's saying his table has 9 fields
Field_1
Field_2
...
Field_9

and if the person enters a 2 then he wants to do
select Field_1 + Field_2

but if the person enters a 4 then he wants to do
select Field_1 + Field_2 + Field_3 + Field_4

If that is the case, that is bad (not normalized) database structure.
You should NOT have a separate field for each week, just two fields, one for week number, and one for the value associated with that week number.
If it is structured properly like that, one can see how easy this request now becomes!