Capturing an employee in a team at a point in time

[raphozs]

Hi, I would really appreciate some help on the following query:

I would like to design a db that captures employee data. At the moment
in the Employees table I have an entry for a team. However, it only
captures the "Current" team the employee belongs to. If the employee
changes teams then changing it in the current table will change it for
all previous entries and associated date.

The question is therefore, how would I go about to create a table (or
tables) whereby I can associate an employee a to a specific team at a
given point in time. For example if Employee1 was in team A for the
first half of the year and team B for the 2nd half of the year, then a
query associated with 01 Feb will return that the employee was in team
A but a query on 10 Oct will indicate the employee belonged to Team B.

Any help will be greatly appreciated. I am not sure what the apporach
I should be taking?

Thannks in advance.

 

[Allen Browne]

You have a classic many-to-many relation between teams and employees. You
therefore need 3 tables, like this:

Team table (one record for each team), with fields:
TeamID primary key
TeamName Text name of this team
TeamStart Date when this team began
TeamEnd Date when this team disbanded.

Employee table (one record for each employee), with fields:
EmployeeID primary key
Surname Text
Firstname Text
...

TeamEmployee table
TeamID which team the employee joined
EmployeeID who joined this team
JoinDate Date when the employee joined the team.
DepartDate Date when the employee left the team.

 

[Guest]

At the risk of upsetting Allen may I add my twopennyworth? Allen's design is
faultless and his post is, as ever, lucid. Follow his advice and you won't go
wrong; you will have a robust design that will not need altering.

What I want to add - because this caused me some grief in a past life - is
the use of these tables, particularly the one with start and end dates. You
may want to answer the questions of the sort: 'Who was employed on this team
on such and such a date or was this employee a team member on such and such a
date?' This is a common and classic requirement; it doesn't just relate to
teams and employees but to items out on loan, states in a cumulative status
table, etc.

From your post I gather you are just starting this project and most of what
I want to say will be relevant later, so I suggest you keep this post until
you need it. However the one thing to do right now is to define the default
value of your dates to be null. Think of null as meaning 'unknown.' Generally
when you create a record you know the start date but rarely do you know the
end date, so a record gets created with a known start date but a null end
date.

Now nulls are a proverbial pain in the arse if you haven't built them into
your design strategy because you cannot do any test comparison against them
and sometimes a null value crops up in an unexpected place (wait for the
inevitable, 'Invalid use of null'). Fortunately Access/VBA provides a natty
little function named NZ that works even inside SQL (Queries). What NZ does
is test if the specified value is null and if it is it substitutes a value of
your choosing.

OK, what's the point of all this? Well, if a deployment is current then the
end date will be null and any test against the end date will fail. So use the
NZ function to substitute a real date - but which date? I recommend you use
the parameter date and make the comparison '>='. Perhaps an example will help.

WHERE NZ([DateEnd],[MyDate]) >= [MyDate]

[MyDate] is the parameter date. The above comparison returns true if
[DateEnd] is GE than [MyDate] or if [MyDate] is null. If you use the Access
Query Grid/Builder to design your queries then you can enter
NZ([DateEnd],[MyDate]) in the Field cell (top row) and >= [MyDate] in the
Criteria cell. A WORD OF WARNING: For this to work you must define [MyDate]
as a date parameter. To do so, go to Query/Parameters from the main menu and
enter [MyDate] as your parameter and select Date/Time as its type.

Sorry there's more! It concerns international date formats and Access's
misguided attempt to be overly helpful. Allen has some great write-ups on
this and you could do a lot worse than log onto his site and read through
them all. It all centres on whether you write short dates as m/d/y, d/m/y,
y/m/d or whatever. Access Query Grid interrogates the Windows setting and as
a first attempt tries to interpret whatever you enter according to that
format. It would be much better if it stopped there, but no, if the
interpretation fails it tries to derive a valid date by applying any/all of
the other formats. Now the catch is that Access/Query Design Grid/Query
Parameters does as I have just described but in-line (VBA) SQL strings assume
you have entered a date in US m/d/y format. Er!, so do all the Access design
properties. If you enter [DateStart] >= #1/2/07# in the Filter property of a
form then Access interprets this as 2nd January, not 1st February (or even
not as 7th February, 2001).

What to do? I guess it's a matter of where you are, what settings are made
in Windows, whether these are likely to change, whether you want to port your
project to another environment that may not have the same Windows settings.
Me? I avoid the issue by setting up ADODB commands with declared date
parameters. When I can't do this I follow Allen's advice and construct a SQL
string (not a date you notice) that has the general format '#m/d/y#' and
build my SQL around this.

Enough for now. When you get to interrogating your data and writing queries
make another post. I or someone will guide you through this particular
minefield.

Regards,

Rod

 

[Tim Ferguson]

WHERE NZ([DateEnd],[MyDate]) >= [MyDate]

WHERE DateEnd IS NULL OR DateEnd >= MyDate


.... has the advantage of saying what it means and working in all database
systems


HTH


Tim F

 

[Allen Browne]

Tim's WHERE clause expression is also is faster, since it can use any index
on MyDate. And it avoids the problem where JET regularly misunderstands the
data type of an expression that uses Nz().

But I think Rod's point has to do with the fact that there is an extra layer
of complexity in querying the data. That's true. The standard normalized
design (junction table with the current and historical records in the same
table) means you are using subqueries in the WHERE clause to get the data
you want. It's not particularly difficult, but neither is it particularly
obvious to the average Access user for whom SQL is a foreign language.

raphozs, if subqueries are new, you may ultimately need an introduction such
as this:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

WHERE NZ([DateEnd],[MyDate]) >= [MyDate]

WHERE DateEnd IS NULL OR DateEnd >= MyDate


... has the advantage of saying what it means and working in all database
systems

 

[Guest]

Tim, Allen,

I stand corrected. Yes the separation into two clauses will be faster. There
was a reason I used the NZ function but I cannot now remember. I've never
(yet) experienced any problems with NZ as long as all terms are strongly
typed.

Regards,

Rod

Tim's WHERE clause expression is also is faster, since it can use any index
on MyDate. And it avoids the problem where JET regularly misunderstands the
data type of an expression that uses Nz().

But I think Rod's point has to do with the fact that there is an extra layer
of complexity in querying the data. That's true. The standard normalized
design (junction table with the current and historical records in the same
table) means you are using subqueries in the WHERE clause to get the data
you want. It's not particularly difficult, but neither is it particularly
obvious to the average Access user for whom SQL is a foreign language.

raphozs, if subqueries are new, you may ultimately need an introduction such
as this:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

WHERE NZ([DateEnd],[MyDate]) >= [MyDate]

WHERE DateEnd IS NULL OR DateEnd >= MyDate


... has the advantage of saying what it means and working in all database
systems

 

[Allen Browne]

Well, this is probably a fairly minor issue in most cases, Rod.

But there's an example of the Nz() issue here:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Tim, Allen,

I stand corrected. Yes the separation into two clauses will be faster.
There
was a reason I used the NZ function but I cannot now remember. I've never
(yet) experienced any problems with NZ as long as all terms are strongly
typed.

Regards,

Rod

Tim's WHERE clause expression is also is faster, since it can use any
index
on MyDate. And it avoids the problem where JET regularly misunderstands
the
data type of an expression that uses Nz().

But I think Rod's point has to do with the fact that there is an extra
layer
of complexity in querying the data. That's true. The standard normalized
design (junction table with the current and historical records in the
same
table) means you are using subqueries in the WHERE clause to get the data
you want. It's not particularly difficult, but neither is it particularly
obvious to the average Access user for whom SQL is a foreign language.

raphozs, if subqueries are new, you may ultimately need an introduction
such
as this:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

=?Utf-8?B?Um9kIFBsYXN0b3c=?= <[email protected]>
wrote
in
WHERE NZ([DateEnd],[MyDate]) >= [MyDate]

WHERE DateEnd IS NULL OR DateEnd >= MyDate


... has the advantage of saying what it means and working in all
database
systems

 

[Guest]

I remember why I used NZ; it was purely for convenience. I was using the
Access Query Grid and it was possible that the start date was also null. Thus
the two dates gave four possible valid combinations and used four criteria
lines. Add to this fact that there were other OR-type selection criteria and
the number of criteria lines required escalated. Use of NZ kept the lines
down to a manageable number.

Regards,

Rod

Well, this is probably a fairly minor issue in most cases, Rod.

But there's an example of the Nz() issue here:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Tim, Allen,

I stand corrected. Yes the separation into two clauses will be faster.
There
was a reason I used the NZ function but I cannot now remember. I've never
(yet) experienced any problems with NZ as long as all terms are strongly
typed.

Regards,

Rod

Tim's WHERE clause expression is also is faster, since it can use any
index
on MyDate. And it avoids the problem where JET regularly misunderstands
the
data type of an expression that uses Nz().

But I think Rod's point has to do with the fact that there is an extra
layer
of complexity in querying the data. That's true. The standard normalized
design (junction table with the current and historical records in the
same
table) means you are using subqueries in the WHERE clause to get the data
you want. It's not particularly difficult, but neither is it particularly
obvious to the average Access user for whom SQL is a foreign language.

raphozs, if subqueries are new, you may ultimately need an introduction
such
as this:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

=?Utf-8?B?Um9kIFBsYXN0b3c=?= <[email protected]>
wrote
in
WHERE NZ([DateEnd],[MyDate]) >= [MyDate]

WHERE DateEnd IS NULL OR DateEnd >= MyDate


... has the advantage of saying what it means and working in all
database
systems

 

[Jamie Collins]

I stand corrected. Yes the separation into two clauses will be faster. There
was a reason I used the NZ function but I cannot now remember.

Was it because it involved separating one fact into two statements
i.e. favouring easier to maintain code over code that will perform
better? If so, that would be a legitimate choice.

Jamie.

--

 

[Jamie Collins]

At the risk of upsetting Allen may I add my twopennyworth? Allen's design is
faultless and his post is, as ever, lucid. Follow his advice and you won't go
wrong; you will have a robust design that will not need altering.

I suppose we must take your comments at face value and assume
sycophantism rather than sarcasm

I cannot agree your comments and, due to the nature of newsgroup posts
(a.k.a. design-by-email), I'm suspect Allen would be wary of agreeing
with you also.

The most obvious flaw if that the tables do not have keys e.g. try
this

INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (1, 'Team1', #2006-01-01 00:00:00#, NULL)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (2, 'Team1', #2006-01-01 00:00:00#, NULL)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (3, 'Team1', #2005-06-01 23:59:59#, NULL)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (4, 'Team1', #2007-01-01 23:59:59#, #2003-01-01 00:00:00#)
;

Opps! We have duplicates, overlapping dates for 'Team 1', end dates
before start dates, etc. You could argue that TeamID is business key
which Allen left intentionally undefined but I'd counter that
disparate teams with the same name existing at the same time is a
little unrealistic. I would guess it's a so-called 'surrogate' lacking
a natural key.

Also consider this scenario:

INSERT INTO Employee (EmployeeID) VALUES (44)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (55, 'Team 2', #2005-12-01#, NULL)
;
INSERT INTO Team (TeamID, EmployeeID, JoinStart, DepartDate)
VALUES (55, 44, #2001-12-01#, NULL)
;

The employee has joined the team (2001-12-01) before it came into
existence (2005-12-01). Again, effective keys and constraints are
required to prevent duplicates and ensure data integrity e.g. may
require a sequenced primary key an employee can only be affiliated
with one team.

Rather than a 'faultless and robust design', Allen has provided a mere
outline sketch that is completely lacking in keys, constraints, data
typing, referential integrity, etc and with no explanation. That's not
meant as a criticism (although admittedly the 'primary key'
designations are a bit misleading i.e. is he proposing constraints or
not?): posting a complete design is a newsgroup thread is hard and
time consuming. Here

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3f233ba3336cc

it took me a few hours, a thousand or so words plus a couple of
hundred lines of code to describe a single history table of the kind
being discussed in this thread, and that was using SQL DDL, being
perhaps the least verbose way of describing a table design

Jamie.

--

 

[RaphOzS]

I suppose we must take your comments at face value and assume
sycophantism rather than sarcasm

I cannot agree your comments and, due to the nature of newsgroup posts
(a.k.a. design-by-email), I'm suspect Allen would be wary of agreeing
with you also.

The most obvious flaw if that the tables do not have keys e.g. try
this

INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (1, 'Team1', #2006-01-01 00:00:00#, NULL)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (2, 'Team1', #2006-01-01 00:00:00#, NULL)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (3, 'Team1', #2005-06-01 23:59:59#, NULL)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (4, 'Team1', #2007-01-01 23:59:59#, #2003-01-01 00:00:00#)
;

Opps! We have duplicates, overlapping dates for 'Team 1', end dates
before start dates, etc. You could argue that TeamID is business key
which Allen left intentionally undefined but I'd counter that
disparate teams with the same name existing at the same time is a
little unrealistic. I would guess it's a so-called 'surrogate' lacking
a natural key.

Also consider this scenario:

INSERT INTOEmployee(EmployeeID) VALUES (44)
;
INSERT INTO Team (TeamID, TeamName, TeamStart, TeamEnd)
VALUES (55, 'Team 2', #2005-12-01#, NULL)
;
INSERT INTO Team (TeamID, EmployeeID, JoinStart, DepartDate)
VALUES (55, 44, #2001-12-01#, NULL)
;

Theemployeehas joined the team (2001-12-01) before it came into
existence (2005-12-01). Again, effective keys and constraints are
required to prevent duplicates and ensure data integrity e.g. may
require a sequenced primary key anemployeecan only be affiliated
with one team.

Rather than a 'faultless and robust design', Allen has provided a mere
outline sketch that is completely lacking in keys, constraints, data
typing, referential integrity, etc and with no explanation. That's not
meant as a criticism (although admittedly the 'primary key'
designations are a bit misleading i.e. is he proposing constraints or
not?): posting a complete design is a newsgroup thread is hard and
time consuming. Here

http://groups.google.com/group/microsoft.public.access.forms/msg/04c3...

it took me a few hours, a thousand or so words plus a couple of
hundred lines of code to describe a single history table of the kind
being discussed in this thread, and that was using SQL DDL, being
perhaps the least verbose way of describing a table design

Jamie.

--

To all that responded - thank you so much it works !

RaphOzS