Updating (1 Viewer)

Hi

Firstly, some background. I am building a database, part of which will be used to log when employees set off stability alarms when driving machinery. The first time an employee sets an alarm off, they are put on to stage 1. The next time stage 2 and finally stage 3, which becomes a disciplinary matter. Each stage lasts for 3 months, then they go back to zero. I have created a form to select an employee and enter that they have set off an alarm.

My question is, can Access work out what stage the employee is on when a new entry is made? The employee details are in one table, and the details of when they set an alarm off are in a separate table linked by employee number. So I would need access to look at what stage they were last on, if any, work out if it was within the last 3 months and then enter the new stage into the stability table.

I hope that all makes sense. Any help would be greatly appreciated.

Here is a quote from the_Doc_Man

In essence, the old programmer's rules apply here.

1. Access won't tell you anything you didn't tell it first yourself (or at least tell it HOW to tell you).

2. You aren't ready to do it in Access until you do it on paper.


So, do some analysis; try your logic on paper with some test cases and adjust until it works.
Then design the database.

Good luck.

Jdraw is of course correct, but a couple of pointers to things you might want to look at would be 1) manipulation and comparison of date fields, and 2) the operation of the DMAX domain aggregate function. That's if I have understood your question properly. Presumably the relationship between the two tables is a one-to-many ?

Do you date field when the alarm was set, then post #3 will cover it provided he is the same person that set the last alarm.

Hello

Thanks for the replies so far and apologies for delayed response, its been a hectic week.

jdraw - the information for Access to work out the next stage is definitely all there, the problem i'm having is not knowing how to tell access how to calculate it, or more specifically, how to calculate it and then add it when making a new entry.

To give a bit more detail, the relevant fields in the employee details table are Title, Surname, First Name and Name Identifier, all of which pull through when employee number is selected. The employee number is what links the table to the stability table which has the following fields, stability ID, Equipment ID, Alarm Count, Shift Date, Action Taken, Stability Completed By, Date Completed and Stability Stage. It is a one to many relationship with each employee able to set off many alarms, but each alarm only being set off by one employee.

The form I have created to input a new entry requires the person inputting to select the employee from a drop down, this completes the rest of the employee details. They then enter the stability ID, Equipment ID, Alarm count, Shift Date and action taken. Action taken can either be follow up or no further action. Only when follow up is selected will an employee move up a stage in the process.

The fields stability completed and date completed will be input later once the employee has been spoken to be a trainer.

I can write an aggregate query which will work out what stage an employee is on after the initial input, i.e before speaking to a trainer. However, what I don't understand is how I can enter this against the new entry.

I'm hoping with a bit more detail someone can point me in the right direction. I will look at DMAX and see if I can work out how that may help, but i'm fairly new to access and can't work it out in my head at the moment.