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
|
|||
|
|||
Design relational
I have a simple data base with a Householder1 field and
Householder2 field and a field for duties that each householder performs eg: Cooking, Washing etc. I have a seperate table that lists all the duties. How do I design a form or subforms where the Householders names are listed and all the duties they perform. I am confused Thanks in advance |
#2
|
|||
|
|||
Roger
It sounds like the design you described uses a single field (duties performed) to hold multiple facts (Cooking, Washing, ...). This is contrary to good data design practices urging one fact-one field. In an instance like you are describing, you have a one-to-many relationship. One Householder can have zero, one or many "duties performed". You need a third table to hold "duties performed by Householder. This brings up a second potential issue. If your record holds two people (Householder1 and Householder2), which one of these is the one performing the duties? A more normalized design would use one row/record per person, so you could then use that person's ID in the "duties performed by Householder" table to indicate who is doing it. Is there a need for you to keep Householder1 and Householder2 associated with each other? If so, you probably need a table to hold that relationship. -- Good luck Jeff Boyce Access MVP "Roger" wrote in message ... I have a simple data base with a Householder1 field and Householder2 field and a field for duties that each householder performs eg: Cooking, Washing etc. I have a seperate table that lists all the duties. How do I design a form or subforms where the Householders names are listed and all the duties they perform. I am confused Thanks in advance |
#3
|
|||
|
|||
Thanks Jeff for you help on this occasion and all the
advice you give to us novices over time. If I have a person's ID field, does an ID have to be provided by typing one in for each new person, or can it be automatically generated? Thanks again -----Original Message----- Roger It sounds like the design you described uses a single field (duties performed) to hold multiple facts (Cooking, Washing, ...). This is contrary to good data design practices urging one fact-one field. In an instance like you are describing, you have a one-to-many relationship. One Householder can have zero, one or many "duties performed". You need a third table to hold "duties performed by Householder. This brings up a second potential issue. If your record holds two people (Householder1 and Householder2), which one of these is the one performing the duties? A more normalized design would use one row/record per person, so you could then use that person's ID in the "duties performed by Householder" table to indicate who is doing it. Is there a need for you to keep Householder1 and Householder2 associated with each other? If so, you probably need a table to hold that relationship. -- Good luck Jeff Boyce Access MVP "Roger" wrote in message ... I have a simple data base with a Householder1 field and Householder2 field and a field for duties that each householder performs eg: Cooking, Washing etc. I have a seperate table that lists all the duties. How do I design a form or subforms where the Householders names are listed and all the duties they perform. I am confused Thanks in advance . |
#4
|
|||
|
|||
Roger
Take a look at the Autonumber data type in table definitions. An ID can be automatically generated. Now, do you want an ID number to relate a Householder to duties (in which case no human really needs to see the ID #), or do you want/need an ID that humans will use/see? If the latter, you'll need to create a sequence numbering routine to assign IDs (and you could still use AutonumberIDs for the row identifiers). I'd suggest brushing up on relational database design and normalization to help with this (data design) portion... Good luck Jeff Boyce Access MVP "Roger" wrote in message ... Thanks Jeff for you help on this occasion and all the advice you give to us novices over time. If I have a person's ID field, does an ID have to be provided by typing one in for each new person, or can it be automatically generated? Thanks again -----Original Message----- Roger It sounds like the design you described uses a single field (duties performed) to hold multiple facts (Cooking, Washing, ...). This is contrary to good data design practices urging one fact-one field. In an instance like you are describing, you have a one-to-many relationship. One Householder can have zero, one or many "duties performed". You need a third table to hold "duties performed by Householder. This brings up a second potential issue. If your record holds two people (Householder1 and Householder2), which one of these is the one performing the duties? A more normalized design would use one row/record per person, so you could then use that person's ID in the "duties performed by Householder" table to indicate who is doing it. Is there a need for you to keep Householder1 and Householder2 associated with each other? If so, you probably need a table to hold that relationship. -- Good luck Jeff Boyce Access MVP "Roger" wrote in message ... I have a simple data base with a Householder1 field and Householder2 field and a field for duties that each householder performs eg: Cooking, Washing etc. I have a seperate table that lists all the duties. How do I design a form or subforms where the Householders names are listed and all the duties they perform. I am confused Thanks in advance . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help with design | Ronnie | Database Design | 6 | March 12th, 2005 02:53 PM |
Who owns the copyright on graphic design layouts prepared in MS Wo | Karen | General Discussion | 4 | February 1st, 2005 07:01 AM |
How to assign a menu bar to a report opened in design mode | Gordon | Setting Up & Running Reports | 0 | January 20th, 2005 12:09 AM |
Action queries changing when reopened in design view | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 12:34 AM |
finding design examples for Invoices and Payments | Paul James | Database Design | 2 | June 9th, 2004 07:14 PM |