How do I make particular fields of one record equal the value of another.

[LS Dave]

Hello,



I am new to Access and this forum, as perhaps the thread title makes evident.

I have created my first two tables ever.

The first is ClientList, which contains typical contact and biographical information (name, address, citizenship, etc), and unique ClientID# for each client. The primary key for this table is the default Autonumber ID that comes with each new table.

The second table is WillInfo, which contains information specific to drafting the client's Will (e.g., spouse name, spouse address, spouse citizenship, similar data on beneficiaries, similar data on executors, etc). The primary key for this table is ClientID#.

I then created a One-to-One relationship between ClientList and WillInfo, binding by Client ID. All this appears to work.

My question arises because I have two clients who are married to each other, which means much of the spouse info I require for the WillInfo table in respect of these particular clients is already accurately recorded as client info in the Clientlist table. So for these specific clients (but not generally!), I want the spouse information in the WillInfo table (e.g., SpouseAddress, SpouseCitizenship for ClientID# 12.001) to EQUAL specific values provided in the ClientInfo table (i.e., ClientAddress, ClientCitizenship for ClientID# 12.002).

I read and understand this is the best approach, following the principle that data should not be entered twice, so as to increase efficiency and avoid mistakes and future problems.

My question is: How do I do this? In Excel, if the client info I wanted to replicate was in cells B4-B9, I would enter =B4, or =B5, or =B6 and so on in the cells for spouse info. What is the equivalent expression for replicating specific client info from a different table, please?
Thanks,
LSDave

[JoeM]

I wouldn't use one to update the other. What if at some point down the road, after the data has been entered, you change it the client but subsequently forget to change it in the Spouse record.

Here is what I would do. Add a field in your WillInfo table that indicates whether or not the address is the same as the matching record in the ClientList table (Yes/No field). If it is, check this box, and leave the address info in the WillInfo table blank. That way it only needs to be maintained in one table for these people, and changes can't cause data discrepancies.

If you control data entry via Forms (which you should do anyway as having users enter data into Tables directly is to be avoided), you can further add code to enable/disable the WillInfo address fields based on the value of the Yes/No field (and you can actually have VBA code that will clear the WillInfo address information if they select "Yes").

[LS Dave]

I appreciate your reply, but I don't quite understand.

You are correct, I certainly wish to avoid the concern you raised about updating the one address field, and not the other. That's why I thought the Excel equivalent to making the SpouseAddress cell "=B4" (where B4 is the relevant ClientAddress cell) would serve. In that Excel scenario, in the case of an address change, I could confidently update only the B4 cell, knowing that the SpouseAddress cell with value of "=B4" would change automatically.

Is there not an equivalent mechanism in Access, so that this one particular field in this one particular record will equal another field in another record and update automatically?

If no, then I have questions about your proposed solution. First, I will provide more details to illustrate, because I wonder if I wasn't clear in my first post.

I am not sure of the term, but I intend ClientInfo to be the main/central/primary/master table in this Database, and as much as possible replicate redundant data from that table to the other tables. The WillInfo is a table only used for a subset of clients that require Will drafting services. Here is a snippet of the two tables:


ClientInfo Table

ClientID	ClientName	ClientAddress	ClientCity	ClientDOB
001	Jane Doe	29 Privacy Place	London	12/12/12
002	John Doe	38 Anonymity Ave	Paris	11/11/11

WillInfo Table

ClientID	SpouseName	SpouseAddress	SpouseCity	SpouseDOB
001	John Doe	38 Anonymity Ave	Paris	11/11/11
002	Jane Doe	29 Privacy Place	London	12/12/12

These are tables I create and fill on my end--no direct client input or forms involved. For most clients, all spouse info will be unique and so have to be entered (and updated) manually in the WillInfo table. However, in scenarios where I represent both spouses (like the Jane and John Doe), would it not be best for 001.SpouseAddress in WillInfo table to equal (i.e., replicate, or update from) 002.ClientAddress in ClientInfo table? The intended effect is that when I update John Doe's address in ClientInfo table as needed, it automatically changes Jane Doe's SpouseAddress in WillInfo.

Are you suggesting this is not a good approach, or not workable? Admittedly, I know little about Access, but I find it hard to believe that an advanced app like Access lacks a comparable function to Excel's "=B4".

EDIT: I meant to address this.

Here is what I would do. Add a field in your WillInfo table that indicates whether or not the address is the same as the matching record in the ClientList table (Yes/No field). If it is, check this box, and leave the address info in the WillInfo table blank. That way it only needs to be maintained in one table for these people, and changes can't cause data discrepancies.

I am not sure I understand. The address won't be the same as the matching record, because the records don't match. Specifically, the spouse info for Client#001 can be found in the client info for Client#002--this only occurs in the limited scenarios where I am retained by both spouses. If the records don't match, would your suggestion still work? If yes, could you please elaborate?


Thanks very much for your help.

LSDave

[JoeM]

You are correct, I certainly wish to avoid the concern you raised about updating the one address field, and not the other. That's why I thought the Excel equivalent to making the SpouseAddress cell "=B4" (where B4 is the relevant ClientAddress cell) would serve. In that Excel scenario, in the case of an address change, I could confidently update only the B4 cell, knowing that the SpouseAddress cell with value of "=B4" would change automatically.

Is there not an equivalent mechanism in Access, so that this one particular field in this one particular record will equal another field in another record and update automatically?

There is, but probably not used to the way that you understand it. Access and Excel are very different - one is a spreadsheet program and the other is a relational database. In a relational database, one of the most important and powerful things is the "relationships" between the tables (how the tables are all lined together). Typically, to follow the rules of normalization, data should never be repeated among various tables (that ruins the dynamic nature of the database and can undermine data integrity). Linking your two tables together, you can get what you need. Anything that can be returned or calculated in a query, there is usually no need to store it down on the table level (unless you were creating historical records about how something looked at an exact point in time).

In looking at your design, if I understand it correctly, I don't think you need address information in the WillInfo table at all. It can all be gotten by linking to your ClientInfo table (everyone if you WillInfo table should have a record in your ClientInfo table, right)?

For more on database design and rules of normalization, have a look here: http://www.deeptraining.com/litwin/d...aseDesign.aspx

[LS Dave]

In looking at your design, if I understand it correctly, I don't think you need address information in the WillInfo table at all. It can all be gotten by linking to your ClientInfo table (everyone if you WillInfo table should have a record in your ClientInfo table, right)?

You are correct that everyone in WillInfo has a corresponding record in ClientInfo. Clients requiring will drafting are a subset of my clients. I do not require address and other information pertaining to a client's spouse EXCEPT for Wills, which is why I created a one-to-one relationship to the WillInfo table. I want to store spouse info and all the other Will details separately, rather than cluttering the ClientInfo table with a bunch of Will-related fields that are only applicable to a minority of clients. Is my approach incorrect?

For more on database design and rules of normalization, have a look here: http://www.deeptraining.com/litwin/d...aseDesign.aspx

I understand that data should never be repeated and the goal that "data entry, updates and deletions will be efficient" is my motivation for starting this thread. I have limited cases where I represent two spouses who both want wills, and in those limited cases the information in WillInfo will necessarily overlap with data already entered in ClientInfo. To avoid redundant data entry and all the pitfalls that come with it, where possible in WillInfo table I want to reference the existing data in ClientInfo table. Is my approach correct?

I know the basics of queries--enough to combines fields from both tables for the purpose of mail merge. Is it a query I need to fill in the spouse-info fields? Any direction you can provide is greatly appreciated.

I sincerely thank you for your patience and time,
LSDave

[JoeM]

This may seem like a silly question, but why would a Spouse's address ever be different from the Client's address?
If they are married, isn't it reasonable to believe that they are living together at the same residence?

So do both your ClientInfo and WillInfo contain ALL clients and spouses?
Are there records in one table that might not appear in the other?

[LS Dave]

This may seem like a silly question, but why would a Spouse's address ever be different from the Client's address?
If they are married, isn't it reasonable to believe that they are living together at the same residence?

So do both your ClientInfo and WillInfo contain ALL clients and spouses?
Are there records in one table that might not appear in the other?

Thank you for your response and your question is not silly. You're right it wouldn't occur often, but I wanted to plan for the possibility and thought a separate address field for a spouse would cover that possibility.

I've read more about normalization. I'm now getting the sense if I want to achieve complete normalization, I should plan for many more tables than I have, and that addresses (as just one example) are not data that should go into a table about WillInfo, or into ClientInfo but rather in a separate tblAddresses. This approach would allow me to point multiple people to the same address duplicating data.

I also read that the best approach would be to an all inclusive tblPeople which includes ALL people (clients, beneficiaries, executors, and every other business contact). To this point, all my thinking and planning for this database centred on the idea that I would have a main table dedicated to a client list and client info, and secondary tables related to other tasks. It never occurred to me to mingle my clients with non-client contacts, and honestly that approach still seems counterintuitive to me.

Given what you've read about my objectives above, do you think I should be striving for complete normalization? If I do, it's going to turn my original plan for the database on its head.

I welcome your comments or suggestions.

In answer to your other question: ClientInfo contains no info about spouses, and includes ALL my clients (regardless of whether they request a will), indexed by ClientID. For the subset of clients that want me to draft a will, I would create a record for them in WillInfo, which would contain all the data needed to draft the client's will, including info about spouses, beneficiaries etc. Most of the time, the spouse info entered would be unique data in the database. When I draft wills for clients who are married to each other (e.g., Client.001 and Client.002 in my Willinfo), suddenly much of Client.001's wife's details, which I needed in WillInfo is already stored in ClientInfo, but in the record for Client.002.

LS Dave

[JoeM]

Yes, I would strive for normalization (at least to around the 3rd level). You will find that while it can take a little extra work to set up, it will save you lots of aggravation down the road. The first database I ever created, I didn't understand these concepts, and programmed myself into a corner I could not get out of. After a few months of spinning my wheels, I bought a book on Relational Database Theory and Normalization, and re-designed it. They are still using and expanding that database over 10 years later!

Based on your last post, it sounds like you are headed in the right direction!