?
Solved

Append to Access table

Posted on 2011-03-05
6
Medium Priority
?
380 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Accepted Solution

by:
peter57r earned 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

650 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