Calculations

[Billionz]

Hi,

This is a calculation that I use in Excel =INDEX($AJ$2:$AQ$2,1,OFFSET($AI$4,-2,MATCH(COLUMN()-19,$AJ4:$AQ4,0),1,1))

Is there a way that I can perform this calculation in Access?

Thanks
Bill

 

[Billionz]

Hi,


This is what I am trying to do in Access.

Field#1___Field#2___ Field#3___Field#4___ Field#5___Field#6___ Field#7___Field#8
__5________6_________7_______8_________4________3_______2________1

Ref.#1____Ref.#2____ Ref.#3____Ref.#4___ Ref.#5____Ref.#6____ Ref.#7____Ref.#8
__8_________7________6_________5_______1_________2________3________4

I am comparing the numbers in Fields (Ref. 1 thru Ref. 8) to the Ref. number itself. Then I move that number (the reference #) to the corresponding Field #.

For Example: If Ref. #1 had the number 1 in it then the number 1 would be placed in
__________________Field #1
If Ref. #1 had the number 2 in it then the number 1 would be placed in
__________________Field #2
Etc…..

If Ref. #2 had the number 1 in it then the number 2 would be placed in
__________________Field #1
If Ref. #2 had the number 2 in it then the number 2 would be placed in
__________________Field #1
Etc…

Thanks for all of your help.
Bill

 

[Rich]

You are trying to recreate a spreadsheet in Access, which is incorrect.
In very simple terms you only need two fields, RefID and Position .
Position numbers from 1 to 8 would be selected from a combo box for each record entry.
There have been many posts here regarding normalisation, search here for more help on the subject. No calculation is needed