Search code examples
ms-accessms-access-2010ms-access-2013ms-access-2016

Multiple table update design in Access

I have learned more, and was compelled to find the solutions, mind changed.

PS dear experts, your help is appreciated in the time-saving nature of having forums and discussion in the first place, and also is mined for usefulness, not street cred. get helping or get off. The time I wasted reading posts where the answer was "I don't understand what you are trying to do..." and then questioning the OP with animus or incredulity, or suggesting some unrelated answer further confusing issues, is seemingly the problem with the world these days, if you want to help, help

Original Post:

Ok so as per the comments, thanks to ANYONE who volunteers any help with this problem.

I have a table and relationship design problem.

I have a table with a pk auto and I want to have a related table with a related column incrementally numbered that updates every time new data is entered in the form that is bound to it. It needs to update the related rows in the autonumbered table's key.

---the answer was to join the table on the autonumbered field.

The autonumber of the first table (main recordsource) is just an ID. I think I need two Primary Keys as I need to update the related record with that number in the entry form and move to NextRec but update the pk in the main table and move to NewRec , how do I join (see jpg image)?

I want enter results and update that existing record but update the adjoining pk so that a new record is created in the main table.

Tourney

more in depth:

From yours

My desired form

note that the calculation table feeds the upcoming games table, where a query shows the players recent results. I would like to split the upcoming results to show the players' recent game history, the second tab I would like to enter either one result or many results at a time. I hope I am clearer. You can see why I have had a challenge. See my form though. The recordsource is the upcominggames table and the data entry form is for input (ENTER GAME DATA)

-----the answer to this was an update query (a separate form) and then requery the statistics form to show the new matchups that were entered.

Solution

  • 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:

    enter image description here

    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.