Proper database setup

[Triedtwice]

I know enough about the Access 2007 I'm using to be dangerous to myself and others, but hopefully someone will feel pity for me and help...

I set up a database to track several different types of data for 300+ "sites". I have a table that has all the site numbers and their names; another table that has all the site numbers and whether they have specific kinds of paperwork (each kind of paperwork is its own field and there's an "x" if it applies to that particular site); the other tables list all the site numbers with a variety of fields. I've identified the site numbers as the primary key for each table and established relationships to tie each primary key (site no.) to the primary key in my first table (Site_List). I'm sure this is a very cumbersome way of getting the job done, but it does get the job done.

Now I have to add a way to track whether one or more of a list of 400 different names has been associated with any particular site...and I want to know the best way to set this up. I'm unable to share the database with anyone due to client confidentiality...I know that makes it almost impossible to help me...but, any takers?

 

[Darren Bartrup]

I'd set up a couple of tables for that - the first lists your 400 different names along with a primary key (auto number unless there's already a unique identifier for each name).

A second table would then have a record containing the site PK and the name PK of any sites and names that are associated - make both fields in this table primary keys so you can't have a name associated with a site twice.
Maybe add a third field to show the type of association?

You can then run a query to return the site and any names associated with it.

 

[Triedtwice]

That sounds very reasonable...I'll try to accomplish what you suggest!