Results 1 to 13 of 13
  1. #1
    suzeq is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    26

    Employee Traininf Database


    Hi,

    I have a task to came out training database for our company employee but encountered an issue whereas i cant create 1 relationship that consist multiple Primary key. I don't want to used auto number as PK since we already have unique ID for every employee. Basically my objective was, we can query by ProductDetails, Product and by employee ID.

    Attach is my sample database .

    Hopefully someone can help me on this and appreciate it.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,515
    I dont see why Employee must be in the relationship. You DO need Product--->ProdDetails as a parent-child structure.
    But Emloyee can just be a lookup table. You dont HAVE to have it in the relation.

  3. #3
    suzeq is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    26
    Basically we have a lot more employee date to be include like reporting manager,nationality, shift code, contract agent etc..that was why i need employee table.
    Now i cant create relationship between EmpProductDetails to BEProduct Details as error 'indeterminate' appear.
    any idea?Click image for larger version. 

Name:	Indeterminate.jpg 
Views:	270 
Size:	179.8 KB 
ID:	23897

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Tell us about your business in simple terms. Give us an overview of "typical day at work".
    What does the company do? What are the Products? etc. etc.

  5. #5
    suzeq is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    26
    Hi Orange,

    I have to do Employee Training database for our company. Basically we have many products (60+) with 30+ process for each product(most of the process are different).
    For now i was started with 1 product which was BE (Table: BEProduct) and Table BEProductDetails was for all process under BE product.

    Table EmpProduct - combination employee ID + Product name
    Table EmpProductDetails - Combination employee ID + process related
    Table BEProduct - Product name+ product description
    Table BEProductDetails - Product name+proceeds related

    Briefly about many company :
    -We have many different product such as Belize, Apx, Malta etc
    -Most of the products have different front to back process such as Chassis assembly, knob assembly, KVL test, CIT test, Welding etc
    -We have 1000+ employee

    My objectives were, we can know employee training status through query by employeeID, by product name and by process name.
    i am new about this ms access and had try to reorder unique index setting and even try tto came out with table for each product and way to much table and my relation was crazy..

    appreciate everyone help.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I took a peek at your dB. With the current structure and field types, I think (just my opinion) you are going to have many headaches/problems. Every field is a text field. The EmployeeRecord table is not normalized. I would have two fields for the emp name: FirstName and LastName (or whatever you want to name the fields).
    What does the EmpShift have to do with the attributes about the employee? You have EmpStartDate and EmpEndDate..... Are these the dates the emp was hired and terminated?

    I would suggest you first design your table/structure on paper/whiteboard/cardboard.
    Maybe you could take the time to work through these tutorials: http://www.rogersaccesslibrary.com/forum/forum46.html

    Years ago I asked a friend that worked as a programmer at a bank to look at my dB because I was having lots of problems. A glance at my table structure and I was told: "This is a very bad design". Not a bad design.... a very bad design. That is when I learned about designing on paper first. (Actually, I had committed "spreadsheet" - mostly because I was converting from a spreadsheet".)


    -----------------------
    I am not sure you understand what autonumbers are for. EVERY table I have has an autonumber field as the PK field. And I have an (unique) EmpID and SSN field (unfortunately). I do not have any multi-field primary keys.

    You might want to read these two links:
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers






    I hope I didn't offend you..... again, these are just my opinions.

  7. #7
    suzeq is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    26
    Hi Ssanfu,

    TQ for the sneak peak

    I took a peek at your dB. With the current structure and field types, I think (just my opinion) you are going to have many headaches/problems. Every field is a text field.
    'I have change EmployeeID field to Number type'.

    The EmployeeRecord table is not normalized. I would have two fields for the emp name: FirstName and LastName (or whatever you want to name the fields).
    'I prefer to have 1 Full name column only'

    What does the EmpShift have to do with the attributes about the employee?
    'Basically my company has 7 different shift pattern (4A,4B,4C, etc) and that was one of the employee record. we are going to query by shift afterward as well.
    every employee would has allocated to 1 shift pattern only. '


    You have EmpStartDate and EmpEndDate..... Are these the dates the emp was hired and terminated?
    'You are correct, those date for hiring date and end contract date'

    That is when I learned about designing on paper first. (Actually, I had committed "spreadsheet" - mostly because I was converting from a spreadsheet".)
    'Actually there were a lot of draft that i had made before start to mapping out to ms access as i am new to this ms access' but seem like now my turn to get that title 'worst design ever' .

    I am not sure you understand what autonumbers are for. EVERY table I have has an autonumber field as the PK field. And I have an (unique) EmpID and SSN field (unfortunately). I do not have any multi-field primary keys.
    'Since every employee has their own employee ID so i was decide to using it as primary key. For your info, my company has 1000+ over employee and i think much easy to used their ID.
    Basically my current db was in excel format so using employee ID could help me to track any missing employee data'. Actually i had try to used unique setting as well but it still didn't work.

    ps - how to attach my latest db in this thread?

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    I agree with Steve. Work through a couple of the tutorials at RogersAccessLibrary to get a better understanding of the database design process. I'd suggest you then create a description -in plain English - of WHAT is involved in your business. Start at the 30,000 ft level and get the major "things", then start adding detail.
    The tutorials will give you a blueprint of your intended database, and you can validate it using test data and some scenarios.

    You wouldn't build a house without a blueprint, and you shouldn't build a database --especially one that deals with your company's business - without a blueprint.

    Here is another link to a design overview.

    Good luck with your project.
    Last edited by orange; 03-01-2016 at 02:14 PM. Reason: spelling

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    ps - how to attach my latest db in this thread?
    Below the Quick Reply area, click the Advanced button.

    Scroll down to the "ATTACHMENTS" area. Click the button.

    Should do a "Compact and Repair" first, then Zip it.

  10. #10
    suzeq is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    26
    I had modify my table and relationship based on input, idea given and tutorial. Here my latest db and seem like its work for 1 department product(BE: BackEnd) out of 3 department.

    After i added new table (FEProduct: FrontEnd) under EmpProduct it prompt error again.
    I had to have 3 main table for Product category since there are different process for each category.
    BackEnd : BE Product
    FrontEnd : FE Product
    Battery : Battery Product

    So for now, attachment was for 1 department only and hopefully everyone could input for it.
    Hopefully everyone still hasn't feel tired to help me again.
    Attached Files Attached Files

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    I'm not sure of the meaning of your FrontEnd and BackEnd, but these have a special meaning in multi-user Access databases. see this info
    Please explain your FrontEnd Product vs BackEnd Product.
    As Steve and I have been suggesting, build a data model; get some test data and test your model against some scenarios.
    See this stump the model approach

    Good luck

  12. #12
    suzeq is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    26
    Hi,

    I was planning to divide our company products into 3 main group (BE,FE and Battery) based on process since different department has different process.

    FE (FrontEnd)- SMT(surface mount tech) area and example of process such as GSM, siplace etc
    BE (BackEnd) - Assembly area and example of process such as knob process, chassis process etc
    Battery - Battery product area and example of process such as welding, loading etc

    Attach is my latest db.

    1) Form EmployeeDetail – EmployeeDetailsubform does not function properly.

    2) Form QueryByID – when i entered employee ID, it still didn't reflect into form

    3) Form QueryByIDSubform – I cant scroll the to view the record

    4) FormQueryByProcess – Still working on it as my combo box still does not function


    rgds,
    Attached Files Attached Files

  13. #13
    suzeq is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2016
    Posts
    26
    oppss sorry as i missout the file . here my latest attachment
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Employee Training Database
    By tajerome in forum Access
    Replies: 5
    Last Post: 08-03-2014, 11:11 AM
  2. Trouble with Employee database
    By swhennen85 in forum Access
    Replies: 11
    Last Post: 07-19-2013, 04:32 PM
  3. Employee Database
    By di00721 in forum Access
    Replies: 1
    Last Post: 07-30-2012, 02:09 PM
  4. Database Design for employee
    By Ramya in forum Database Design
    Replies: 1
    Last Post: 07-29-2011, 11:57 AM
  5. Employee Attendance database
    By oxicottin in forum Database Design
    Replies: 0
    Last Post: 02-14-2007, 02:58 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums