Solved

Append to Access table

Posted on 2011-03-05
6
363 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
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.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Some sers suddenly getting error popup msg 28 86
sql server concatenate fields 10 31
Query Help - MSSQL - Averages 5 25
SQL multicriteria from ONE textbox 32 41
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

786 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