Querying one row with the cell in the previous row | Access forum

Hi Nicholas, thanks for your question. The answer lies in the title of your own post - this has to be done using a query. Calculated columns are part of the meat and drink of an Excel spreadsheet, but it's very bad practice to try to do this to your database. Access 2010 allows you to add calculated columns to tables but this will result in a table that cannot be opened in previous versions of Access. It also contravenes the basic rules of normalisation and create big problems down the line.

So the answer is to create a query to display everything in a table, but to append a calculated column (created using the expression builder). The expression you need to create will take the form of a basic IIF expression, but the logical test you need to write will be the complicating factor. You need to test whether, say, the employee salary is greater than the salary of any field where the Employee Name is "CEO". The Expression Builder makes this a complicated thing to write and most database administrators would write this using raw SQL. Writing this for you is a bit beyond the scope of this forum, I'm afraid as is very dependent on the nature of your database but here's a basic introduction to calculated fields.

http://allenbrowne.com/casu-14.html

Hope this helps,

Anthony