0

I have Access 2010 and am trying to create a table that logs rugby games. To make life easier, I would like to give each match a unique reference. (This may get used as the primary key later, but at present does not need to be).

I want the Unique ref to consist of:

Year-Comp-MatchNo

Now, getting the Year & Comp is easy, that's just a concatenation. But the match number I want to be smart.

I would like it to Number the games based on the date of the match in a given year, and start at 1 for the first game of each comp.

For example, the first game of the 2011 Six nations comp would have a ref of 2011-6N-01

The first game of a Rugby World cup in the same year would be 2011-RWC-01

The matches can be entered in any order, so if I enter a Six nations game which was played on the 1st March first, I will get a 1, if I then enter a six nations game that was played on the 1st Feb of the same year, I expect that to become no. 1, and the previous entry to become No. 2.

I have searched! And found this: Reset counter ID to 1 for every new year in MS Access

It provides the number I am looking for, but only in order of data entry, not in order of the date given.

The code I currently have is exactly as the example there.

Hope I made that clear enough to understand, and I hope to get an incredible smart person to be able to show me how to do it.

Thanks everyone in advance.

Community
  • 1
  • 1
RBurns
  • 83
  • 7
  • So if you enter a record for the 2011 RWC it should set the match to 2011-RWC-01 and then a month later when you enter another record for the 2011 RWC that was a game that happened earlier in the year the existing record should change its match value to 2011-RWC-02 and the new record should become 2011-RWC-01? So inserting a record would somehow have to trigger an update operation? – Jerry Jeremiah May 27 '16 at 03:57
  • Is this for a database which will never be used by more than one person at a time? – HansUp May 27 '16 at 04:24
  • Hi Jeremiah. That is correct. – RBurns May 27 '16 at 14:14
  • HansUp. The entry will be by one person, but the ability to look it up will be public to the group. – RBurns May 27 '16 at 14:15

0 Answers0