Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    ukfastcar is offline Novice
    Windows 11 Office 365
    Join Date
    Sep 2024
    Posts
    8

    Create a new database with relationships

    Hi All,



    I would like to create a database with the following.

    List of individuals (Firstname and surname + three other fields)

    Job Roles (C1, C, C+E)

    Date training was given for the following ( Oil Change, Wipers Change, Bulb Change)

    When training expires (1 year after date of training received)

    I have been using excel for this but feel that access would be the better option but I have not used access before.

    Thanks for the help in advance

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    Access is nothing like Excel. The first thing you need to understand is database normalisation. Your description indicates you have some idea of normalisation but you are missing some tables - review one to many and many to many relationships. You also need to include in your tables a PK (primary key) and where appropriate FK's (foreign keys) which are used to link tables together (i.e. relationships between tables)

    Become familiar with the relationships window. Once a table is created it can be dragged onto the relationships window. One there you can right click on it to edit it - e,g. correct a field name, add or delete fields, etc You can draw your relationships by click on a field in one table, keep the mouse down and move to a field in another table and release.

    Other tips:

    Don't use spaces or non alpha numeric characters in table and field name (except the _ if you really must)
    Use meaningful names (name? name of what?) use empName, roleName etc
    Avoid reserved words there ar a fair numbe rof them, for example Name, Desc, Description, Date

  3. #3
    Micron is offline Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    It's a long learning curve if you want to be good at it - but worth the effort. I suggest you start here
    https://www.accessforums.net/showthr...773#post521773
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ukfastcar is offline Novice
    Windows 11 Office 365
    Join Date
    Sep 2024
    Posts
    8
    I have started what I think is the correct way please see attached.

    So I have a table for the staff with all the details I require, A table for the training courses there are with the number of spaces and a form with a very simple attendance sheet.

    What I would like to do next is place staff on courses, lets say a MOD 2 course (put 3 people on the course for example) and have that information show on the attendance sheet when I select the type of course and the date that matches.

    ThanksDatabase3.zip

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,258
    You will need a junction table between staff and courses.
    That would be the source for your form to allocate staff to courses.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2021
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    As a self-confessed novice I understand that you have a great deal of new ideas and concepts to understand but in post#2 CJ_London did give the following advice:
    Don't use spaces or non alpha numeric characters in table and field name (except the _ if you really must)
    I would agree with his advice and also recommend that you keep the names given to objects, as short as you can.
    Naming a field "
    Number that can attend" is likely to cause you problems.
    You also have an abundance of spaces in your other table.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2021
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,700
    Heeding advice from others, see if this gets you on the right track:

    ukf-davegri-v01.zip

    Click image for larger version. 

Name:	rels.png 
Views:	37 
Size:	11.5 KB 
ID:	52390

    Click image for larger version. 

Name:	Course.png 
Views:	36 
Size:	27.4 KB 
ID:	52391

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,751
    Hi
    In your Staff table you have a field named "TachoNumber"

    I would assume that this belongs to a specific Vehicle?

    To maintain a History of which member of staff drove which vehicle in a specific time period, you need tables to maintain this process.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,258
    Fields can have captions, but I rarely see anyone using them.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Micron is offline Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    CJ_London did give the following advice:
    Don't use spaces or non alpha numeric characters in table and field name (except the _ if you really must)
    So did I, by providing links on everything one should know before starting. Based on your comments I see no reason for me to attempt to help any further.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,845
    but I rarely see anyone using them
    I used to but gave up as (particularly with complex queries) matching what you see in the datasheet and what you see in the sql/QBE can be confusing. And 99.9% of the time I use queries for forms/reports so easier to just change the caption of a label. Not quite up there with lookups, but not far behind

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2021
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Quote Originally Posted by Micron View Post
    So did I, by providing links on everything one should know before starting. Based on your comments I see no reason for me to attempt to help any further.
    Hmm.... I presume you're familiar with the saying about leading a horse to water
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    madpiet is offline Expert
    Windows 10 Office 365
    Join Date
    Feb 2023
    Posts
    551
    If training courses are events they have to attend... is it only a single day every time? Normally it's something like

    CourseTopic--(1,M)--CourseTrainingEvent(CourseTrainingEventID, CourseTopicID,TrainingDate)--(1,M)--Attendance(CourseTrainingEventID, EmployeeID)--(M,1)--Employee

    Apologies for the really nice snake, but that should give you a starting point. It's a variant of the Students and Classes database, so you may want to look that up too.

  14. #14
    Micron is offline Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    @Bob Fitz - fer sure. If I'm riding the horse or I own it then I have a vested interest in not letting it die of thirst, but people are not horses.
    If they ignore the advice or teaching I've given then I quickly lose interest. I guess it's based on a long history of expressing opinions at work, only to be ignored and then watching the predicted result come to fruition. At least I never said "I told you so."
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    ukfastcar is offline Novice
    Windows 11 Office 365
    Join Date
    Sep 2024
    Posts
    8
    Thanks for the advice all.

    I have removed the spaces and will no longer use spaces

    @davegri thanks for the attachment it was very helpful.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 23
    Last Post: 08-01-2017, 06:14 AM
  2. Replies: 7
    Last Post: 08-05-2016, 11:53 AM
  3. Replies: 2
    Last Post: 07-27-2016, 10:25 AM
  4. Explicit Relationships and Implicit Relationships
    By Dazza666 in forum Database Design
    Replies: 2
    Last Post: 07-17-2013, 02:11 AM
  5. Replies: 3
    Last Post: 04-20-2012, 05:53 PM

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