Getting started (Access 97)

Topic

New Reply

Hello All

I am new to this group, so please be gentle with me.

I have been pondering the design of a database for nearly 12 months and keep hitting brick walls. I would be very grateful for some discussion to get my creative juices going!

I will try to explain. In my workplace, all staff are required to do some training in particular areas. The things they need to do are dependant on their position. To add another complication, there is a frequency factor. Some things are done once only, some annually, some every two years etc.

For example

Driver Training is done once by all staff when they are first employed
First Aid is done by only Supervisors annually
Communicable Diseases training is done by all staff every three years
Vaccination (optional) for all staff once only.
and so on

It is not like a class where they all go to the same sessions, but I do want to be able to see who has completed First Aid – if they have to.

The object of the database is a recording and reminder system. I want the user to be able to be reminded when something hasnt been done according to the above criteria.

Any help and discussion would be most appreciated.

Regards Kerry

Reply | Quote

Replies

Start thinking of it like this. Required Frequency is an attribute of the training course, so you need a tblCourse table something like this:

CourseID (autonumber)
Course (Text – name of course)
Descrip (Text – description or comments on course – optional)
Frequency (Numeric – i.e., 1 = once, 2 = Annual, 3 = 2 Years, 4 = 3 Years, etc.)
Level (Numeric – Staffing level: 0 = All Staff, 1 = Supervisor)

You would of course have an employee table, preferably with an autonumber key (call it EmployeeID) rather than a PK on the employee name or SSN. It would include things like hire date, termination date, etc. It would also include title and a numeric Level field to allow for variations of title that would still group into Supervisor or all staff.

Then you would have a join table, perhaps called trelEmployeeTraining that looked something like this:

EmployeeID (Numeric – Long Integer – FK to tblEmployee)
CourseID (Numeric – Long Integer – FK to tblCourse
DateTaken (Date/Time – Date employee completed course)

The three fields together would form a multi-field unique key. Each individual field would not be unique since each of them might be repeated many times but not in the same combination.

From those basics, you could expand it several different ways, depending on what you were most comfortable with.

The basic structure would allow you to query something like this to see who had taken courses:

SELECT E.EmployeeID, E.LastName, E.FirstName, E.Title, E.Level, C.Course, C.Frequency, T.DateTaken
FROM tblCourse AS C RIGHT JOIN (tblEmployee AS E LEFT JOIN trelEmployeeTraining AS T ON E.EmployeeID = T.EmployeeID) ON C.CourseID = T.CourseID
WHERE (T.DateTaken Is Not Null);

Does that help?

Reply | Quote

Thankyou Charlotte for your reply. To assist with understanding your response can you tell me what “FK”, “PK” and “SSN” stand for?

I have actually set up tables pretty much as you suggested. I am finding that I can enter the information in correctly, however the retrieving of information in a report showing a summary of what is NOT done is causing me a headache.

I am starting to wonder if I would be better to use Excel and have a worksheet for each course and one for staff then link it altogher in on summary Worksheet.

I can send you what I have done and the view of the desired report if you wish.

Regards Kerry

Reply | Quote

Sorry for the jargon. FK is foreign key, that is the primary key from the related table, PK is primary key, and SSN is social security number, something that only applies in the USA, although many countries have an equivalent government-issued identifier.

In the long run, you would find the Excel worksheets harder to maintain because you can’t keep them updated as easily as you can an Access database.

It’s a bit difficult to summarize things that haven’t happened. What you often do is get a list of all the things that have happened as expected and then use that to produce a list of everything that isn’t in the first list.

Can you post the SQL of the query you’re trying to use and explain exactly what it is you want to see in a report?

Reply | Quote

Thanks Charlotte – FK – PK – so obvious now.. I feel like a real dope! SSN … I can be excused for as it isnt such a big deal in Australia.

I have attached a Word Table showing what I envisioned the end result would look like. This is the best way of trying to explain. You may have better ideas. If this is not possible then it isnt worth pursuing any further with a database.

Your feedback is appreciated.

Kerry

text

Reply | Quote

whoops – hopefully this has the attachement – Told you I was new!

Reply | Quote

You would do this with a crosstab query to return the people and titles as rowheadings and the course as a column heading with the Max of the course date as the value. You would need to preset the column headings property to the particular courses to make sure you get all the courses in the query even if no one has taken that course. Then base your report on the crosstab query.

Is all that color absolutely necessary? I personally find it hard on the eyes. Why not just get a report on the information you need instead of trying to include everything and using color to tell you what to look at?

Reply | Quote

Thankyou Charlotte – you are a marvel! You really have me on my way now. I have managed to get a pretty good looking report with your crosstab idea.

One question though … can you eloborate a little more on this statement?

You would need to preset the column headings property to the particular courses to make sure you get all the courses in the query even if no one has taken that course.

No quite sure what you mean by this.

Also I take your point on the colors. It is a bit Yuk, but I was a desparate woman!

Thankyou.

Kerry

Reply | Quote

Well, the nature of crosstabs is that if there is no data for a heading (i.e., there’s a course that no one has yet taken or no one has taken within the timeframe you specified), then that heading will not appear. That makes it hard to produce the same report reliably. However, if you specify column headings by setting the column heading property of the query to a comma-delimited list of course names, then you will always gets those columns even if no one has taken the course. Does that answer your question?

Reply | Quote

Yes that does explain it and I have done it on my report. I hadn’t ever looked at this area in a crosstab before, so you have taught me something I will surely never forget.

I am on my way now and my database is really taking shape thanks to your assistance. I may come back here at some time in the future if I come across any new hurdles.

Keep well

Regards Kerry

Reply | Quote