Self-join calculation?

[jamesbr]

Hi all, wonder if you can help me.

I'm trying to create a calculation field which compares similar records in a table, and I think a self-join relationship should do it, but can't figure it out!

Specifics: There is a record for each exhibition visit. Exhibitions are annual (e.g. Exhibition X 2004, Exhibition X 2005, Exhibition Y 2003, Exhibition Y 2005 etc). Where someone has visited the exhibition in the previous year I want to display the difference in price he/she paid for that year. In other words: Ticket Price Exhib X 2005 - Ticket price Exhib 2004.

Can I do this via a self-join relationship? I've tried relating a second occurance of the table via exhib name (without year) and visitor name, then creating a calculation field to compare years (if year=year-1 then calculate price difference), but it doesn't seem to work!

any help appreciated.

james

[Wim Decorte]

See attached file. A little to complex to explain here, but see if you have any questions about the setup.

There might even be better ways, I didn't spend much time on this and I don't really like the way I had to lookup. Using a recursive custom function with the GetNth function would probably better from a database-structure standpoint.

fmforums_eventAttendance.zip

[jamesbr]

Thanks for your help - i'll look at it in detail on monday, but definitely looks like it will achieve what i'm after.

thanks again

jamesb