- Joined
- Apr 4, 2008
- Messages
- 1
- Reaction score
- 0
Hi
I've just created my very first Access database. I've overdosed on dummy books and idiot guides and everything was going fine and dandy. However, now I think I've hit a brick wall.
I managed to create a parameter query to ascertain the training hours of employees between any two dates entered by the user. The SQL view looks as follows:
SELECT tblEmployees.Firstname, tblEmployees.Lastname, tblNPLQTrainingSessions.DateOfSession, tblNPLQTrainingSessions.UnitNumber, tblNPLQTrainingSessions.NumberOfHoursWet, tblNPLQTrainingSessions.NumberOfHoursDry
FROM tblEmployees INNER JOIN tblNPLQTrainingSessions ON tblEmployees.EmployeeID = tblNPLQTrainingSessions.EmployeeID
WHERE (((tblNPLQTrainingSessions.DateOfSession)>=[Enter Start Date] And (tblNPLQTrainingSessions.DateOfSession)<=[Enter End Date]) AND ((tblEmployees.Location)=[Enter Blackshots, Belhus Pk or Corringham]) AND ((tblEmployees.Active)=True));
At first I was jumping up and down as I managed to get this far. Then I was awoken in the middle of the night by the reality that if an employee had done a training session between the two specified dates then he/she would show up on my report. However if an employee had done no training at all between the two dates, then they didn't show up. This I desparately need.
I then did a query to get all the employees who need to train that hadn't so far. That looks as follows:
SELECT tblEmployees.Firstname, tblEmployees.Lastname, tblEmployees.Location
FROM tblEmployees LEFT JOIN tblNPLQTrainingSessions ON tblEmployees.EmployeeID = tblNPLQTrainingSessions.EmployeeID
WHERE (((tblEmployees.Location)="blackshots" Or (tblEmployees.Location)="corringham" Or (tblEmployees.Location)="belhus pk") AND ((tblEmployees.[Job Title])="leisure manager" Or (tblEmployees.[Job Title])="duty manager" Or (tblEmployees.[Job Title])="leisure attendant") AND ((tblNPLQTrainingSessions.EmployeeID) Is Null));
Then I realised as soon as one of these people did train it would give them a record on the training table, and again wouldn't show up if they hadn't happened to train between the specified dates in the previous query.
All I'm trying to get to is where a user can see whether or not employees have trained and if so, how many hours.
Please, please help
Jane
I've just created my very first Access database. I've overdosed on dummy books and idiot guides and everything was going fine and dandy. However, now I think I've hit a brick wall.
I managed to create a parameter query to ascertain the training hours of employees between any two dates entered by the user. The SQL view looks as follows:
SELECT tblEmployees.Firstname, tblEmployees.Lastname, tblNPLQTrainingSessions.DateOfSession, tblNPLQTrainingSessions.UnitNumber, tblNPLQTrainingSessions.NumberOfHoursWet, tblNPLQTrainingSessions.NumberOfHoursDry
FROM tblEmployees INNER JOIN tblNPLQTrainingSessions ON tblEmployees.EmployeeID = tblNPLQTrainingSessions.EmployeeID
WHERE (((tblNPLQTrainingSessions.DateOfSession)>=[Enter Start Date] And (tblNPLQTrainingSessions.DateOfSession)<=[Enter End Date]) AND ((tblEmployees.Location)=[Enter Blackshots, Belhus Pk or Corringham]) AND ((tblEmployees.Active)=True));
At first I was jumping up and down as I managed to get this far. Then I was awoken in the middle of the night by the reality that if an employee had done a training session between the two specified dates then he/she would show up on my report. However if an employee had done no training at all between the two dates, then they didn't show up. This I desparately need.
I then did a query to get all the employees who need to train that hadn't so far. That looks as follows:
SELECT tblEmployees.Firstname, tblEmployees.Lastname, tblEmployees.Location
FROM tblEmployees LEFT JOIN tblNPLQTrainingSessions ON tblEmployees.EmployeeID = tblNPLQTrainingSessions.EmployeeID
WHERE (((tblEmployees.Location)="blackshots" Or (tblEmployees.Location)="corringham" Or (tblEmployees.Location)="belhus pk") AND ((tblEmployees.[Job Title])="leisure manager" Or (tblEmployees.[Job Title])="duty manager" Or (tblEmployees.[Job Title])="leisure attendant") AND ((tblNPLQTrainingSessions.EmployeeID) Is Null));
Then I realised as soon as one of these people did train it would give them a record on the training table, and again wouldn't show up if they hadn't happened to train between the specified dates in the previous query.
All I'm trying to get to is where a user can see whether or not employees have trained and if so, how many hours.
Please, please help
Jane