How do you right a formula in a query to reference the data in a previous row for a column

[nagiese]

I am recreating a series of queries and reports in access, the original is in a system called Brio (and old querying tool by Oracle now owned by IBM, I believe). We are moving away from Brio but have no working tool. Thus we have decided to re-wright this in MS Access. I've run into some issues where there are formulas in the results from a query that reference the previous row instance of a record. I'm not sure how to rewrite a formula like this in MS Access. The Brio formula looks like this:

 if ( Base_Lot == Prior (Base_Lot) && Selling_Sector == Prior (Selling_Sector) && Acct == Prior (Acct)  && Description == Prior (Description)) { 'Drop' }

They have used the Prior function in Brio to reference the previous instance of that item in the previous row. I would really appreciate it if someone would please tell me how re-wright this in an MS Access query. So lost on this one.

Thanks for all your help.

[John_G]

Unfortunately, a query in MS Access cannot do that - there is no function similar to "Prior". Queries and recordsets can refer only to fields in the current record, and know nothing about other records in the query or recordset. And this makes sense - because a query can so easily be re-ordered a "prior" function would be meaningless, since its value would be totally dependant on the ordering.

You are probably going to have to use VBA and a recordset to achieve what you want.

[CJ_London]

there is no equivalent to Prior in Access.

databases have no sense of previous/next/first/last without some sense of order (a date/time of input or PO number for example). To do this in Access you would typically use a subquery. However there is not enough information provided to suggest what that subquery might look like - there are different ways depending outcomes required.

Suggest post the structure of your table, some sample data and what your expected outcome would be from that sample data.