Help

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hi

I am new at access and would appreciate any help given.
I have a database with 2 tables. One table tblstaff has
usual staff records including holiday allocation and
holidays taken. The other table is tblholidays which is
linked to the first table by a tech id. I want to enter
the dates and input manually a new holiday period
and "add" it to the first table to be able to produce
a "table" with the holiday allocation remaining and a
list of all detail to a particular tech. Any help or an
example would be much appreciated. Thanks in advance for
help.
 
Your description is not clear enough for us to understand: "holiday
allocation remaining"? "holiday period"? fields in the two tables? etc.
 
From the start it sounds as if you do not have the tables properly
constructed (normalized). You talk of a table [tblstaff] with 'usual staff
records' and 'holiday allocation and days taken' . It sounds as if you have
mixed Person, things that go with that individual e.g. firstname, lastname,
dateOfbirth, address
with things that have to do with :
a. holidays e.g. date, holiday hours (days) earned
and
b. holidaysTaken e.g. date, holidayCode, hours(days) taken

A person earns holidays in steps over time (gives a one to many
relationship)
A person takes many holidays (gives a one to many relationship)

So I see at least three tables

tblStaff (stuff about the individual) tblStaffID as keyfield
tblHolidayEarned (stuff about holiday time earned or lost) tblStaffId as
foreign key
tblHolidayUsed (stuff about holidays taken) tblStaffId as foreign key

(although the earned/used tables can be combined if you use "+" values for
holidays earned and "-" values for holidays used. This would work something
like a check register adding holiday deposits and debiting holiday
withdrawals).

maybe there is also another table

tblStaffEmployment (stuff about the individual's employment within the
organization) e.g. date, hired|promoted, salary etc. tblStaffID as foreign
key

Now you could query to answer the following questions.

give me the total timeunits earned by a given date and the total timeunits
used by a given date and show me the difference between the two.

Take a look a table normalization.

Ed Warren
 
Hi

Sorry I will try better this time. I have a table called
tblstaff which has asociate name, associate number, email
address, annual holiday entitlement, days bought, days
sold, days carried over. The data for these will be
manually inputed.

I have another table called tblholidays which has
associate name, holiday start date, holiday end date and
the number of days.

The other table is tblholidaysleft which will have the
associate name, number of days left. As I type this I
sort of think this table is not needed......I want to be
ba able to enter the holidays taken to hit a button and
see what is remaining and the dates that an associate has
taken. I think its an update or append query but not
sure what to do. Hope this explaines this better. Thanks
for your reply.

Cheers

Joe
 
Hi Ed

Thanks for the reply I have posted another thread which
might be clearer.

Cheers

-----Original Message-----
From the start it sounds as if you do not have the tables properly
constructed (normalized). You talk of a table [tblstaff] with 'usual staff
records' and 'holiday allocation and days taken' . It sounds as if you have
mixed Person, things that go with that individual e.g. firstname, lastname,
dateOfbirth, address
with things that have to do with :
a. holidays e.g. date, holiday hours (days) earned
and
b. holidaysTaken e.g. date, holidayCode, hours(days) taken

A person earns holidays in steps over time (gives a one to many
relationship)
A person takes many holidays (gives a one to many relationship)

So I see at least three tables

tblStaff (stuff about the individual) tblStaffID as keyfield
tblHolidayEarned (stuff about holiday time earned or lost) tblStaffId as
foreign key
tblHolidayUsed (stuff about holidays taken) tblStaffId as foreign key

(although the earned/used tables can be combined if you use "+" values for
holidays earned and "-" values for holidays used. This would work something
like a check register adding holiday deposits and debiting holiday
withdrawals).

maybe there is also another table

tblStaffEmployment (stuff about the individual's employment within the
organization) e.g. date, hired|promoted, salary etc. tblStaffID as foreign
key

Now you could query to answer the following questions.

give me the total timeunits earned by a given date and the total timeunits
used by a given date and show me the difference between the two.

Take a look a table normalization.

Ed Warren


Joe said:
Hi

I am new at access and would appreciate any help given.
I have a database with 2 tables. One table tblstaff has
usual staff records including holiday allocation and
holidays taken. The other table is tblholidays which is
linked to the first table by a tech id. I want to enter
the dates and input manually a new holiday period
and "add" it to the first table to be able to produce
a "table" with the holiday allocation remaining and a
list of all detail to a particular tech. Any help or an
example would be much appreciated. Thanks in advance for
help.
Click to expand...


.
Click to expand...
 
My initial reaction is that your tables could be better structured to make
your life a lot easier when it comes to querying the data.

From the sounds of your description of tblstaff table, the fields related to
holiday information will be updated probably once per year; meaning that
you're overwriting a previous year's information with new info, right? And
if this is correct, then the tblholidays information is only good for that
same "year" of the values in tblstaff?

I'd be inclined to look at a table structure similar to this:

--Table to store basic data about each staff person--
tblStaff
StaffID
StaffName
(etc.)


--Table to store the different types of holidays that can be
associated to a staff member (such as entitled holiday, bought
holiday, sold holiday, carried over holiday)--
tblHolidayTypes
HolidayTypeID
HolidayName


--Table to the "starting" number of days for each staff
member for each of the possible holiday types--
tblStaffHolidayMain
StaffID
CalendarYear
HolidayTypeID
HolidayDays


--Table to store the use of holidays by each staff member--
tblStaffHolidayUsed
StaffID
CalendarYear
HolidayDays

In the above structures, what you do is store basic, fundamental data values
in the tables. No summing, calculating, etc. is necessary when you enter the
data. Then, use your queries to add up and subtract the days based on what
you want to know. If you want to know how many holiday days total a staff
member has "accumulated" for a calendar year, just sum the days in
tblStaffHolidayMain for that staff member and that calendar year. To find
out how many days the staff member has used in a calendar year, just sum the
days in tblStaffHolidayUsed for that staff member and that calendar year.
And so on.

Try this approach and see if that makes your setup and use much easier. Post
back with questions about queries, data entry, etc.
 
Back
Top