Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2019, 05:02 AM   #1
Maiju
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Maiju is on a distinguished road
Query Calculation with Row Value

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.

Maiju is offline   Reply With Quote
Old 11-09-2019, 05:34 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,951
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query Calculation with Row Value

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-09-2019, 05:55 AM   #3
Maiju
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Maiju is on a distinguished road
Re: Query Calculation with Row Value

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.

Maiju is offline   Reply With Quote
Old 11-09-2019, 06:18 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Query Calculation with Row Value

Can you use the DLookup() function?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Maiju (11-09-2019)
Old 11-09-2019, 07:17 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,540
Thanks: 92
Thanked 1,680 Times in 1,559 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Query Calculation with Row Value

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.

Quote:
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?
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Maiju (11-09-2019)
Old 11-09-2019, 08:07 AM   #6
Maiju
Newly Registered User
 
Join Date: Nov 2019
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Maiju is on a distinguished road
Re: Query Calculation with Row Value

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([TABLE]![row_no]="101",[value]*200/DLookUp("value","TABLE",[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.
Maiju is offline   Reply With Quote
Old 11-09-2019, 08:11 AM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Query Calculation with Row Value

Hi. Good luck with your project.


__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculation Query Jul Queries 5 10-26-2017 08:26 AM
Calculation in a query mashoutposse Queries 0 03-12-2011 10:03 AM
Calculation in a query TashaSpicer Queries 1 09-18-2009 12:55 PM
Calculation Query steve09 Queries 1 02-27-2007 05:32 AM
Help with calculation query torok Queries 5 06-14-2004 12:30 PM




All times are GMT -8. The time now is 08:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World