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
|
|||
|
|||
Confused over database design
I have a bit of a problem and am hoping someone out there may have some
pointers. I'm not an experienced Access user - I can set up a table, perform a simple query and scour help files etc for other bits. my problem is that I'm trying to set up a database for work to search through a list of contractors based upon their area of coverage so we can quickly see which contractors will cover certain sites. I'm not sure how to structure the database - I've got a table with the contractor's details listed out (ie name, address1, address2, phone) plus a field which has a whole load of partial postcodes separated by spacs which I can get a query to search through. I'm aware that this is probably not the best way to do this, and it makes maintaining the list a headache. I also now want to search via regions, as well as Postcodes, so I'm totally confused as to how to search - I thought about having yes/no fields for each region for each contractor and ticking the relevant ones, but can't work out how to then search them. I'm not too clear on the whole relational datatbase thing, and get the feeling I should be splitting the contractor's addresses, their operating regions and postcodes into separate tables but have got completely confused. If anyone has any suggestions or can point me in the direction of some examples, I would be very grateful. Cheers Matt |
#2
|
|||
|
|||
Confused over database design
plus a field which has a whole load of partial postcodes separated by spacs
You need another table instead of this field. Also add an autonumber field as primary key in the Contractor table named ContractorID. New table WorkArea -- ContractorID - long integer - foreign key - associated as many side in a one-to-many relationship from the Contractor table. Postcode - text Region - text Active - Yes/No - optiomal field In Relationships window add both tables, click on ContractorID in Contractor and drag to WorkArea table ContractorID. Select Referential Integerity and Cascade Update. Create a form for contractor and another for workarea. Put workare as subform in contractor. Set Master/Child link using ContractorID. You may want a table for Postcode and Region to select from when adding to the contractor. Use a combo box in the subform to do the selection. "Matt Gotts" wrote: I have a bit of a problem and am hoping someone out there may have some pointers. I'm not an experienced Access user - I can set up a table, perform a simple query and scour help files etc for other bits. my problem is that I'm trying to set up a database for work to search through a list of contractors based upon their area of coverage so we can quickly see which contractors will cover certain sites. I'm not sure how to structure the database - I've got a table with the contractor's details listed out (ie name, address1, address2, phone) plus a field which has a whole load of partial postcodes separated by spacs which I can get a query to search through. I'm aware that this is probably not the best way to do this, and it makes maintaining the list a headache. I also now want to search via regions, as well as Postcodes, so I'm totally confused as to how to search - I thought about having yes/no fields for each region for each contractor and ticking the relevant ones, but can't work out how to then search them. I'm not too clear on the whole relational datatbase thing, and get the feeling I should be splitting the contractor's addresses, their operating regions and postcodes into separate tables but have got completely confused. If anyone has any suggestions or can point me in the direction of some examples, I would be very grateful. Cheers Matt |
#3
|
|||
|
|||
Confused over database design
Hi Karl, thanks for the quick reply. I've tried to follow your steps, but am
having some difficulty getting the relationship to work - all I get is one-to-one. I also can't find how to set the foreign key parameter you mention. I'm using Access 2003 if that makes any difference. Thanks again |
#4
|
|||
|
|||
Confused over database design
On Fri, 12 Jun 2009 02:36:02 -0700, Matt Gotts
wrote: Hi Karl, thanks for the quick reply. I've tried to follow your steps, but am having some difficulty getting the relationship to work - all I get is one-to-one. I also can't find how to set the foreign key parameter you mention. I'm using Access 2003 if that makes any difference. Thanks again There is no indicator in query or table design identifying a field as a foreign key - that's just defined by how you're using the field. If you're getting one to one relationships, you're either linking one table's Primary Key to the other table's Primary Key, or there is a unique index on the linking field in the child table. Reread Karl's post: he did NOT say that the second table, the one with postcodes, should have the ContractorID as its primary key (it shouldn't). -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|