View Full Version : transfer data from one field&table to another same name field different table


09-09-2007, 06:31 PM
Could someone please tell me how to (I'm sure its elementary - I'm very new) automatically fill in a field on a child table from a parent table with the same-named field? The tables are in a set and linked by Record ID. If needed, more detail: Parent table [ADDRESS FILE] has [RECORD ID] field, [NAME] field, [ADDRESS] field, then other fields relating to address info. Child table [MEMBER INFO] has [RECORD ID] field, [NAME] field, [ADDRESS] field, then other info relating to their membership. When a person becomes a member, their [ADDRESS FILE] record is opened and their membership level is entered. This creates a new record on the [MEMBERSHIP INFO] table. When the new record is created, I want the [NAME] and [ADDRESS] fields to automatically fill in, based on the common [RECORD ID]. Please, if I am going about this the wrong way, advise me...I have separate tables because I do not want [MEMBER AMOUNT DONATED] field from [MEMBERSHIP INFO] table to show on [ADDRESS FILE] table.

Tom Cone Jr
09-09-2007, 09:22 PM
Your post raises questions about the advisability of maintaining duplicate field values in multiple tables. When a member's address changes are you going to handle the change in the ADDRESS FILE or in the MEMBER INFO table? Or are you planning to make the changes twice each time someone moves or the postal service assigns a new zip code? Often it's best to keep biographic and census data in a single place since it's far easier to maintain. On the other hand, sometimes folks work from prospect lists which function as lookup tables by supplying field values to transaction records when needed.

Leaving that aside for the moment, there are several different ways the field values from one table can be passed to a new record in the second table. Tell us more about the forms you will be using and the exact sequence the user will be following. Will data entry be done using forms based on single tables or on a form based on your set? Is the set linkage one to one or one to many? Tell us how the data will be maintained as changes occur later on.

Is there any chance you could post a working copy of your database here so we can see exactly what you're attempting? We don't need actual data or many records. A representative sample of dummy records would suffice.

09-09-2007, 09:57 PM
I'm in over my head...
I can send database if I knew how.
I want to be able to use either table in the set to edit address info, with entry into either table posting to corresponding record in other table. I am intending to build form for membership to edit/update their own data, using the membership table, but edits to address will also post back to address table. Conversely, I want address table to populate the field in membership table so that there is a record for them to start validation of data from, so they are only editing, not creating.
The intent right now is for there to be a master address file, with a field on address file showing if they are a member. Then, if member, they will populate their own table, having name and address fields in the membership table that are the same as in address file. I want edits to be able to be made in either table, mostly so that the person who enters membership info cannot mess up all address files, only membership address file. And, do not want people who access address file to be able to see how much donated for membership.
Should I just have one table and make this more reasonable???
I intend to have them all using forms for data entry, and have address file as parent table with membership and personal info tables linked as a set.

09-09-2007, 10:17 PM
I think I need to create a form for membership. The form will use data from set (Address file:parent / Membership:child / Personal:child). Then when membership edits data on form it will feed back to ADDRESS FILE table, which will feed edited info to BIO form used by general public, without allowing general public access to all info on membership table.

Am I correct that a separate form would solve this?

Can I secure access to forms or only databases?

HUGE learning curve here, I work in SPSS, this is foreign !!!


Tom Cone Jr
09-10-2007, 07:20 AM
Lauren, it's beginning to get confusing here, too. You started off with two tables and now mention a third. It's sounding as though you are building a web app that individual members would update using their browser, is that correct?

Instead of trying to describe your situation in technical terms, give us a detailed description of what the user would see, and what you want to happen when they enter or update their information. the more details the better.

09-11-2007, 01:46 PM
The result I wanted was achieved by creating a form, based on the linked table set, that allowed viewing and updating from the Address file table, but only displays the portion of data that membership needed to see.

Thanks for the help!

Mike Wilson
09-11-2007, 06:55 PM
Lauren, Tom,
I will chime in here if you don't mind.

I believe you MUST answer Tom's question first. Are you developing a desktop application that will be used by others, either on a single machine, or networked environment, or are you developing a web application?

The intent right now is for there to be a master address file with a field on address file showing if they are a member. Then, if member, they will populate their own table, having name and address fields in the membership table that are the same as in address file. I want edits to be able to be made in either table, mostly so that the person who enters membership info cannot mess up all address files, only membership address file.

Secondly, you are throwing out the term "file". In database terms, would that be a record? I believe I understand what you are looking for. Tell me if this is what you want:

The member will interact with the database with a login of some type that will identify them as a member. They will then be presented with their record that exists in the master Address table, and their record present in the Member table. The master address table is read only, and select fields will be present for their viewing. The membership table, the member has priviledge to change/edit. This action will enter the information into a member table, not changing the master table.

Am I on track so far?

09-12-2007, 09:02 AM
I am working in a desktop application. In the post you reference, file and table were interchangeable.
I have a database set up for use by a small, startup nonprofit. Our goal is to hold many address records (in ADDRESS table) for many different mailing purposes. The first use of the address records was to mail membership applications. Once the applications came back, the info collected is to be put into the database using the MEMBERSHIP table. On the MEM table, I originally had fields for NAME and ADDRESS, that I thought would get filled in via link to ADDRESS table, which is the parent table and shares the field RECORD ID with the MEM table. Using the RECORD ID field, I thought that the MEM table would 'pull over' NAME data from ADDRESS table and display it in the MEM table NAME field. It seems that posting would accomplish this, but I wanted MEM table to further be able to edit NAME field and have it actually update ADDRESS table NAME field as well. The only records edited would be those of members, but the records would be updated within the entire database, not only on the MEM table NAME field.
(There are 3 tables linked as a set: ADDRESS, MEMBERSHIP, PERSONAL. Personal is of little use in this scenario and is only being mentioned as to it being in the set.)
SOLUTION: Instead of having NAME field on MEM table as well as on ADDRESS table, I created a form based on the set ADDRESS/MEM/PERSONAL. Using the form, I can show only data on the form that I need, the updates do not need to post because they are actually at the ADDRESS table, and a report compiled of any field from the set (NAME from ADDRESS table and MEMBER AMOUNT DONATED from MEM table) can be generated, displaying all of the data from records as needed.
This eliminates duplicate NAME records, yet allows only people with MEM access to view donation amount on a 'person's name' basis.
Being very new, and an SPSS user, I didn't come up with this right away, but - elementary!

Tom Cone Jr
09-13-2007, 03:21 AM
In case you haven't discovered it already, there's a template database called "Membership Manager" in the templates subfolder in the A5v8 program files directory. It illustrates a way to organize your membership into various subgroups, a way to track transactions for each member (could be dues or donations), and includes a series of reports. Much of it might be useful to you.

09-13-2007, 08:54 AM
Thank you, Tom - this may become very useful!