1

I have to develop this database for my work and one part of it is that I have 4 different types of members that need to available to the system: Students, Parents, Mentors, and Coaches. Each have different information associated, so they all have their own table. Another table is a "notes" table that I want to be able to attach and unlimited number of notes to each member.

So for each table, there is a formatted autonumber. For students this number should be S#### in an incremental order. It doesn't matter the number and there will be far fewer than 9,999 students so I'm confident that's all I'll need. Then there's also P#### for parents, and so on.

It needs to be this way because the database also houses survey questions and responses. My notion is that survey responses can be uniquely identified by the member_ID, year, and term taken (since they're only open certain times). The problem is that without the formatted autonumber being found in the query, then ID's will be repeated and non-unique.

So my question is, does format autonumber not work? Am I going to have to use some VBA to build my own autonumbered string that will carry over through queries and other table lookups?

EDIT: So HansUp (below) suggested using a master list that the member tables feed off of. I've set this up since I haven't thought of an alternative. Basically, the flow is this:

  1. Database user clicks "New Student"
  2. "new student" form opens, along with "new member".
  3. "new member" form creates a new ID # and assigns S group, sets viability off
  4. Concatenated ID is then passed to "New student" form
  5. If OK, then all changes are comitted
  6. If cancel, then DoCMD.Undo for both new student and new member.

Would this do it? I'm not exactly sure how to pass that undo statement to the "New member" form though...

Djones4822
  • 577
  • 3
  • 6
  • 23
  • @hansup I've thought about that but it adds a layer of complexity I was trying to avoid since it would mean that the ID's aren't unique within the database which makes me nervous. My goal was to make it so that every single member has a unique ID, this could be solved if I just had a way to link autonumbers and make them continuous between tables... – Djones4822 Jan 26 '15 at 18:11
  • @HansUp so you're saying have another table with the autonumbering and the member type that the other tables use as a lookup? I suppose that would work except I'm not sure how I'd implement that as a form. Wouldn't that mean I'd need to have the database user add a new member&type, and then assign that member & type the necessary information? – Djones4822 Jan 26 '15 at 18:33
  • @HansUp thats ok! It definitely wasn't inappropriate and is how I'm moving forward now until I get a better idea. It creates a complexity in the VBA where a new user if canceled needs to have that new ID deleted, but other than that it is simple as just passing values between forms. I think I can do that by just making a few extra steps that aren't entirely impossible. If I don't have any better solution then your idea will be the one! – Djones4822 Jan 26 '15 at 19:04
  • Can a member have more than one role, for example both Parent and Coach? If so, and you're still intent on storing the role letter plus the autonumber in one field, would a parent/coach have something like P00123 and C00124 in the respective tables, or do you want them as P00123 and C00123 instead? – HansUp Jan 26 '15 at 20:45
  • No, there is no way for a member to belong to multiple groups (and if they did they would be entered as separate individuals with unique ID's in order to retain integrity). – Djones4822 Jan 26 '15 at 21:58
  • In which table is student John Doe's name stored? – HansUp Jan 26 '15 at 22:04
  • In "student Table." I'm thinking I can reinforce the naming schedule in the form. I'm thinking I can still using my alpha numeric by creating a string for student John Doe as "S0002", in my VBA I'll loop through the ID field, truncating off the letter using `Right(cell,4)` and converting it to an integer. Once I've found the max, I add one, convert back to a string, concatenate with the group identifier. This is a horrible process but I'm not sure of an alternative. – Djones4822 Jan 26 '15 at 22:12
  • Yes, it is a challenging design and I don't believe it supports your goal of avoiding complexity. With member's names stored in the individual role tables, it will be more difficult to answer simple questions such as "Is Jane Doe a member of our organization, and if so what is her role?" – HansUp Jan 26 '15 at 22:18

1 Answers1

0

Leave the auto-number field as it is. it won't help you even if it does to your current business requirement it will create problem once your application grows further.

Use GUID/UUID to overcome database-level (or even global level) unique ID issues. This way you will maintain an unique ID throughout your database which will help in your case to have only one "notes" table for all of your entities.

in short:

  • Add a GUID field in your tables
  • Use triggers or public function/macro to generate the GUID
  • try to merge students, parents, mentor & coach into one table by separating them by "Type"
  • Remember an auto-number field is not continuous (in case if some records get deleted the gab will be there)
  • you can always perform string concatenation/format to produce S12355 number in your query to present it to your end-users.

I guess your main goal is to use only one note table for all of your users. GUID would be the way to go. here some starting point: How can I generate GUIDs in Excel?

Community
  • 1
  • 1
Krish
  • 5,917
  • 2
  • 14
  • 35