Autonum, but not? :S

[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

 

[NateBuckley]

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.

 

[Jeff Boyce]

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/

 

[Dennis]

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.

 

[Evi]

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

 

[Jeff Boyce]

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

 

[Jeff Boyce]

Correction ... "an Employee does NOT get to "own" a holiday, ..."

Jeff