Make Table using VBA

J

Johnny

Hello everyone,

I have a table called "Holidays" where all the employees holidays are
stored! At this moment 2008 holidays are stored.I wish, when entering
2009 to copy all the records from "holidays" table to a new table
(archive) and clean up the "holidays" table.
So far I'm thinking of make a new table using a make table query with
records from "holidays" table, the problem is, I want this to be done
dynamically without user intervention.The idea is, at the first time
the user open the DB in 2009 the code runs and do all on the fly.
The new table name would be "Holidays" & year(date())-1).
This way when 2010 cames the same will happen and I'll get one
Holidays table for each year.

Any help woukd be appreciated, including other ideas to achiev this

Thanks in advance
Johnny
 
D

Daniel Pineault

Just a question, but why not create 1 table with a field 'Year' that way all
your data is in one place and you don't need to perform updates... every year.
 
J

John W. Vinson

Hello everyone,

I have a table called "Holidays" where all the employees holidays are
stored! At this moment 2008 holidays are stored.I wish, when entering
2009 to copy all the records from "holidays" table to a new table
(archive) and clean up the "holidays" table.
So far I'm thinking of make a new table using a make table query with
records from "holidays" table, the problem is, I want this to be done
dynamically without user intervention.The idea is, at the first time
the user open the DB in 2009 the code runs and do all on the fly.
The new table name would be "Holidays" & year(date())-1).
This way when 2010 cames the same will happen and I'll get one
Holidays table for each year.

A better idea, IMO, would be to use an Append query to put twenty or fifty
years of holidays in the table, with a date/time field (*including* the year).
Non-moving holidays can be inserted using the DateAdd() function. If your
business observes moving holidays (Easter Monday, the mondays after Labor Day
or Memorial Day, Eid ul-Fitr...) you will need to enter these separately or
edit them. You can then easily use a Query

BETWEEN DateSerial(Year(Date()), 1, 1) AND DateSerial(Year(Date()), 12, 31)

to get all the holidays for the current year.

Storing data in tablenames is essentially NEVER a good way to go.
 
J

Johnny

Thank you all for the answers, but I confess that I confused the word
"Holidays" with "Leave".
What I want is to register people "leave days" in different tables one
for each year. The reason is that when a year is finished probably I
don´t need those records anymore, but is better to keep the
information for some time in case of someone need to clarify any doubt
about employees "leave days" on previous years.
So in 2010 I will have on the DB:
2010LeaveTbl(Current Year)
2009LeaveTbl
2008LeaveTbl

2008 and 2009LeaveTbl working as archive no add or edit allowed.

Tks
Johnny
 
J

John Spencer

It is still a bad idea to store the data in multiple tables. When you
do so, you have to redesign.

At a minimum (and I still think this is not a good idea) I would have
two tables. One called CurrentYearLeaveTable and a second one called
PriorYearsLeave.

At least with this set up you would not need to change your queries,
forms, and reports every year.

CurrentYearLeaveTable would have the same structure as you currently have.

PriorYearsLeave would have the same structure as current.

I am assuming the structure is something like
EmployeeID
LeaveDate

Then all you need is a procedure to check
The year of the current date and compare it to the data in the prior
year table. If it does not exist, then execute an append query to add
all the records from the current year table into the prior year table.

Then DELETE all the records in the current year table.

It can be done, but it would make a lot more sense to just keep all the
leave records in one table and filter which ones you display by current
year.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
A

Arvin Meyer [MVP]

You haven't listened to the answers you've received. You DO NOT need to use
multiple tables. It is poor database design which will only result in
problems in the future. You should have 1 table with a year field in that
table. As a matter of fact, if you store the complete date, you do not need
to have a separate Year field.

It is quite easy to keep from editing or even seeing prior year's data. That
will be done with a query which should be the recordsource of a form. With
all databases, data needs to be entered and edited through a form, and with
exception of a few tables which contain fairly static data, those forms
should be based on a query.

You have received similar answers from 4 expert level database developers, 3
of whom Microsoft has recognized as Most Valuable Professionals for many
years. It might be a good idea to try to understand why we are telling you
to use 1 table. You can learn why for free by reading about Normalizing
data:

http://www.accessmvp.com/Strive4Peace/Access_Basics_Crystal_080113_Chapter_03.pdf
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Thank you all for the answers, but I confess that I confused the word
"Holidays" with "Leave".
What I want is to register people "leave days" in different tables one
for each year. The reason is that when a year is finished probably I
don´t need those records anymore, but is better to keep the
information for some time in case of someone need to clarify any doubt
about employees "leave days" on previous years.
So in 2010 I will have on the DB:
2010LeaveTbl(Current Year)
2009LeaveTbl
2008LeaveTbl

2008 and 2009LeaveTbl working as archive no add or edit allowed.

Tks
Johnny
 
K

Ken Sheridan

Johnny:

You might be interested in the following set of tables, which is a
simplified version of those I used in a leave planning database some years
ago. To create the tables just run each of the 'action queries' in the order
given below; try it in a new blank database first, rather than in your
working file:

CREATE TABLE Employees (
EmployeeID COUNTER,
FirstName TEXT(50),
MiddleName TEXT(50),
LastName TEXT(50),
HireDate DATETIME NOT NULL,
QuitDate DATETIME,
CONSTRAINT PrimaryKey PRIMARY KEY (EmployeeID));

CREATE TABLE Leave (
LeaveID COUNTER,
EmployeeID LONG,
StartDate DATETIME,
EndDate DATETIME,
CONSTRAINT PrimaryKey PRIMARY KEY (LeaveID),
CONSTRAINT EmployeesLeave
FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID));

CREATE TABLE Leave_Entitlement (
EmployeeID LONG,
LeaveYear INTEGER,
LeaveDays INTEGER,
CONSTRAINT PrimaryKey PRIMARY KEY (EmployeeID, LeaveYear),
CONSTRAINT EmployeesLeave_Entitlement
FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID));

CREATE TABLE Leave_Entitlement (
EmployeeID LONG,
LeaveYear INTEGER,
LeaveDays INTEGER,
CONSTRAINT PrimaryKey PRIMARY KEY (EmployeeID, LeaveYear),
CONSTRAINT EmployeesLeave_Entitlement
FOREIGN KEY (EmployeeID) REFERENCES Employees (EmployeeID));

CREATE TABLE Calendar (
CalendarDate DATETIME,
PubHol YESNO,
CONSTRAINT PrimaryKey PRIMARY KEY (CalendarDate));

The Calendar table is simply a table of all dates over a given range (e.g.10
years or so) and a Boolean (Yes/No) column to indicate if the day is a public
holiday. An easy way to fill this is to serially fill down a column with
dates in Excel, link to the Excel file in your Access database and append all
the dates to the calendar table.

You could then return a list of all employees' leave for 2008 for instance,
including public holidays, with a query such as:

SELECT DISTINCT FirstName, LastName, CalendarDate
FROM Employees, Leave, Calendar
WHERE (Leave.EmployeeID=Employees.EmployeeID
AND CalendarDate Between StartDate AND EndDate
AND YEAR(CalendarDate)=2008)
OR (PubHol=TRUE AND YEAR(CalendarDate)=2008
AND CalendarDate < NZ(QuitDate,#2999-01-01#)
AND CalendarDate > HireDate)
ORDER BY LastName, FirstName, CalendarDate;

Note how an artificially high date of 1 January 2999 is returned by the Nz
function where an employee's QuitDate is Null, i.e. they are currently
employed, allowing the CalendarDate to be compared with the QuitDate so as
not to return public holidays for an employee after they've quit. The same
isn't done with HireDate as every employee will have a HireDate by virtue of
it being a 'required' field (NOT NULL in the table definition above).

Ken Sheridan
Stafford, England
 
J

Johnny

Hello again,

In first place I wish to thank you all for all the explanations.
I'm quite a newby in access, trying to understand the best way to do
things, so my idea was just a starting point, not my definitive
solution. I read carefully all information posted here and already
started organizing things the way that you all sugested, so I thank
you all.
Ken I will also give a deep look on what you sugest.

Thks
Johnny
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top