When one field depends on another

[JMF]

I realize this subject has been discussed in various forms other times, but
I need to make another pass through it.

The last time I saw it was when a poster wrote that he had makes of cars
(Ford, Toyota, etc.) and wanted another field to have the specific model.
That field, of course, depends on the make of the car.

Another situation is for example, time management for a number of projects,
where each project has a set of tasks. So if you want to have two fields,
for project and task, the task will depend on the project.

Since I'm relatively new to Access, it doesn't seem like this can be handled
without "special treatment." That is, you can't just use the standard
editing facilities, but have to begin using things like "on update" or other
techniques that have been mentioned, including some VB programming possibly.

In fact, it seems like it's a relational database thing, the difficulty of
working with fields that depend on each other in the same table. But I
haven't even found a reasonable solution when trying to separate things out
into different tables.

But of course, as a newcomer I may be completely wrong, and would be happy
to get insights from experts! So, does anybody have any insights or
direction to point me in to understand better the topic of "when one field
depends on another"?

Thanks,

John

 

[Craig Alexander Morrison]

The only field(s) that should be depended upon are the field(s) in the
PrimaryKey.

All other dependencies should be removed during the normalisation process.

Relational Database designs tend to be counter-intuitive.

In the Car example you would need a Manufacturer table containing Ford,
Toyota etc.

You would also have a Model table containing the Model Name/Number and a
ForeignKey relationship to the Manufacturer. This assumes the Model relates
to a single Manufacturer.

In the Projects example you would have a Projects table containing the name
of the Projects.

You would also have a Tasks table and unlike the Cars example this may only
contain the Name of the Tasks.

You could then have a third table which joins the Projects to the Tasks to
allow a Project to have many Tasks and similarly to allow a Task to be used
in several Projects.

Of course the actual design depends upon your exact requirements.

I would strongly suggest you read up on "Normalisation" or "Relational Data
Analysis" to understand more fully the logic behind the representation of
data in a Relational Database. Please note you MUST use forms to help join
the data back together, using the narrow tables created through
normalisation will be very unproductive.

If you have come from Spreadsheets this will be a steep learning curve, once
you "get normalisation" Access becomes very easy to use.

 

[JMF]

The only field(s) that should be depended upon are the field(s) in the
PrimaryKey.

All other dependencies should be removed during the normalisation process.

Relational Database designs tend to be counter-intuitive.

In the Car example you would need a Manufacturer table containing Ford,
Toyota etc.

You would also have a Model table containing the Model Name/Number and a
ForeignKey relationship to the Manufacturer. This assumes the Model
relates to a single Manufacturer.

Yes, that is the case here.

In the Projects example you would have a Projects table containing the
name of the Projects.

You would also have a Tasks table and unlike the Cars example this may
only contain the Name of the Tasks.

You could then have a third table which joins the Projects to the Tasks to
allow a Project to have many Tasks and similarly to allow a Task to be
used in several Projects.

Okay, I think I get the idea here: the third table would contain two fields,
one for projects and one for tasks. Each row would associate a project with
a task. If it was only one project per task (like the car example), then you
could not have two rows/records like:

task1 project1
task1 project2

Of course the actual design depends upon your exact requirements.

As it happens, my requirements are in fact one project to many tasks but not
vice versa (therefore like the car example).

Then, I would track time by recording "on this day I worked n hours on task
x of project y." I could have several entries on a single day, of course,
for different combinations of projects and tasks.

My naive idea would be to have an "hours worked" table with:

date, hours, project, task

Each record would represent a new entry. Would this be "correct"?

I would strongly suggest you read up on "Normalisation" or "Relational
Data Analysis" to understand more fully the logic behind the
representation of data in a Relational Database. Please note you MUST use
forms to help join the data back together, using the narrow tables created
through normalisation will be very unproductive.

If you have come from Spreadsheets this will be a steep learning curve,
once you "get normalisation" Access becomes very easy to use.

Thanks, that's great. I've started reading up on it -- as usual, the
Wikipedia gets you quickly to the subject with some articles.

John