1. ## Calculations (Office 2000)

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

2. ## Re: Calculations (Office 2000)

Is this a lookup? Try Dlookup....

Cecilia :-)

3. ## Re: Calculations (Office 2000)

There might be a way. You would probably want to use a query or a compound query, however, since that is more of the "database language". It is also "freer" in that you dont have "anchored cells" in the same sense as in Excel.

Probably the best way to start is to write out longhand what your formula does. It is opaque to me, & i would guess to others as well. But then, maybe that's why youre asking in the first place, eh? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

4. ## Re: Calculations (Office 2000)

The Dlookup function is somewhat analogous to INDEX. Records in Access don't have a fixed order, so you can't refer to them the same way you do ranges in Excel. Instead, you tell Access, in effect, "lookup up the value for <fieldname> in <tablename> where <another fieldname> has this particular value". If you use a function like DLookup in a query (be careful, it's slow), you can refer to column names in the query, which will return the value in that column in the current record, so the dlookup would return a different answer for each row.

5. ## Re: Calculations (Office 2000)

Hi,

>>> Probably the best way to start is to write out longhand what your formula does. It is opaque to me, & i would guess to others as well. But then, maybe that's why youre asking in the first place, eh?<<<

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

6. ## Re: Calculations (Office 2000)

Are you trying to look at the value of a field in one record and then change the value of another field in another record based on that? You would normally do that in code by opening a couple of recordsets and moving through them, but the methods depend on what exactly you need to accomplish. Perhaps if you explain *why* you're trying to do this, someone can help you with a database equivalent of the result.

7. ## Re: Calculations (Office 2000)

Hi Charlotte,

Let me try to give the example another way and maybe it will clear things up.

The first 8 Fields that I have are 1st - 2nd - 3rd - 4th - 5th -6th 7th and 8th

The second 8 Fields that I have are Student #1 - Student #2 ---------Student #8

In the second 8 fields it will tell if student #1 came in 1st thru 8th with the number 1 thru 8

So what I need is if Student #1 came in 8th place is to have a number 1 put in the cell under Field 8th

I hope this makes things a little more clear, thanks again.

Bill

8. ## Re: Calculations (Office 2000)

Yes, it makes it clearer what you're trying to do, but it doesn't really make sense in a database context unless you're talking about a query rather than a table. In a database, you would have a student table that would list the students information and have a studentID to identify the unique student record. You might have another table for something like test results that would contain the student ID and a score for each test. If your 1st - 8th fields represent some sort of ranking, you would normally do this with a query that joined the student table to the test results table simply sort them in ranked order.

9. ## Re: Calculations (Office 2000)

Hi Charlotte,

Thanks for taking the time to help me.

I guess I am not understanding you or I am still not explaining what I am trying to do well enough.

If I can do this in a Query that would be fine, the only thing is the numbers in Fields 1st thru 8th are calculated not something that can be looked up.

Can they be calculated in Access?

Bill

10. ## Re: Calculations (Office 2000)

>>>>>>>>>Can they be calculated in access?<<<<<<<<

If the raw data your spreadsheet is based on is in rows, then yes this should convert nicely into an access table. You could then base queries on this raw data to do the calculations you want.

Unfortunately although an Access table looks very similar to a spreadsheet, it isn't anything like a spreadsheet. So if you're planning to convert a spreadsheet into a database you might have to think about the way you put your data together differently to suit Access.

I gather from your posts that you are putting a student / results type database together. Now the best way to design a database is to sort your information into groups, you have a group (students) and another group (results) so you would now form a table called students for simplicity this could be two fields student ID and student name. You could have another table course, which would list all the courses available. And finally you'd have a table results, a simple results table would have three fields the first one storing the student ID, the second storing the course ID and the third storing the result, you might want to add extra fields like date & tutor.

Now to get the information out, you base queries on the results table, for example; you could query a student and see all his results, or you could query a course and see all the students that went on that course. You could also query and see how many students got top marks.

I

11. ## Re: Calculations (Office 2000)

Hi Rupert,

I really appreciate all of the time you put in answering my question. Thank you

If you don't mind let me ask another one to make sure I have this clear in my mind.

>>>> You could then base queries on this raw data to do the calculations you want.<<<<

Does the above statement mean that I could do the calculations in Access or Excel?

Bill

12. ## Re: Calculations (Office 2000)

>>>>>>Does the above statement mean that I could do the calculations in Access or Excel?<<<<<<<<<<
It really depends on the type of calculation, some calculations are best suited for a spreadsheet. But if you have row like data, where all the information to be calculated can be contained in one row, then you should be able to transfer the calculations to the Access environment.

Looking at the examples of the calculations you have posted, it appears that you have a separate column for each student. The problem is that in Access you would never do this unless there was a particularly special case. So you are now at a stage where I was, and most other access programmers were when they first started trying to program in Access, you are thinking about your data as it appears in XL.

You now have to arrange your thinking and your data differently so that Access can work on it.

Could you give a brief description of the layout of your spreadsheet?
And also could you indicate the type of information you want to extract from it?

13. ## Re: Calculations (Office 2000)

Hi Rupert,

Thanks for all of your help. Let me go over this for a couple days then I will get back with you if that's ok.

Bill

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•