• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

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
0
Stephen Byrom
Asked:
Stephen Byrom
  • 3
  • 2
1 Solution
 
peter57rCommented:
Create a standard select query which select pkempid for all employees with a null end date.
Add new columns
 HolsTaken:0
 DaysInLieu: 0
 DateFrom:Dateserial(Year(Date()), 04,01)
 DateTo:Dateserial(year(date())+1, 03,31)
 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.
 
0
 
JAMcDoCommented:
Try this.  It is much the same as described by peter57r but is done for you.



  Test4Append.accdb

J.
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
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,1) AS DateFrom, DateSerial(Year(Date())+1,3,31) AS DateTo, 9 AS HolsAdded, Date() AS DOE
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
peter57rCommented:
WHy are you including tbldates in the query?
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
INSERT INTO tblDates ( HolsTaken, DaysInLieu, DateFrom, DateTo, HolsAdded, DOE )
SELECT 0 AS HolsTaken, 0 AS DaysInLieu, DateSerial(Year(Date()),4,1) AS DateFrom, DateSerial(Year(Date())+1,3,31) AS DateTo, 9 AS HolsAdded, Date() AS DOE
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.
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
IINSERT INTO tblDates ( HolsTaken, DaysInLieu, DateFrom, DateTo, HolsAdded, DOE, fkEmpID )
SELECT 0 AS HolsTaken, 0 AS DaysInLieu, DateSerial(Year(Date()),4,1) AS DateFrom, DateSerial(Year(Date())+1,3,31) AS DateTo, 9 AS HolsAdded, Date() AS DOE, tblEmployees.pkEmpID
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now