Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2004
    Posts
    13

    Unanswered: Had to start new DB...Please Help!!!

    I am having a very difficult time with this database...First off I attached it as a .txt file just change it to .mdb to view. I only have the tables started because that's as far as I can get before I lose what Iam tring to do.

    Which is select one of five departments(Material Flow, Finishing, Extrusion...), after selecting the department I want to enter employees for that particular department.

    After employee data is entered I need to select the certification that the employee earned in that department(i used a subform in my orginal db...was very nice but I couldn't cary over the certification value for some reason ). I need the cert value to add up for each employee so that when it is greater than 6 it bumps the Service Specialist Level up 1, and so on.

    Sorry for the long post, but this is probably the 6th time I had to redo this...I am almost positive I could do this without creating a table for each department, employees in a specific department, and certifications for each department ...LOL...thats exactly how I did my orginal, what a waste of time. I am at the Point Where I am WillIng To PAY FOR HELP!!!

    Thanky you,
    A very frustrated Eric
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Posts
    13
    Anyone??? All I really need to now is how to link the tables...

  3. #3
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Eric,
    Hopefully this will get you started. This is how I would tackle it. I would suggest that you not store the Service Specialist Level in the table, since you can calculate it whenever you like (at least it sounds like it is calculated).

    Check out the relationships and the 2 queries, I hope that gets you on the right track.

    Chris
    Attached Files Attached Files

  4. #4
    Join Date
    Dec 2004
    Posts
    13
    YOU ARE A LIFE SAVER!!! That is what I wanted to do, but, yes the big but , After I select the department I want only the certain certifications for that department to be selected for the employee in that department. I don't want an employee that is in the extrusion department to be able to have a certification that is for the finishing department... I am going to mess around with what you gave me... I already found that there was an issue that when i selected the department it generated another field for the same department. Anyways, I really appreciate your time for helping me!!!

    What really sucks is that I have to have this finished(at least one of the departments) by tommorrow, Friday, at 2:30. Thats when I have to give a presentation for my G.M. and a bunch of plant supervisors . I have to give them a walk through. This will be the third time that they see a different DB for this project.

  5. #5
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Glad to help. Not sure how much I'll be around tonight/tomorrow, but there's a lot of helpful people here and usually much smarter than I .

    Just a couple thoughts...
    1. Not sure about the duplicate fields except that you now have primary and corresponding foriegn keys to tie the tables (may appear like the same field, but seperate tables)

    2. There are a fews ways to handle the Dept/Certification limits. If you're only dealing with a few values for each field, you could use some IIF's in the criteria of the query for a form or combo box. Or you may want to track the relationship in another table each record has keyDeptID and keyCertID. This would track valid cert types for each Dept. Someone else may have a better idea for you, but those come to mind.

    3. You could go tie one on tonight and play hookie tomorrow. I'm sure your GM would understand

    Good luck,

    Chris

  6. #6
    Join Date
    Dec 2004
    Posts
    13
    I think that the hardest concept for me to understand is how to relate the tables...You wouldn't believe it, but I actually have a degree in Information Systems . I have a total of 114 certifcations, over 350 employees, that are spread out in the 5 departments. I also have to make something up that when a employee transfers departments that I can store it some where for possible future needs....You know what I think I might just head to the bar now , Iam starting to get a shooting pain in my head again.

  7. #7
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    I might join you.
    Anyway, sorry...hard to gauge people's knowledge/experience level on here.

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by gixer311
    I think that the hardest concept for me to understand is how to relate the tables...You wouldn't believe it, but I actually have a degree in Information Systems . I have a total of 114 certifcations, over 350 employees, that are spread out in the 5 departments. I also have to make something up that when a employee transfers departments that I can store it some where for possible future needs....You know what I think I might just head to the bar now , Iam starting to get a shooting pain in my head again.
    Hi Gixer,
    Nice to have degrees and all. Having one in IS may not serve you as well as one in Databases in general. If you pick up any book on RDBMS you will find just after the gathering of ALL the info. you will need for your database, you will start to see how things will/should tie together. That is the basis or ground floor of database design.
    You have three basic Relationships...
    (1) One-To-One ---One field relates to only one field in Main or second table.
    (a) One employee can only have ONE desk.

    (2) One-To-Many--One field relates to Many in another Table
    (b) One employee can visit Many Buildings.

    (3) Many-To-Many--This is basically two One-To-Many relationships.
    (c) One employee can visit Many Buildings...and One Building can be visited by Many Employees.

    Each Table generally has a PrimaryKey field...(a field that makes EACH Table Unique).
    For a one to many Relationship, once your PrimaryKeys are set, you take the PrimaryKey of One Table and place it in another Table. It then becomes what is called a ForeignKey. That Table is now the Many side of the Relationship. So, whatever Table has a PrimaryKey set for it Itself and from Another Table, you have a One-To-Many Relationship established. Also set your Referential Integrity as well. One good selection for a PrimaryKey is an AutoNumber field. That is basically all it is used for, as a Unique Identifier. But keep in mind, it is automatically Created, and Incremented, BUT, once it is Deleted, it is gone. So set that as your PrimaryKey but still use another field to Identify your field.

    TableName = tblEmployee, with PrimaryKey named, EmployeeID, set to Autonumber, set as IndexedYES/No Duplicates.

    TableName = tblLocations, with PrimaryKey named, LocationsID, set to Autonumber, set as Indexed/No Duplicates, also include EmployeeID as ForeignKey, set to Indexed YES/Duplicates OK.

    Now you have a One-To-Many Relationship established.

    Hope that helps you to get around to understanding what you need to do. But honestly though....get a book to learn the Basics about databases, because that is the First and Crucial step in designing one and should be understood before creating any table on the computer. Database Design for Mere Mortals by M. Hernandez is a good book.

    hope you get your situation taken care of,
    have a nice one,
    BUD

  9. #9
    Join Date
    Dec 2004
    Posts
    13
    I can't tell you all how much I appreciate this website!!! I messed around with this new DB until I had to go to the meeting to present it. Let me tell as of right now I feel like I could sell water to a drowning person ...Everyone in the meeting thought my proposal of this DB was great. BUT, Yes again with the but, it is far from finished(great thing about tech. applications is that you can tell people anything and they don't know if your pulling their leg or not). I might need more help on how to select only certain certifications(in subform) when the certain department is selected. And once again I think my problem is with the relationships. By they way that last post made me see more clearer on relationships(soon will find out for sure).

    Thanks,
    Eric

    P.S.- Chalk another up for a very satisfied customer...

Posting Permissions

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