Solved

Append to Access table

Posted on 2011-03-05
6
364 Views
Last Modified: 2012-05-11
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
Comment
Question by:Stephen Byrom
  • 3
  • 2
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35043955
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
 
LVL 3

Expert Comment

by:JAMcDo
ID: 35044064
Try this.  It is much the same as described by peter57r but is done for you.



  Test4Append.accdb

J.
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 35044292
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 35044325
WHy are you including tbldates in the query?
0
 
LVL 1

Author Comment

by:Stephen Byrom
ID: 35044374
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
 
LVL 1

Author Closing Comment

by:Stephen Byrom
ID: 35044391
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question