Create a new database with relationships

[ukfastcar]

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

[CJ_London]

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

[Micron]

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

[ukfastcar]

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

[Welshgasman]

You will need a junction table between staff and courses.
That would be the source for your form to allocate staff to courses.

[Bob Fitz]

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.

[davegri]

Heeding advice from others, see if this gets you on the right track:

ukf-davegri-v01.zip

[mike60smart]

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.

[Welshgasman]

Fields can have captions, but I rarely see anyone using them.

[Micron]

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.

[CJ_London]

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

[Bob Fitz]

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

[madpiet]

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.

[Micron]

@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."

[ukfastcar]

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.