Summation of N Number of fields

[deepanadhi]

Hi All,

Please check the following tables.

Table1 :
--------------------
|Param | Occurence |
--------------------
| 100   |  5       |
| 150   |  7       |
| 200   |  2       |
| 250   |  5       |
--------------------

Table 2 contains around 50 fields and 3 calculated fields.

Caluclated Field 1 : Based on the first entry in Table1. In ex : it is 5, so From table 2, 5 fields from 8th field(Fixed always) should be summed and stored in Calculated Field 1.
Calculated Field 2 : Second entry in Table 1 is 7. So the next 7 columns should be summed in Table 2
Claculated Field 3 : Third entry in Table 1 is 2. So the next 2 columns should be summed in Table 2

Every time Table 1 values will be changing.


Please help on how to acheive this.

Thanks in Advance
Deepan

[Robeen]

1.
I don't think you should put calculated values in a table.
It is a much better idea to do this in a query.

2.
Is there a limit to how big the number under 'Occurence' can be?

3.
Just to make sure I understand correctly:

Caluclated Field 1 : Based on the first entry in Table1. In ex : it is 5, so From table 2, 5 fields from 8th field(Fixed always) should be summed and stored in Calculated Field 1.

In this case, your calculated field will have:
field9 + field10 + field11 + field12+ field13
Is that correct?

What are the field names of the fields that are after the 8th field?
It MIGHT be possible to refer to the fields in a table by their 'position' in the table - but I have never done that. I will post here to ask if that is possible in Access.

[Robeen]

I have posted a question regarding referring to fields by their position in the field list here:
https://www.accessforums.net/access/...tml#post122971

[deepanadhi]

Hi Robeen,

Thanks for the Reply.
Please find my comments below.

1.
I don't think you should put calculated values in a table.
It is a much better idea to do this in a query.

2.
Is there a limit to how big the number under 'Occurence' can be?

3.
Just to make sure I understand correctly:

In this case, your calculated field will have:
field9 + field10 + field11 + field12+ field13
Is that correct?

What are the field names of the fields that are after the 8th field?
It MIGHT be possible to refer to the fields in a table by their 'position' in the table - but I have never done that. I will post here to ask if that is possible in Access.

1. Yes Query wud do.
2. Max - 21 , then the next 2 occurence fields wud be lesser than 10.
3. Since i have mentioned from the 8th field, it wud be
field8 +field9 + field10 + field11 + field12

The Fieldnames are dates which is not fixed, its an everyday run program and the field names keep changing.

Thanks,
Deepan M

[Robeen]

I have posted a question regarding referring to fields by their position in the field list here:
https://www.accessforums.net/access/...tml#post122971

Deepan,
Did you follow this post that I did on your behalf?
Paul [pbaldy] had some information there. See if that helps you.

[deepanadhi]

Thansk a lot Rubeen,
For your Effort.

yes i checked the Thread and seems i need to do some coding.
will check for coding now

Thanks,
Deepan.