Autonum, but not? :S

  • Thread starter Thread starter NateBuckley
  • Start date Start date
N

NateBuckley

Hello,

I have two tables - One is "Employees" and the second is "Holidays". Each
Single Employee within Table Employees can have many holidays, the problem is
that I have a field inside of Holidays that is called HolNum. This field will
hold a unique number for that Employee, so let's say:

Employee1 books two holidays -
Holiday1 HolNum = 1
Holiday2 HolNum = 2

now a second Employee books three holidays
Holiday3 HolNum should = 1 and Holiday4 HolNum should = 2. Holiday5 Holnum
should = 3.

Basically the number will be unique but only for that Employee, I'm just
wondering if it's possible to do this automatically? Kinda like a controlled
Autonum?

I'm using Excel as a front end to access the database, so I could simply do
a few checks via Excel that update a new record that way, but I'd like it, if
it did it automatically.

Any suggestions would be greatly appreciated.

Thanks!

Nate
 
Hello and thanks for your reply.

Holidays is where the booked holidays go, After reading a little into
database design it appears I may need some type of third table but I'm quite
perplexed and thus a teeny bit confused.

Basically I want it so when a user books a holiday, there holiday is logged,
and a sheet is printed out with hoilday details (that part I have done), but
on this sheet I have a number "HolNum: 0001" when they book there second
holiday the number at the top of the printed page will be "HolNum: 0002".
However every employees holnum starts at 0001. So will have duplicates. I was
basically looking for an easy way of setting up the database so when a new
holiday is booked it makes sure the Holiday has a unique number, but the
HolNum depends on how many previous holidays the employee has.

I think I may have confused myself by that explanation. :S Hope that helps
somewhat?

Thanks anyways though.
 
Nate

There seems to be something missing...

If you have Employees, and you have Holidays, don't you also have
BookedHolidays? This would require a third table to show the many-to-many
relationship between Employees and Holidays.

Or maybe you are use a different definition that I am...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
You could add a field in the Employee table called "NextAvailableHolidayNum"
(or something like that. Each employee starts with a "1" in the field, and
when a holiday is booked, that number gets incremented. For the Holiday
table, you'd use a concatenated key of employee number + holiday number to
uniquely address each record.
 
You can do this (if you are using a form to input your data) by using DMax.
Use the After Update Event of a previous field in the record (perhaps the
field where you enter the Employee?) and use code something like this (I'm
assuming that HolNum is a number field, rather than a text field - you can
use the field's Format to show the leading 0's and that Holiday contains the
foreign key field EmpID which links to the Employee table's Primary key)

Me.HolNum = NZ(DMax("[HolNum]","Holiday", "[EmpID]=" & Me.[EmpID]),0) + 1

ie it looks for the highest HolNum for the current Employee, adds 1 to it
and puts it into the HolNum field

Evi
 
Perhaps I'm misinterpreting...

In my experience, an Employee does get to "own" a holiday, excluding all
other Employees. Thus, John Doe may "book" Christmas and Jane Smith may
also book Christmas. This is a many-to-many relationship between Employees
and Holidays, and requires a third (junction/resolver/relation) table that
holds valid pairs of Employee-Holiday.

As I read your description, I get a sense of a "how" more than a "what" or
"why". What will having the type of numbering you described allow you to
do? That's more an issue of an underlying business need than a description
of the implementation in Access.

And if you want to "count" the number of holidays booked by each Employee,
use a query. You don't have to "number" them.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top