Append to Access table
Hi There,
I have a small database consiting of two tables,tblEmployees and tblDates.
tblEmployees consists of the following fields;
pkEmpId Primary Key (autonumber)
FirstName text
LastName text
merglogon text
StartDate date
EndDate date
tblDates fields are;
pkDatesID Primary Key (autonumber)
fkEmpID number
HolsTaken number
DaysInLieu number
DateFrom date
DateTo date
HolsAdded number
DOE date (current date)
It is a simple database to view and update Employee holidays.
Every year (April 1st), each employee is given 9 days holidays for the next 12 months. This is in addition to the company days and public days.
tblDates has records for holidays taken and accrued, some employees may have many records and others may have only a few records, whereas new employees may not have any records at all yet.
I have a button on a form, which if certain criteria is met, should run an append query.
I need to append new records into tblDates for each employee in tblEmployees where EndDate is null (still employed).
The fields in tblDates need to be filled-in with the following data;
HolsTaken = 0, DaysInLieu = 0, DateFrom = April 1st (currentYear), DateTo = March 31st (NextYear), HolsAdded = 9, DOE = (current date)
I hope this isn't too much to ask.
Thanks in advance
I have a small database consiting of two tables,tblEmployees and tblDates.
tblEmployees consists of the following fields;
pkEmpId Primary Key (autonumber)
FirstName text
LastName text
merglogon text
StartDate date
EndDate date
tblDates fields are;
pkDatesID Primary Key (autonumber)
fkEmpID number
HolsTaken number
DaysInLieu number
DateFrom date
DateTo date
HolsAdded number
DOE date (current date)
It is a simple database to view and update Employee holidays.
Every year (April 1st), each employee is given 9 days holidays for the next 12 months. This is in addition to the company days and public days.
tblDates has records for holidays taken and accrued, some employees may have many records and others may have only a few records, whereas new employees may not have any records at all yet.
I have a button on a form, which if certain criteria is met, should run an append query.
I need to append new records into tblDates for each employee in tblEmployees where EndDate is null (still employed).
The fields in tblDates need to be filled-in with the following data;
HolsTaken = 0, DaysInLieu = 0, DateFrom = April 1st (currentYear), DateTo = March 31st (NextYear), HolsAdded = 9, DOE = (current date)
I hope this isn't too much to ask.
Thanks in advance
ASKER
Thanks for the responses.
I converted the suggested solutions to SQL view;
INSERT INTO tblDates ( HolsTaken, DaysInLieu, DateFrom, DateTo, HolsAdded, DOE )
SELECT 0 AS HolsTaken, 0 AS DaysInLieu, DateSerial(Year(Date()),4,
FROM tblEmployees LEFT JOIN tblDates ON tblEmployees.pkEmpID = tblDates.fkEmpID
WHERE (((tblEmployees.EndDate) Is Null));
However, If employee 1 has 5 records in tblDates and employee 2 has 3 records in tblDates and employee 3 has no records in tblDates, the append query adds 9 new records instead of 3.
The fkEmpID for each of the new records in tblDate are also blank, and should be equal to the employee ID.
If I have 50 employees I need to add 50 new records to the tblDates (one for each employee).
tblDates may have a few hundred records for each employee after a couple of years, but I still only need to add 50 records each year, not however many reords there are in tblDates.
Hope that clarifies my problem
I converted the suggested solutions to SQL view;
INSERT INTO tblDates ( HolsTaken, DaysInLieu, DateFrom, DateTo, HolsAdded, DOE )
SELECT 0 AS HolsTaken, 0 AS DaysInLieu, DateSerial(Year(Date()),4,
FROM tblEmployees LEFT JOIN tblDates ON tblEmployees.pkEmpID = tblDates.fkEmpID
WHERE (((tblEmployees.EndDate) Is Null));
However, If employee 1 has 5 records in tblDates and employee 2 has 3 records in tblDates and employee 3 has no records in tblDates, the append query adds 9 new records instead of 3.
The fkEmpID for each of the new records in tblDate are also blank, and should be equal to the employee ID.
If I have 50 employees I need to add 50 new records to the tblDates (one for each employee).
tblDates may have a few hundred records for each employee after a couple of years, but I still only need to add 50 records each year, not however many reords there are in tblDates.
Hope that clarifies my problem
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
INSERT INTO tblDates ( HolsTaken, DaysInLieu, DateFrom, DateTo, HolsAdded, DOE )
SELECT 0 AS HolsTaken, 0 AS DaysInLieu, DateSerial(Year(Date()),4,
FROM tblEmployees
WHERE (((tblEmployees.EndDate) Is Null));
My mistake, Sorry
It now adds the same number of records as there are employees. However, it still doesn't include the pkEmpID in the fkEmpID field.
SELECT 0 AS HolsTaken, 0 AS DaysInLieu, DateSerial(Year(Date()),4,
FROM tblEmployees
WHERE (((tblEmployees.EndDate) Is Null));
My mistake, Sorry
It now adds the same number of records as there are employees. However, it still doesn't include the pkEmpID in the fkEmpID field.
ASKER
IINSERT INTO tblDates ( HolsTaken, DaysInLieu, DateFrom, DateTo, HolsAdded, DOE, fkEmpID )
SELECT 0 AS HolsTaken, 0 AS DaysInLieu, DateSerial(Year(Date()),4,
FROM tblEmployees
WHERE (((tblEmployees.EndDate) Is Null));
Because you were so clear in how to make an append query, I have learnt yet something else on here. THANK YOU.
I have added the employee ID
SELECT 0 AS HolsTaken, 0 AS DaysInLieu, DateSerial(Year(Date()),4,
FROM tblEmployees
WHERE (((tblEmployees.EndDate) Is Null));
Because you were so clear in how to make an append query, I have learnt yet something else on here. THANK YOU.
I have added the employee ID
Add new columns
HolsTaken:0
DaysInLieu: 0
DateFrom:Dateserial(Year(D
DateTo:Dateserial(year(dat
HolsAdded : 9
DOE:Date()
Now convert the query to an append query and choose the tblDates table.
Make sure each of the fields are matched correctly (the end date field should not be matched with anything).
That's it.
Save the query, backup your data and test the query.