Query/Design help

[Arty]

Hi,

I'm designing a DB for our company. Well, actually I have already done the
initial design and have put together a whole bunch of forms but then I
thought of the queries and wasn't sure my design was all that good.

Well, what we'd like to do is keep track of hire histories for our personnel
so basically I created a hire table that all of the history for each
employee will go in. Then for each of the hires there will be
sub-histories - when they change departments, levels, etc. (I created
separate tables for each of those). It seems to work nicely while you're
entering the info. But let's get back to queries. What if wanted to see
their current position information including departments, levels, etc.? To
me, it seems that I would have to run a whole bunch of sub-queries on those
history tables to pull up the most current record (by probably using "top 1"
and "order by start_date desc") and then join them all together with one
big query. It's just seems too "bulky" to me... Plus I need to get that info
quite often.

I thought that I could add a field called "current" or something like that
to each of the history tables but it just doesn't seem right to me in terms
of the design....

I would really appreciate your suggestions. Thank you in advance.

Arthur

 

[Fritz Range]

You probaly need a new table that is the history table.

You would set up a many to many relationship

In other words, you would not have a key field that would
restrict entries

sample table

HistKID AutoNumber Key Field
HistEmployeeFK Integer Employee Foreign Key
HistPositionFK Integer Position Foreign Key
HistDatePostion Date/Time Date Position Obtained

In you regular table, you could have the same type of
fields

EmployeeKID Integer Key Field
PositionFK Integer Position Foreign Key
DatePosition Date/Time Date Position Obtained

Create a form and on your after update event, populate the
history table.

You would need to develop reports and forms to "find"
history.

 

[Larry Daugherty]

Hi Arty,

From your post it seems that you've done a lot of studying but haven't yet
done much at the level where the rubber meets the road. That's not a
put-down. Most people do it the other way and try to get a lot done before
they've done any reading. That's not a put-down either.

A good application will be designed around output objectives.

Your intuition about hierarchies of queries is correct. If your modeling of
the real-world entities you're tracking is accurate and if your data is
normalized to at least 3rd normal form then the queries will be easy.

In the application you've sketched you'd probably have a high level table,
say tblEmployee, There would be another table, say tblPosition, that would
contain the date of job change and department and some key or description of
the employee's job, possibly a field for notes.. As you surmised, the most
recent date for this employee will be in the record for her/his current
position and the employee's complete promotion history would be available
via a simple and similar query.

Don't be overly concerned about the number of queries in your application
but do keep the Database|Query window as clear as possible. That protects
you from accidentally tweaking the wrong query or from trying to share a
query for several different purposes. A multi-purpose query will eventually
get tweaked for one of those purposes and blow the others away. The way I
do that is to start my form design based on the highest level table the form
needs to see. Once I'm into the form design, I click on the ellipsis (
.... ) to the right of the Data Source text box in the form's properties
which brings up the QBE wizard. I then design the query for what I'm doing,
adding all of the necessary tables (or even other queries) at that point.
When finished, allow Access to save the query in the form or report but not
to save it to disk/the query window. Your mileage may vary.

hth