If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
When one field depends on another
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 |
#2
|
|||
|
|||
When one field depends on another
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. -- Slainte Craig Alexander Morrison Crawbridge Data (Scotland) Limited "JMF" wrote in message ... 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 |
#3
|
|||
|
|||
When one field depends on another
"Craig Alexander Morrison" wrote in message ... 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 he 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
LOOKUP FOR A VALUE ON A TABLE | Samora | New Users | 9 | February 22nd, 2005 01:06 PM |
New Record Update | Michelle | Using Forms | 5 | October 28th, 2004 07:59 AM |
field manipulation | Steve | Running & Setting Up Queries | 2 | May 28th, 2004 03:12 PM |
Supress blank lines in DOCPROPERTY field | Mary | Formatting Long Documents | 10 | May 25th, 2004 07:27 PM |