Calculation on unique entries

[cjbuechler]

I have a table of ContestEvents, a table of Entires, and a table of Contestants.



Each contestant can have multiple entries in the Entries table for a single event. As you can image that’s all easy enough.
ENTRIES looks something like this:
ENTRYID /CONTESTANTID /CONTESTID
1 /111 /2010
2 /111 /2010
3 /112 /2010
4 /112 /2010
5 /113 /2010

I run into a problem when I have one of the fields on the Entries form calculated during data entry.

The problem is I only want the calculated field to “calculate” if this is the Contestant’s first entry. If this is the second or more then it should not do the calculation on that field. Using the mock table above I would apply it to entryid 1, 3, and 5 and when someone had gone to enter in the others the coding in the form would not have applied the calculation.

I’m thinking I need a query that groups the entries table so that I get EVENTID (Group by), CONTESTANTID (Group By), and ENTRYID (Min)… I was thinking MIN since the EVENTID’s are auto-generated sequencial numbers. From there I'm not sure how to compare against that query in the coding of the form and whether or not thats the right route to begin with.

Any help would be great. Thanks!

[pbaldy]

Why 5? That's 111's third entry. I think you're on the right track. See if this helps:

http://www.baldyweb.com/LastValue.htm

[cjbuechler]

Thanks, I'll take a look at that.

You're right. I copied and pasted and forgot to edit the ContestantID.

[cjbuechler]

Update: Luckily due to the nature of the data I was able to make a query that gave me the MIN of the ENTRYID for each CONTESTANTID. There were some cases where that didn't work but they were not the norm.