RE: Capturing an employee in a team at a point in time
- From: Rod Plastow <RodPlastow@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 13 Feb 2007 08:17:02 -0800
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 whatI 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
"raphozs@xxxxxxxxxxx" wrote:
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.
- Follow-Ups:
- Re: Capturing an employee in a team at a point in time
- From: Jamie Collins
- RE: Capturing an employee in a team at a point in time
- From: Tim Ferguson
- Re: Capturing an employee in a team at a point in time
- References:
- Capturing an employee in a team at a point in time
- From: raphozs
- Capturing an employee in a team at a point in time
- Prev by Date: Re: Relationship Issue
- Next by Date: Re: Table to report design
- Previous by thread: Re: Capturing an employee in a team at a point in time
- Next by thread: RE: Capturing an employee in a team at a point in time
- Index(es):
Relevant Pages
|
