1
0
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.
1Add to your post an example of the kind of SELECT that you would like to write. Unclear: In your schema all records share the AccountID with no auto-magic required. Is your problem in using sub-selects, table-joins, or something else? – harrymc – 2019-05-17T20:12:40.873
@harrymc, added clarification to my question. Thank you for your time. – Daniel W – 2019-05-20T14:58:58.100