1

I am maintaining an Access Database for use with student admissions. I have a primary table which houses biographical information, and a secondary table which has application information, and allows for multiple applications per student (with each student having a unique student ID; that ID is stored in both tables and is how the applications are matched to the student).

Each application is assigned an "Application Number," and each student can only have one application with a specified number (i.e., student A cannot have two applications numbered "1", but can have 1, 2, and 3).

I would like to create a validation rule of some kind to prevent duplicates, but the whole column is not unique... it's only as it relates to the specified student.

Is there a way to create such a rule, or should I be arranging my data differently? I am open to making changes if it means a more efficient workflow.

I hope this makes sense... I wasn't sure how best to describe this. Thank you for any help.

1 Answers1

0

If you are expecting the user doing the data entry to come up with a valid unique "application number", then the rule you are looking for would be a unique index on both StudentId and ApplicationNumber. (Remember, you can create an index which includes multiple columns.) This would mean that every pair of StudentId and ApplicationNumber must be unique.

However, I should note that requiring the user doing the data entry to have to come up with a unique application number by themselves is very user-unfriendly.

Consider the following alternatives:

  1. Have the database suggest a unique application number. Or, better yet,

  2. Do not even suggest any number while the application is being filled-in, but instead issue a unique application number at the moment that the application is submitted. Or, even better yet,

  3. Stop storing application numbers in the database, and instead have the database calculate them, only when there is a need to display them, based on user id and date of data entry of the application. (Caveat: if a student has 3 applications, and application #2 gets deleted, then the old application #3 will be renumbered to #2, thus causing confusion. So, this will only work if deletion is disallowed.)

Mike Nakis
  • 56,297
  • 11
  • 110
  • 142
  • Thank you so much for your thoughtful reply. I like #3, though I have a concern. I use the app # with a secondary query in order to pull _only_ the latest app in other forms, i.e. where app # matches that of the query. The query is: `SELECT [Student ID], MAX([Application Number]) AS [Latest Application Number] FROM [Student Applications] GROUP BY [Student ID];` If the app # no longer exists, how can I pull _only_ the latest app? I thought to use dates, but my concern was it is possible (however unlikely) that a student may apply twice in a single day. Thank you for any help. –  Jan 07 '16 at 22:31
  • Oh, simple, instead of using a date, use a date-time field. I don't remember the specifics of ms-access, but there are 2 cases: either it supports different column types for date and date-time, or the "date" type is actually a date-time type. – Mike Nakis Jan 08 '16 at 04:12
  • It does indeed seem date & time are stored in a single column type, so long as both date & time are input (i.e., if no time is provided for a date it defaults to midnight). I may use this method, even with the possibility of duplicate apps (we could, after all, just not accept more than one app per day). Regardless, one last question to you: in the case of #1 or #2, how would I suggest or issue a unique app number? Could I use an If statement in a macro, something like: _If [Last App #] is null or blank, set value of [App #] to "1", else set value to [Last App #]+1_? How would one do that? –  Jan 11 '16 at 21:39
  • I figured out your second suggestion! I indexed the two columns as you said, based on the answer here: http://stackoverflow.com/questions/2127698/can-we-create-multicolumn-unique-indexes-on-ms-access-databases Then, in my form, I made the Default Value for `[Application Number]` the following: `=Nz(DMax("[Application Number]","[Student Applications]","[TempVars]![ID of Student in Active Input Form]=[Student ID]"))+1` (the TempVar is made based on the ID in the calling form). It works! A unique sequential app # is automatically generated. I hope that all made sense... and THANK YOU again! –  Jan 11 '16 at 22:34
  • Sorry, I did not log in during the last day or more, so I had not seen your previous comment. I am glad it worked for you! C-:= – Mike Nakis Jan 12 '16 at 16:03
  • 1
    It should be noted that your solution is not a *proper* solution in the sense that it does not provide atomicity, which means that if two users try to have an application number issued simultaneously on the same database, they may receive the same application number. The way this problem is normally solved is with identity columns, which in ms-access are called "autonumber" if I remember correctly. But if there is no concurrency (and therefore no need for atomicity) in your scenario, then your approach should be OK. – Mike Nakis Jan 12 '16 at 16:04
  • I hadn't thought about that... to be fair, an app should be entered only by one user at a time, so it should be okay, but still. I do have an autonumber field, of course, but as it is assigned to every single application, a student's first app could be #15, and the second and third could be #108 and #798. That's not helpful... but if there is some way to extrapolate from that, say, for the system to recognize that #15 is really #1 for that student, just as #108 and #798 are really #2 and #3, then that would certainly be preferable. I really do apologize... I'm such a novice with all of this! –  Jan 12 '16 at 19:48