Confused over database design

[Matt Gotts]

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

 

[KARL DEWEY]

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]

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

 

[John W. Vinson]

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).