Solved

Append to Access table

Posted on 2011-03-05
6
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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