Access formulas

[Joseph Cheong]

Hello World,

I am a newbe here. Need some help. On an Access Query table, so far I am able to do expressions to calculate between fields on the same row. I am wondering if it is possible to do expressions involving cells on the same column but different row. eg =IF (B2 = "ABC",B2,B1) That formula is of course possible on excel but I cant figure out how to do this on access Query Table.

Kind Regards
Joseph

[pbaldy]

One way to get the value from the previous row:

http://allenbrowne.com/subquery-01.html#AnotherRecord

[razors1968]

If I understand you correctly you want to be able to set a value in your current row based on a value in a different row?

Is this the row previous or do you need to find a specific row based on the value? A little more detail on what you are trying to achieve would be useful, almost all things are possible it is just a matter of defining exactly what.

Cheers

Ray

[Joseph Cheong]

[IMG]D:\! MRP Detail Report\Formula Example[/IMG]

[Joseph Cheong]

0	A	B	C	Result of the formula in column C
1	Tango	Apple	If A1 = Tango, B1, C0	Apple
2	T	123	If A2 = Tango, B2, C1	Apple
3	T	343143	If A3 = Tango, B3, C2	Apple
4	T	34365	If A4 = Tango, B4, C3	Apple
5	T	L;agl;k	If A5 = Tango, B5, C4	Apple
6	Tango	Zebra	If A6 = Tango, B6, C5	Zebra
7	Z	2222	If A7 = Tango, B7,C6	Zebra
8	Z	Uuuuu	If A8 = Tango, B8,C7	Zebra
9	Z	Kkkkk	If A9 = Tango, B9,C8	Zebra

[Joseph Cheong]

Thats what I want to do

[razors1968]

Hi Joseph

Have a look at using a DLookup() function

http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

This function allows you to retrieve information from a specific field from a specific row with a table or query. Expression is the field name, domain is the table or query name, the criteria is like the WHERE condition of a query.

Let me know if this makes sense for your problem.

Cheers

ray

[Joseph Cheong]

Hello Ray, I am preatty new at Access and especially DLookup functions. I will give it a go. Thanks for pointing me in the right direction

[June7]

There is no C0 cell in the sample table. Even if there were, I don't see how this can work at all. You are trying to apply spreadsheet functionality to a relational database. Will drive you nuts.

[Joseph Cheong]

Hmmmm, How do I DLookup 1 cell above?

[Joseph Cheong]

Hmmmm, I am still stuck. How do I DLookup 1 cell above in a query table

[ItsMe]

Hmmmm, I am still stuck. How do I DLookup 1 cell above in a query table

Specific to this question
http://support.microsoft.com/kb/101081

However, it seems you are not using Access as it is intended. You may benefit from studying Relational Database fundamentals.
https://www.accessforums.net/tutoria...ase-45451.html

[June7]

Also, review post 9.