expression using prior record attributes

[TrollBro]

Hi

I need to create a field expression to evaluate two fields from a prior record but I'm not sure how to do it. I have a table with fields including dollar amounts and dates. I can Desc sort on dollars and then dates, and need a 3rd field that evaluates the preceeding dollar and date against the currect record's dollar and date values. If they are the same, the results should return a "Y", else a "n". Qry results would look like this:

Amount Date Flag
100 1/1/2009
120 1/15/2009 "N"
120 1/15/2009 "Y"


Is there an easy way to even do this in an access query? I have to resort to an xls spreadsheet. Thanks

 

[dhookom]

I expect you need another field value since there is no primary key or way to identify the order.

Duane
Hook'D on Access
MS Access MVP

 

[TrollBro]

thanks dhookem

It's been a while, but I thought from something I worked on a number of years ago that there was a way to also rank order in an expression as well, and the logic might be similar. I was hoping to avoid creating a DAO routine outside of the qry to loop through all the records to accomplish what I'm after, but maybe that's what I'm stuck with having to do.

thanks

 

[dhookom]

I expect you have more fields than you are describing. If you don't have any unique values, then why not add an autonumber primary key?

Duane
Hook'D on Access
MS Access MVP