modifying poor design of inherited database

[Guest]

My company has a Access 2002 database to track construction jobs. The
database is 26MB, main table is "Jobs", which has 40 fields.

When a new job is created, we must enter job ID and name into each of 8
related tables, such as "architect" and "general contractor". The original
design didn't use good relational design, and all 9 tables contain the same
first 2 fields. But we're stuck with this database for a while.

1. Is there some easy way to automate populating these 8 other tables with
new job ID and name? I only need to add the data in these 2 fields from one
table into the corresponding fields in the other 8 tables. I only have to
add one or two rows each time, but some days I have to populate the other 8
tables several times as new jobs come in.

2. I need to add more fields to the database. It would be easy to add
these 15 fields to the current 40 fields of the main job table, but I'd
rather follow good design and create a new linked table. I could use some
help. The main Jobs table must contain all jobs, even cancelled ones. I
need more info on some of these jobs, which have been cancelled. I need
several fields to record date and reason why jobs were cancelled. I'm having
trouble figuring out how to set up new "cancelled" table.

Any help is appreciated! thank you

 

[Guest]

Does your relationship chart have anything in it? If not, it might be fairly
easy to change your database.

 

[John Vinson]

My company has a Access 2002 database to track construction jobs. The
database is 26MB, main table is "Jobs", which has 40 fields.

When a new job is created, we must enter job ID and name into each of 8
related tables, such as "architect" and "general contractor". The original
design didn't use good relational design, and all 9 tables contain the same
first 2 fields. But we're stuck with this database for a while.

Well... not really. Why not create a single Contractor table, with an
additional field ContractorType (e.g. "General", "Architect",
"Electrical"), and run Append queries to populate it; rename or delete
your eight tables; and create Queries with the same name as your
tables? Your application should work without even noticing the change.

1. Is there some easy way to automate populating these 8 other tables with
new job ID and name? I only need to add the data in these 2 fields from one
table into the corresponding fields in the other 8 tables. I only have to
add one or two rows each time, but some days I have to populate the other 8
tables several times as new jobs come in.

Now I'm confused. Your table (or tables) of contractors should NOT
contain ANY job information. If you have the same GC working on
multiple jobs, are you adding their name and address information
repeatedly!?

2. I need to add more fields to the database. It would be easy to add
these 15 fields to the current 40 fields of the main job table, but I'd
rather follow good design and create a new linked table. I could use some
help. The main Jobs table must contain all jobs, even cancelled ones. I
need more info on some of these jobs, which have been cancelled. I need
several fields to record date and reason why jobs were cancelled. I'm having
trouble figuring out how to set up new "cancelled" table.

I'd say that you would want the JobID to be the Primary Key of this
table - a one-to-one relationship, used for "Subclassing" just
cancelled jobs. You can fill this in using a subform on the Jobs form.

John W. Vinson[MVP]