keeping fields in 2 unrelated tables in sync 2

[THWatson]

Using Access 2000

tblTrinity
UniqueID (primary key)
LastName
FirstName
LastName2
FirstName2
HouseNbr
Street

tblUCWMembers
RecordID (primary key)
LastName
FirstName
Address

tblTrinity is the main table in the database. This table holds all of the members in the church. tblUCWMembers is a table of women who are members in the UCW.

Here are the idiosyncracies…
1. Some women, but only about 1 in 20, from tblTrinity will be in tblUCWMembers.
2. Some in tblUCWMembers will not be in tblTrinity.
3. If the woman in tblUCWMembers is in tblTrinity, she could be in tblTrinity as FirstName (if a single family unit, or the primary name in the household) or as FirstName2 (if she is the spouse of the primary name in the household)

What I am looking for is the best method by which to keep the “Address” field in tblUCWMembers in sync with the combined “HouseNbr and Street” fields in tblTrinity, so that when those 2 fields in tblTrinity change the Address field in tblUCWMembers will also reflect the change without the database user needing to manually update.

I realize that I may have to redesign tblUCWMembers to split the Address field into 2 separate fields of HouseNbr and Street…however my starting point is what exists now, and I am looking for the best method for keeping the 2 tables in sync.

Any suggestion would be appreciated.

Thanks.

Tom

 

[Remou]

How about splitting out the addresses into an address table and holding the address key in the two members tables?

 

[PHV]

UPDATE tblUCWMembers U INNER JOIN tblTrinity T
ON (T.LastName = U.LastName AND T.FirstName = U.FirstName)
OR (T.LastName2 = U.LastName AND T.FirstName2 = U.FirstName)
SET U.Address = T.HouseNbr & " " & T.Street

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Or how about a junction table that look like:
tblTrinityUCW
TUCWID Primary Key
UniqueID Foreign Key
RecordID Foreign Key

This table contains only woman in both. So if someone is in this table, use the address in Trinity since that's the current address.

Also, according to the way it's presented, why do you need two firstnames in tbltrinity? It's an either/or not a both.

Are you planning to normalize these tables anytime soon?

 

[JoeAtWork]

Best solution is to normalize the database by adding a People table that holds all information about a person, then link to it with foreign keys from both tblTrinity and tblUCWMembers.

 

[TheAceMan1]

How are ya THWatson . . .

I agree with [blue]JoeAtWork[/blue] in the issue of normalization. It would relieve your current problem and others to come.

As a start, I'm considering members or not can belong to more than one group (like the UCW). With this in mind and normalization, I came up with the following tables:

[tt][blue]tblPeople
PeopleID as (primary key)
LastName
FirstName
Role as text like "Head of HouseHold . . . ect"
NonMember as boolean for tagging non-members
Address
City
State
Zip

tblGroups
GroupID as (primarykey)
PeopleID as Long (foreign key to tblPeople.PeopleID)
GroupName Like "UCW"
'
Other Related Fields
'[/blue][/tt]

Be aware: there's quite a bit of church software out there that may save you all those design hours. Here's one I've seen used in several churches: FlockBase

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[THWatson]

Hi TheAceMan1
First of all, I want to thank everyone for their responses.

Next, I will address fneily's question:"why do you need two first names in tblTrinity?" In actual fact, there aren't two first names.
FirstName is the first name of the primary householder
FirstName2 is the first name of the spouse of the primary householder
(e.g. John Brown is the primary, Mary Brown is the spouse)
LastName is the last name of the primary householder
LastName2 is the last name of the spouse, if they do not have the same last name (as in John Brown and Mary Smith)

Now, TheAceMan1, and JoeAtWork and others, to flesh this situation out a bit.

It's not that I disagree at all with the issue of normalization. For the most part, this database is pretty well normalized and functions well. This tblUCWMembers is only one small piece of the overall database and its sole purpose is for the issuing of receipts for income tax purposes at the end of a year. It does nothing else.

I first started building this database in Access in about 1996 or 1997. The church had formerly used a DOS based program and the company quit supporting it. It also had limitations. At the time we looked at a number of church software programs. The trouble was that any we looked at could not be modified, or added to, in order to produce what we wanted. So, using whatever rudimentary knowledge I had, I imported the data from the old DOS program and started to build.

Since then I have rebuilt it 3 or 4 times, and have added many components. And most things hinge back, one way or the other, to tblTrinity. As I said, the database functions quite well. Recently the Treasurer from another church in our city took a look at receipts and reports that we produce and preferred them to what were being produced though his church's church management program.

I agree that some things might be done differently, and if I were starting from scratch I would follow the model you suggest. In fact, I did something similar when I built a database for a small church where a friend is minister.

So, rightly or wrongly, redesigning stuff that has to do with tblTrinity (which means redesigning the entire database, including a ton of queries and reports) in order to (1)deal with a small number of 50 or so annual UCW receipts and (2)where only 2 or 3 of those addresses change in any given year, feels to me like a ton of work for which I don't have the time right now.

As you say, doing so could relieve future problems to come...and at some point I may well tackle it. But for the moment I am going to ponder how to deal with this very small piece of the overall puzzle.

I expect you can envision where I'm coming from on this. As I say, rightly or wrongly...

I have seen a few programs used by other churches but hadn't heard of FlockBase, so will check it out.

Tom

 

[TheAceMan1]

THWatson . . .

[blue]Roger That![/blue]

I'll brainstorm some more on this and get back to you in the mourning (USA Eastern Time). In the meantime, have you tried [blue]PHVs[/blue] suggestion? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[THWatson]

TheAceMan1
Yes, PHV's suggestion will work.

I am also considering this possibility which would eliminate the need to run a periodic update query...
1. Add UniqueID as a foreign key to tblUCWMembers
2. Create a query based on tblUCWMembers and tblTrinity, with a left join on UniqueID, using all fields from tblUCWMembers, and then a calculated field MailAddress: IIf(IsNull([UniqueID]),[Address],[HouseNbr] & " " & [Street])
3. Use this query as the record source for the UCW form and any reports. In any situations where the UCW member is not in tblTrinity, and there are 1 or 2 of those, the address could be filled in manually.

A further refinement would be to split the Address field in tblUCWMembers into HouseNbr and Street. When I built that table and form - away back when - I didn't worry about that address field because it was a once-a-year usage thing. I should have done so but didn't.

Thanks for your help. As always, much appreciated.

Tom

 

[THWatson]

Well, my solution didn't work well. So, in the final analysis, I have decided to use PHV's solution with a one line refinement.

The full SQL is
UPDATE tblUCWMembers AS U INNER JOIN tblTrinity AS T
ON (T.LastName = U.LastName AND T.FirstName = U.FirstName)
OR (T.LastName = U.LastName AND T.FirstName2 = U.FirstName)
OR (T.LastName2 = U.LastName AND T.FirstName2 = U.FirstName) SET U.Address = T.HouseNbr & " " & T.Street;

I can run this update query from the UCW form.

The line in bold is the additional line.

Thanks again for input and assistance.

Tom