Adding digits to a numbering scheme (2000 & XP)

Topic

New Reply

My database tracks employees as they take non-credit courses in my learning lab. Each employee has a unique Payroll ID number. The older employees have a 4-digit PID; the newer ones have a five-digit PID. I want to use that PID as part of a numbering scheme in a database. My plan is to append another digit to the beginning of the PID to indicate which level the student is in (sort of like freshman, sophomore, junior, senior). Here’s the plan:

For the sake of consistency, I want to make all PIDs into 5-digit numbers. Thus, 8146 becomes 08146. Then I want to add one more digit to the front of the PID to designate the training level. Now 108146 means that the employee/student is working on the first level of training; 208146 means the second level and so on. I see creating a table with fields for PID, LastName, FirstName, MI, and then fields for the four training levels. So I’ll see, e.g., 08146 – Smith – Leonard – C – 108146 – 208146…and so on.

What I haven’t figured out is how to do all that. Suggestions, anyone…? TIA.

Reply | Quote

Replies

Apart from the fact it is more difficult to concatenate fields prior to saving them but also when you wish to query a “group” of records it is easier when you use different fields.
I guess what I am advocating is to keep the data inn 2 separate fields.

Reply | Quote

I agree with Pat. The Payroll ID should be just that – a unique identifier. Keep track of the level in a separate field.
If you want to change the four digit PIDs to five digits (I assume this means they are text, not mumeric), make sure that all relationships involving the PID have cascading updates turned on. Otherwise, you’ll break the links between the employees table and related tables. But, as remarked in other recent discussions, why bother? As long as PID is unique, it works.

Reply | Quote

Sorry–I failed to clearly describe the table layout. I do plan to retain the PID as the key field. Here’s how it will look:

PID	LName	FName	MI	ModPID	Level1	Level2	Level3
[/b]8146	Smith	James	E	08146	108146	208146	308146
11234	Jones	Joseph	J	11234	111234	211234	311234

So…PID is the primary key. ModPID is the field I’ll use to build the “Level” fields–ModPID just adds a leading zero to the PID. To create the Level fields, I guess I’ll use…what, a query?

I got the idea for this scheme from another DB I’ve seen and used. That DB uses a similar scheme to relate academic courses to a given semester. Each course has a unique 6-digit number that never changes. Each course also has a 5-digit number that uniquely relates that course to a given semester. Thus, History of Computers has a CourseID of 123456; it has a ClassID of 54321 for Fall 2002, and 65432 for Spring 2003. I just want to do something similar to easily identify a student’s training level. (We’re not working with a semester system in this case. Our students have two years to complete 38 clock hours of training in the learning lab. When they finish the first round, they start a second round of 38 clock hours.)

I hope this clarifies what I’m trying to accomplish. I’m sure there are other ways to achieve what I want–I’m open to any ideas you may offer. Thanks for your help.

Reply | Quote

A function to add a 0 to your ID would be something like

newid: Right(“0” & [PID],5)

If you have your levels in a filed you could just concatonate them on as well. This could be done on the fly when you need the levels rather than storing them.

Hope this helps

Peter

Reply | Quote

Thanks, Peter! I can really use that function, although Hans and Charlotte have got me thinking in a new direction.

Reply | Quote

What do the levels represent and how many of them are there? If the level field is in the same record as the employee PID, there is no reason for anything except a date or a yes/no in the level fields. Repeating the PID in a different format and with a 1, 2 or 3 at the front is redundant and should be done in a query if it’s done at all. Are the levels sequential? That is, do they have to complete Level 1 before they can reach Level 2? If so, you only need a single numeric Level field that holds the number of the highest level for that employee.

Don’t confuse table design with query design. You can display anything you want in a query but don’t try and design the tables to display the same information just because you want ot see it that way in a query or report.

Reply | Quote

At present, we have four levels, but we’ll eventually need to add another, I’m sure. I kept it to 3 in my example for the sake of simplicity. The levels refer to what we call “rounds of training,” and our students go through them in sequence. We have about 800 employees (students) in our system, and they may choose from about 240 “courses.” These courses range from 0.5 to 4 hours in length, and almost all courses are taken via computer or videotape. We have a very small number of courses that we deliver in a classroom. Students start at the first level (we call it “Green”) and must complete 38 hours. Then they start the “Yellow” round (“level 2”), and must complete another 38 hours without repeating any courses they took in their first round. In my example, I represent the levels with numbers (Access likes numbers, right?) but we really refer to the rounds or levels as Green, Yellow, Lavender and White.

I’m trying to build a new system to replace the exiting one. We need a way to separate and display the courses a given student has taken in a given round. In our existing system, when a student finishes Green and starts Yellow, we add the letter G to the end of the student’s last name and to the end of the student ID number (not the same as the PID). The current (Yellow) record displays the name and number normally. When we look for a student’s record on the form, we know that SmithG John 12345G is the old Green round for John Smith; Smith John 12345 is the current (or Yellow) record. It just seems to me there oughta be a better way to do this, y’know?

I understand what you say about redundancy, but I do need a way to display separately what a student has taken at each level. Perhaps my question is about how best to design the DB as opposed to how to build the table and its fields.

Reply | Quote

The best way to do it depends on whether you need history records.

You have a many-to-many relationship between students and courses: one student must follow many courses and one course can be taken by many students. The way to implement a many-to-many relationship is by creating an intermediary table. This table will contain the courses taken by a student. Fields will be the student identifier and course identifier; there must be a unique key on the combination of these. Additionally, there can be fields for the date of the course, the level it counts for, examination results, etc. (I am under the impression that courses are notbound to a particular level, I may be wrong here).

To keep track of the level, you could include a single level field in the student table, as suggested by Charlotte. This field will contain the current level for the student. If you want to see courses taken in the current level, add the students and intermediary (and courses) tables to the query, and join them on student identifier and level. If you want to see all courses taken up to now, leave out the join on level, so that you join only on student identifier.

If you need to store student-and-level specific information (not student-and-course specific), you can create another intermediary table containing student identifier, level and the student-and-level specific information. You can find the current level for a student by taking the highest level that is stored for that student.

Reply | Quote

I do need history records. Let’s say my student Hans is working in Level 3 (Lavender). It’s quite possible that I would want to look back to see what Hans did in Yellow (Level 2) or even in Green (Level 1). I’m still trying to absorb your information, but it appears that I do not need to maintain two or more records on the same student (a record for each level). That’s exactly what I’m trying to avoid–separate, redundant records.

You are mostly correct in assuming that “courses are not bound to a particular level.” There are exceptions, particularly at the Green level. There are some courses that we exclude from the list for students in Green (we reserve them for the higher levels), and there is one course that every Green student must take.

I’ve had a little experience creating intermediary tables to create many-to-many relationships. All such tables I’ve created have included only the primary keys from the two related tables. In this case, I might use, say, StudentID from tblStudents and CourseID from tblCourses. However, I gather from your post that I could include in this intermediary table fields such as the date of completion and the level to which we’ve applied the course. (We don’t worry about grading.) You mean to add those fields to the intermediary table and not to one of the other tables, right?

[indent]

---

If you want to see courses taken in the current level, add the students and intermediary (and courses) tables to the query, and join them on student identifier and level.

---

[/indent]
Could you please elaborate on that point. Let’s say I’m in the query design view and I add tblStudents, tblCourses, and tblStudentCourses (the intermediary table) to the query grid. Those tables should automatically appear with join lines. I’m unclear what you mean to “join them on student identifier and level.”

Thanks again, Hans!

Reply | Quote

Lucas,

I don’t know if this will help you or not, but I played around and quickly made a small example of what can be done with a bit of normalization in your database design… I created some general tables and created relationships between the data (as I saw it on what little information I have) and did a couple of queries to show how it could be used…

Take a peek… Hopefully it’ll give you some ideas, if nothing else…

P.S. If the PID is a unique number… and (I’m assuming) the Student ID is a unique number… I don’t understand why you’d need both… but I included the PID in the Employees table in case it has some other function for you…

HTH

Reply | Quote

Trudi,

Wow! This is terrific! Your example clarified the ideas some other folks offered, and I think your sample DB may be just the thing I’m looking for!

Yes, the PID (payroll ID) and the StudentID are both unique. I need both, believe it or not. The database would work fine with just one or the other. However, we have to deal with at least two separate systems. Our students’ employer assigns the PID, but we need the StudentID to transfer the non-credit hours into the state’s community college academic record-keeping system. So all my students have three identifying numbers, if you include the Social Security Number. If there be strength in numbers….

A quick question: What does the Desc in “MaxOfLevelDesc” mean?

Reply | Quote

Lucas,

That’s short for Description… I made the Levels table have an ID and Description… (in case you ever wanted to see Green, Yellow, Lavender or White on some form or report… ) …You can use the ID throughout and get at the description (color) whenever needed…

I was trying to show you that you could do a query that would show the Level each employee was currently on…. I used the Max function to select the highest level currently in the records for each employee… If that makes any sense… You can pull out pretty well whatever data is needed using relationships… Saves redundant data and helps ensure data integrity…

For example, when you said that you would probably have to add a 5th level at some point, I thought if you had a Levels table, you will simply have to add a record to that table and it can then be used throughout the database in the future…. Saves the time of looking through queries and reports for calculated fields like “=IIF(LevelID = 1, “Green,… ” etc… don’t you think?

(Geez I’m yappy today … Sorry about that!)

Reply | Quote

Description…of course!

I had noticed the Max function in the query design, and I think I understand exactly what you’ve done. One more question, if you don’t mind:

You may have seen my comment in my reply to Hans earlier in this thread that we exclude some courses from the Green students (level 1). Thus, I need to devise some way to keep some courses from appearing on the list we offer the Green students. Would you suggest adding an intermediary table between tblLevels and tblCourses to set up a many-to-many relationship? Or would you recommend some other approach?

Thanks again…very much!

Reply | Quote

Hello Lucas,

You could set up another table to act as intermediary between levels and courses (the “official” relational way), but you can also set up queries for the different levels with ad-hoc criteria impended.

In Trudi’s very nice design, there is a fixed relation between levels and courses. Instead, you need a relationship between Levels and CurrentCourses. I have altered the table structures accordingly. See attached zipped Access 97 database; look at the tables and queries and at the Relationships window.

Reply | Quote

Thank you, Hans! I’ve used this example with some slight modifications, and I’ll probably have some follow-up questions later. So far, I think this is going to work.

Reply | Quote

Hello again, Hans!

I’ve hit a little snag. I’m trying out the new scheme by just adding a record directly into the table (tblStudentCourses–the junction table between tblStudents and tblCourses). Everything goes smoothly till I try to enter a second record. When I hit Tab or Enter, I get an error message: “Cannot add or change a record because a related record is required in ‘tblCourses.'” The data I entered for the course came straight from tblCourses, so…I’m confused! Help!

Reply | Quote

Lucas,

Are you sure that you entered a valid Course ID? … Did you type it in or select it from the combo box – lookup list I added? … I tried it in Hans’ db and the first db I made… It worked fine for me in both cases… I notice that the table names havechanged to “tblStudentCourses”, “tblStudents”… is it possible that the relationships got messed up with you were altering the db?

Reply | Quote

I’m modifying this post to say that I’ve made the CourseID field into a combobox (just as you and Hans did), and now the silly thing works! Oh well…. But I still have a question for you, Trudi, if you don’t mind.

Truth is, Trudi, I did not copy your tables (nor Hans’s) into my DB and populate them there. In my existing DB, I already had a tblStudents and a tblCourses. I modified them to emulate the examples you and Hans provided. Then I built the join table and set up the relationships. Another truth is that I did not even notice the combo box feature you’d added–that’s an obvious enhancement!

Your sample differed in some respects from Hans’s, as you know. One difference I noticed is that in your tblCurrentCourses, the first field is an AutoNumber field and shares the Primary Key role with StudID. I chose to follow Hans’s approach: My Primary Key fields in tblStudentCourses are PID and CourseID. I’m curious as to the purpose for that AutoNumber field.

Thanks for your help.

Reply | Quote

Hmmm…. Now you’ve got me thinking…

Truth is , I put that database together on the fly and didn’t think it through completely… I probably created the table in Datasheet view first, threw some data in as I saw it would look, and when I went to go to Design View it probably said I had to save it first and then prompted me for a primary key….

However, generally I find it useful to have Separate Primary keys in most of my tables…. (even with foreign keys and/or composite keys)

One thought that comes to mind is… what if (for whatever reason) you wanted to see the last 20 records that were added to the table?… Maybe it’d never happen in your case, but this field would come in handy if that were needed… Sometimes I just add an autonumber field for sorting purposes (My clients sometimes have definite layout requirements for government reporting or whatever…) … Sometimes I just use the field during design and testing activities to quickly check what results I’m getting from queries, in which case I’d remove that field/key when I’m done with it…

Hans and you were right to change the relationships around a bit… I knew my example wouldn’t be exactly what you needed… I just thought it would help you get started…

Let me know if I can be of any further assistance okay?

Reply | Quote

Trudi, thanks a million! I also added an AutoNumber field to my tblCourses. You’d think that with every course already having three numbers to identify them I wouldn’t need yet another one. However, out of 236 courses in my inventory, I have exactly one that causes me headaches in the numbering department. With this course, if a manager type takes it, we require them to complete the entire course and they get 2.5 hours credit. If a worker-bee takes it, we don’t require them to complete parts of it, and they get 2 hours credit. To keep the two “versions” separate, we long ago assigned the number 80 to the worker-bee version and 80TL (team leader) to the manager version. So now I can’t treat that field as a number–I have to use it as text.

Anyway, I’ll probably be back with more questions. Thanks again!

Reply | Quote

Be very careful using “Desc” in a name, since DESC is used in SQL to designate a descending sort. You would be better off using Descrip or something similar to avoid confusion. I would have interpreted that aggregate name as indicating a descending sort on the underlying field.

Reply | Quote

You’re right Charlotte… Sorry about that…

Reply | Quote