Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Adding digits to a numbering scheme (2000 & XP)

    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.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Adding digits to a numbering scheme (2000 & XP)

    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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding digits to a numbering scheme (2000 & XP)

    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.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Adding digits to a numbering scheme (2000 & XP)

    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:

    <table border=1><td>[b]PID</td><td>[b]LName</td><td>[b]FName</td><td>[b]MI</td><td>[b]ModPID</td><td>[b]Level1</td><td>[b]Level2</td><td>Level3</td><td>8146</td><td>Smith</td><td>James</td><td>E</td><td>08146</td><td>108146</td><td>208146</td><td>308146</td><td>11234</td><td>Jones</td><td>Joseph</td><td>J</td><td>11234</td><td>111234</td><td>211234</td><td>311234</td></table>

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding digits to a numbering scheme (2000 & XP)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding digits to a numbering scheme (2000 & XP)

    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.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Adding digits to a numbering scheme (2000 & XP)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding digits to a numbering scheme (2000 & XP)

    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.

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding digits to a numbering scheme (2000 & XP)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Attached Files Attached Files

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Adding digits to a numbering scheme (2000 & XP)

    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?

    <hr>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.<hr>
    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!

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Adding digits to a numbering scheme (2000 & XP)

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

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Adding digits to a numbering scheme (2000 & XP)

    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?

  13. #13
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding digits to a numbering scheme (2000 & XP)

    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... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> 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 <img src=/S/yadda.gif border=0 alt=yadda width=15 height=15>... Sorry about that!)

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Adding digits to a numbering scheme (2000 & XP)

    Description...of course! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    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!

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding digits to a numbering scheme (2000 & XP)

    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.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •