Editing Records

[Guest]

We have a Permit db which is updated yearly. The only thing that changes is
the permit number. For example 01-05 might change to 12-06 but all other data
stays the same in the four tables.

Each table has the 'PermitNo' field name which is used for the 'one to many'
relationships.

I want to just edit the permit number in the form and have it edit the
permit numbers in all of the tables. Right now it just edits one table.

 

[Guest]

Your problem is a result of poor database design. You should not use any
value that may change as a primary key, especially if it is something that
can changed. If you were using an autonumber as the primary key in you parent
table, changing the permit number would be easy because it would not be in
the other tables and require changing.

With that being said, the only solution you have now is to create update
queries to update the fields in the child tables with the new number.

 

[Guest]

There is one table that has an auto number as a primary key but I originally
didn't consider it as parent. I created a test copy and chaged the
relationships with no luck.

So if I were to start from scratch, I should put an autonumber field in each
table that is unique to the permit # but the same in each table?

BTW-I am not dealing with a huge db as I only have 66 records currently

 

[Guest]

Yes. The parent table should have a primary key. That primary key should
have a data type of autonumber. Then each child should have a field with the
data type of Long Integer that contains the same value as its parent table.
Parent Table
PARENT_ID AutoNumber - Primary Key
PARENT_NAME - Text

Child Table
CHILD_ID AutoNumber - Primary Key
PARENT_ID Long - Foreign Key
CHILD_NAME - Text

Parent Table
1 |Sam
2 |Dave
3 |Moe

Child Table
1 | 1 |Sam's First Child
2 | 1 |Same's Second Child
3 | 2 |Dave's First Child
4 | 2 |Dave's Second Child
5 | 3 |Moe's First Child
6 | 3 |Moe's Second Child

The size of your database is irrelevant. You always need good database
design.