[Solved] Multiple table update design in Access

Question

As from the comments, this is what I’m thinking of when I read your description:

Since the UpcomingGames will be entered first and exactly one GameResult can be entered per UpcomingGame, this will be a 1:1 relationship. As the name Upcoming says: The Upcoming data has to be entered before the Result can make sense. Unless an UpcomingGame can be cancelled, there will indeed be a Result for the Game, so there is no need to separate the information into 2 tables. I’d say, a user interface could look like this:

As you can see, the T_NUM column is an autovalue. Before entering any data, I initialized that column using a query like this (and deleted that record afterwards):

INSERT INTO Games ( T_NUM )
VALUES (1004);

This way, the numbering started with number 1005.

You won’t be able to to avoid gaps in the numbering, as long as the users can remove existing records or cancel the insertion of a new record. If you want at least to avoid the latter, you will need some VBA code in the form.

Accepted Answer

As from the comments, this is what I’m thinking of when I read your description:

Since the UpcomingGames will be entered first and exactly one GameResult can be entered per UpcomingGame, this will be a 1:1 relationship. As the name Upcoming says: The Upcoming data has to be entered before the Result can make sense. Unless an UpcomingGame can be cancelled, there will indeed be a Result for the Game, so there is no need to separate the information into 2 tables. I’d say, a user interface could look like this:

As you can see, the T_NUM column is an autovalue. Before entering any data, I initialized that column using a query like this (and deleted that record afterwards):

INSERT INTO Games ( T_NUM )
VALUES (1004);

This way, the numbering started with number 1005.

You won’t be able to to avoid gaps in the numbering, as long as the users can remove existing records or cancel the insertion of a new record. If you want at least to avoid the latter, you will need some VBA code in the form.