Is it possible to update a field in one table record with data from a different record in the same tables. Record #1 (receiving record) contains the key to the second record. I looked in the Form (Payment Entry) to see if I could use a SQL statement to select the data (dates), but that doesn’t appear possible. I would like to investigate two approaches; first in a (this) Form and second as a query that might make the changes in a batch process.
Thanks in advance for insights.
Joe
Copying data from one record in a table to another on a form is possible with a macro created for that specific purpose. Copying data using a query from one to another (or multiple) record is also possible. It is necessary to be more specific on what you are looking to accomplish in order to provide any relevant answer.
LastRenewalDate and MemberThruDate are updated in Payment Entry Form when dues are paid. The requirement is that a Family Member, which looks just like a Primary Member, except for a P or F, carries the same date information as the Primary. Now, both Primary and Family records must be updated in the Payment Entry form. The goal is to automatically update the Family Member. Hopefully this advances the thinking.
First I must correct my earlier statement. You cannot use a query to copy the data. I should have stated it could be done with SQL. But it would require either running that in Tools->SQL or putting the SQL in a macro for the updating.
Knowing a bit more now about what is wanted presented new problems. The code is not a problem for me. The code is a problem for you. You do not know how to code macros. When changing almost anything in this process, form, table, fields and more, this is going to probably affect the macro code. Then who is going to modify the code? Any code of this type is more in depth that the simple macro from before. Until fixed you cannot enter payments. It’s kind of like relying on a software bug to be fixed.
But still I pressed on. Up until this point I had not look closely at what was in the Master. It appears you are fighting what a relational database is all about - the elimination of redundant data.
I would have placed these family records in a different table linked back to the primary account records. Not only would this eliminate the presented problem but seemingly a variety of other duplicated information such as address, city, state multiple indicators for family and possibly more.
Took some more time & reviewed your Payment Entry form. Now, even without splitting the other family members to a seperate table, only the Primary member needs the correct info for dates. The rest of the family is dependant upon the primary. So on the form, you stay with the member lookup (for the filter) and keep the entry for the payment. Now the only other thing needed is the master record for the selected member. Display that and change the dates directly. No need for the other stuff.
Macros should be avoided where possible. In most cases they are not needed and it may just be some design problems as in your case. You know a LOT more now than when you started. Re-think the above. My first main system (use almost daily) was re-designed three different times to get where it is now.
I wouldn’t be surprised if you couldn’t get rid of some of the queries or views.
Edit:
In order to give you a better (hopefully) understanding, I threw together a couple of new forms & a table. The table is “FamilyMembers” and the two forms are “AlternateMemberMaster” for record entry and “AlternatePaymentEntry” which relates to the above discussion. Don’t expect much - could have done way better. I think this gives you the idea.
Sample: SASMemberMasterMacro.odb
Thank you for all your hard work on this. I am aware of the structural issue with both primary (P) and family (F) members and argued to do it differently with no luck. A task for me is for two linked Calc files that use dates of both P and F. I’ll need to rethink my approach. A 2nd thing I had hoped to learn is how to update one table with data from a 2nd. E.G., if MemberMaster.NotActive =TRUE, make PhoneNumbers.Inactive (new column) = TRUE Is this also not possible w/o a macro? Thanks again
Regardless of separating primary and family records, I believe the PaymentEntry form needs revision. It can be less busy and still allow you to easily update information.
Updating from one table to another, as stated in first paragraph of my answer can be done with an SQL Update statement (using Tools->SQL). Sample:
UPDATE "CALENDER2" SET "EVENT2"= (SELECT "CALENDER1"."EVENT1" FROM "CALENDER1" WHERE "CALENDER1"."MYID" = "CALENDER2"."MYID") WHERE "CALENDER2"."EVENT2" IS NULLThank you for this. I downloaded your sample DB and am studying the Payment Entry. I do like it better than mine. I’ll try to convert it without a new table. Thanks also for the SQL code. I ran this: It ran, but the “PhoneNumbers”.“Inactive” field was not changed??Any idea what I did wrong?
`UPDATE "PhoneNumbers" SET "Inactive"= (SELECT "MemberMaster"."NotActive" FROM "MemberMaster" WHERE "MemberMaster"."MemberID" = "PhoneNumbers"."PhoneID") WHERE "PhoneNumbers"."Inactive" IS NULLFound the above issue. It seems that the PhoneNumbers.Inactive was set to FALSE and not NULL. Changed SQL to say IS FALSE and all is well. Thank you again.
As you may be aware, depending upon the situation, SQL differs. For your specific case, this will work:
UPDATE "PhoneNumbers" SET "Inactive" = True WHERE "PhoneID" IN (SELECT A."MemberID" FROM "MemberMaster" A WHERE A."NotActive" = True)
There is almost always more than one solution.
Got it. Thank you again.
Great. Thanks for the reference. I’ll check it out.