Microsoft Access 2019, One to One relationships (SuperType -> SubType)

Question

I am getting frustrated understanding how to setup a one-to-one relationship in Microsoft Access 2019. I have a tblAccounts that is subtyped to tblVendors and tblClients.

Now I might not have a full understanding of how this should work, but my "Access 2019 Bible" barely touches this subject, and I cannot find any online examples of such.

My setup is, to keep it simple:

tblAccounts
-----------
AccountID    [AutoNumber, PK]
AccountName  [ShortText]
AccountData...

and

tblClients
---------
AccountID    [Number, PK-FK]
ClientData...

tblVendors
---------
AccountID    [Number, PK-FK]
VendorData...

I have the relationships setup correctly, I believe... As of now, I can create an account record, then separately Create a Client/Vendor record. It is one-to-one with Referential Integrity, so will only allow AccountIDs that exist, and only allow them once. BUT I have to hand type the IDs of the account.

What I am looking for, when I create a SELECT query between tblAccount and the subtypes, is how to get the records share the same AccountID auto-magically. Or am I supposed to use a macro that adds the secondary records into the Client/Vendor tables? Or am I going about this completely wrong/ my tables need re configuring.

I am only at the Table and Query design level of my project at this point. So wondering if this is not accomplish-able on this level?

Thanks all!

---

Edit:

Clarification,

I am looking to strengthen the tied relationship between tblAccounts and it's subtypes. So whenever, at any point, a record is created in tblClient or tblVendro a supper record is created in tblAccount related to that client or vendor via the AccountID.

As apposed to now, where I have to create a record in tblAccounts first, and then create a record in tblClient/Vendor and hand type the AccountID from tblAccounta.

At this point in my design I am not trying to pull, join or extrapolate any extra data, I just want to understand how One-To-One really works in access, and If I should really be using it at all. Should the Client and Vendor Tables just be independent from one another, though they will be similar in the majority of setup data? My "Microsoft Access 2019 Bible, by Michael Alexander and Dick Kulsleika" Uses this Account -> Customer/Vendor relationship to describe One-to-One, but offers no setup examples. I just thought that the relationship should exist as such, if they took the time to mention it in an actual publicized book.

Answer 1

You would like to be able to store a row in a dependent table in a foreign-key integrity constraint and that this will automatically create the parent row. This cannot work because:

1. A store in the dependent table cannot trigger the AutoNumber property in the parent table
2. Access just does not do that for integrity constraints.

You should look at other mechanisms that better fit more your needs:

• Data macro
  With this you may create a macro that will be triggered by the insert on the dependent table.

• SQL procedure
  With this you may create a procedure that will do the repeated job of inserting a row into the dependent table after checking if a parent row exists, creating it with default values if it doesn't. You may also create procedures for update or delete of the parent table.

Using one of the above may make the foreign-key integrity constraint superfluous. Without it, some database operations may become faster.