Query Calculation with Row Value (1 Viewer)

Maiju

New member
Local time
Tomorrow, 00:00
Joined
Nov 9, 2019
Messages
4
How do I identify an observation from a different row of a data table and use it as a divisor in an expression builder in a MS Access query?

I trying to accomplish this via a series of IIf-functions. What I am trying to do is as follows:

DataField: IIf([value]>0,[observation]/[value]*100,[observation]),

IIf(row=100, observation*200/(row 5 observation here), null),

IIf(row=101, observation*200/(row 5 observation here), null),

IIf(row=103, observation*200/(row 5 observation here), null), etc

The first works all right, but I need a second, third, fourth, etc. which have an expression that uses the observation from a different row.

The syntactical challenge is with part

(row 5 observation here)

How to identify in an expression a specific value from a particular row?

Basically, the data requires a number of observation subjected to different calculation and I would like to have them all in one query.

Thanks a lot for your help.
 

isladogs

MVP / VIP
Local time
Today, 22:00
Joined
Jan 14, 2017
Messages
18,186
I don't really follow what you are trying to do but none of the IIf statements after the first will run

Is 'row' a field name? What does row 5 observation here mean?
Why do you have the same expression repeated for values 100, 101, 103 etc?

The code should possibly be something like
DataField: IIf([value]>0,[observation]/[value]*100, IIf(row=100, observation*200/(row 5 observation here), II(row =101, ),....[observation])))

Even if you get this to work, your query will be horribly slow and inefficient as this complex expression will need to be checked for every single record
 

Maiju

New member
Local time
Tomorrow, 00:00
Joined
Nov 9, 2019
Messages
4
Hi,

The data in rows 100 to 110 are calculated differently. The final data column either has (1) percentages or original observations, which are produced by the first IIf function or (2) subdata results for rows 100 to 110, to which the calculation with IIf structure for each row aims to do.

I need to use the data observation from row 5 in the function, which creates the challenge. What is the syntax from getting an observation from a row for a given data column in Access? I seem to find no references anywhere. I thought it would be a straight-forward thing, given that same data table is in question.

Thanks for any help you can give.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:00
Joined
Oct 29, 2018
Messages
21,358
Can you use the DLookup() function?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:00
Joined
Feb 28, 2001
Messages
27,001
Maiju, your problem is that you are using Excel-think, but Access is NOT Excel. Oh, the grid-like display of table-view mode would make you think that, but no, it doesn't work like that.

In Excel, EVERY CELL is independent. You can use row or column headers to impose certain formatting and take certain actions, but the Excel merely organizes content in the familiar row/column structure. Every cell can be its own value totally unrelated to the cells around it.

In Access, what you see as "rows" are actually records for which (if designed properly) every field (which you see as "columns") has something to do with some identifying field that is sometimes referred to as the "key" field. Access organizes data according to relationships - which DON'T EXIST in Excel unless you program them into a cell.

The data in rows 100 to 110 are calculated differently.

This can't happen in Access either, as the rule is that ALL RECORDS in a given table are always handled the same way.

My advice to you is to do this in Excel or else totally re-think this exercise to take into account that Access does not do what you are trying to do. It organizes data VERY differently than the layout you seem to be suggesting.

Try describing your problem in English and DO NOT USE CODE. What data do you have, what does it look like, and what is your end goal. Tell us WITHOUT NAMING SPECIFICS. No "row 100" or "column 5" nomenclature. What are you trying to do?
 

Maiju

New member
Local time
Tomorrow, 00:00
Joined
Nov 9, 2019
Messages
4
I progressed to the DLookUP direction, which seemed like a promising one and now I have the following:

DataField: IIf([value]>0,[observation]/[value]*100,[observation]) Or IIf(
![row_no]="101",[value]*200/DLookUp("value","TABLE",
![row_no]="5"),Null)

No error messages with this one. It gives a result, but unfortunately, the first IIf that gives a right result alone does not retain the validity of the results. The column gives uniformly -1.

Being able to pick up a value from the same or different table would be so useful for me at this time. But since the next stage of the data is Excel, processing the subdata there is an option. Thanks for your input.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:00
Joined
Oct 29, 2018
Messages
21,358
Hi. Good luck with your project.
 

Users who are viewing this thread

Top Bottom