Trying to simplify update/insert in two linked tables

[Notiophilus]

I have a list of authors, each of which may go by multiple pseudonyms. One of these is the "main" pseudonym and is displayed in forms, reports etc. (E.g. "JRR Tolkien" is the main pseud, synonymous to "John Ronald Reuel Tolkien" and "hobbitlover1")

• tblAuthor: AuthorID [PK, autogenerated], MainPseudID
• tblPseud: PseudID [PK, autogenerated], Pseud [text], AuthorID

where AuthorID.tblAuthor = AuthorID.tblPseud (one-to-many).

When I want to add a completely new author, by entering a new pseudonym into a form (=strNewPseud) I do the following:

        ' 1) add new pseud (text), get autogenerated PseudID
        strSQL = "INSERT INTO tblPseud (Pseud)" & _
                " VALUES ('" & strNewPseud & "');"
        DBEngine(0)(0).Execute strSQL, dbFailOnError
        
       ' 2) insert that PseudID as the main pseud into tblAuthor, get autogenerated AuthorID
        lngNewPseudID = ELookup("[PseudID]", "tblPseud", "Pseud = '" & strNewPseud& "'")
        strSQL = "INSERT INTO tblAuthor ( MainPseudID )" & _
                " VALUES (" & strNewPseud & ");"
        DBEngine(0)(0).Execute strSQL, dbFailOnError
        
       ' 3) find that new AuthorID, then update tblPseud with the AuthorID we just got.
        lngNewAuthorID = ELookup("[AuthorID]", "tblAuthor", "[MainPseudID] = " & lngNewPseudID)
        strSQL = "UPDATE tblPseud" & _
                " SET tblPseud.AuthorID = " & lngNewAuthorID & _
                " WHERE tblPseud.PseudID = " & strNewPseudID & ";"
        DBEngine(0)(0).Execute strSQL, dbFailOnError

It works perfectly well, but I can't help second-guessing myself. Is this really the most efficient approach?

 

[June7]

Why not just use a BOUND form and forego the VBA to INSERT or UPDATE?

Otherwise might explore "UPSERT" https://stackoverflow.com/questions/6199417/upserting-in-microsoft-access

 

[Notiophilus]

I use bound forms normally but this specific form needs to be unbound. I've used Upserts all of once, but I think I'd run into the same problem, right - having to get the new autonumber PseudID and the new autonumber AuthorID simultaneously?

> scratch that, it's bound to tblAuthor. hmmmmmm why did I do that

 

[ebs17]

It works perfectly well

If you set referential integrity in the relationship (which is a must in the vast majority of cases), your action will fail in this order.

1) You insert into the primary table (tblAuthor) and remember the newly created primary key
2) Now you can enter into the secondary table (tblPseud) and use the saved primary key as a foreign key.

insert that PseudID as the main pseud into tblAuthor

Should this be a second key to link the tables? What are you doing? Did you think of that yourself, or who teaches something like that?

For main pseud:
Create an additional field in tblPseud for this information:
- Boolean field where the main pseud receives a True or
- Number field, where e.g. 1 indicates the main pseud

 

[Notiophilus]

If you set referential integrity in the relationship (which is a must in the vast majority of cases), your action will fail in this order.

1) You insert into the primary table (tblAuthor) and remember the newly created primary key
2) Now you can enter into the secondary table (tblPseud) and use the saved primary key as a foreign key.


Should this be a second key to link the tables? What are you doing? Did you think of that yourself, or who teaches something like that?

This is what the relationship looks like. Referential integrity is enforced with cascade update and delete. tblAuthor.MainPseudID is not required; tblPseud.AuthorID (FK) is not required. This may be bad design, I'm not sure; I remember struggling when I first built that part of the database a few years ago.

Should this be a second key to link the tables?

Do you mean having two relationships between the tables - tblAuthor.AuthorID (PK) = tblPseud.AuthorID and tblAuthor.MainPseudID = tblAuthor.PseudID(PK)? I had no idea that was possible. Is this what you mean?

What are you doing? Did you think of that yourself, or who teaches something like that?

I'm self-taught and doing this for fun, so that should answer both your questions.

 

[Notiophilus]

For main pseud:
Create an additional field in tblPseud for this information:
- Boolean field where the main pseud receives a True or
- Number field, where e.g. 1 indicates the main pseud

Sorry, just saw your edit. This does make a lot of sense, and might make updating simpler. How would you ensure that each author has exactly one main pseud - no more, no less? That was my main reason for the structure I posted above.

Thanks for taking the time to answer. I'm trying to revamp this thing and it's hard to keep my ideas straight.

 

[ebs17]

How would you ensure that each author has exactly one main pseud - no more, no less?

1) Check when entering. If you have several records and want to change the default, then first set all of them to False and then the new one to True.

2) As a real safeguard, install a check constraint in tblPseud, in the sense of "number of True for a given AuthorID <= 1"

Validation Rules for Access Tables

Declarative data validation in Access with Validation Rules and Check Constraints.

codekabinett.com

You should think again about “no less”.

 

[Notiophilus]

Thanks!

 

[Pat Hartman]

I'm not surprised that you had trouble implementing this structure. It is pathological. You have two tables, each of which requires a row to have been added to the "other" table before a row can be added to "this" table.

I don't get the necessity of assigning a priority to the pseudonym.

I would have used the concept of "family" that we see used in mailing lists in order for all family members to exist in the membership (i.e. tblAuthor) list but one if them is designated as the "family" ID. So, any row with the MainAuthorID value = AuthorID is either singular or the parent of a group of pseudonyms. Some people leave the MainAuthorID null for the "master" record but logically it is easier if it ALWAYS has the "master" value. That way you always use the MainAuthorID as the reference ID. In this schema, which is commonly used by churches, when a child leaves home and now has his own address, the "family" ID is changed to be his own ID so he becomes the head of his own family.

 

[June7]

BOUND form/subform will handle passing new master ID to new dependent record.

Can use @@Identity to pull ID generated with CurrentDb.Execute method https://www.access-programmers.co.uk/forums/threads/how-to-get-last-inserted-id-in-a-table.253284/

Can set compound index to assure authorID/pseudoID pairs cannot be duplicated and let Access nag users or write code to check for so users aren't annoyed by error messages after doing a lot of data entry.