Displaying information from two different tables on the same form

[sunwindandsea]

We have a database of church members. It’s on the Internet so that church members can contact one another. All changes to the database are made online by church members. Once a month we download mychucch.mdb to update our database. We want to add several confidential text and memo fields that would not be available on the Internet but we would like to maintain the data in a separate table here so that when we open the form used to lookup member information the confidential info that is maintained here is displayed. When we add confidential information to either the text or memo fields the separate table, CongregantPrivate, is updated. Over time we expect to have many entries in the memo fields of varying lengths. One entry our pastor made has over 500 characters.

This is obviously a one to one relationship. We’ve created a relationship between mychurch and CongregantPrivate based upon CongregantNum and set the Control Source “=[CongregantNum]!PledgeHistory” in design view when form view is selected “#Name?” is displayed. What are we doing wrong? Is this most appropriate manner to display information from two different tables on the same form? Are the memo fields the problem?


Thank you,

Ed

 

[fneily]

add several confidential text and memo fields ". Do you mean each member has several confidential text and memo fields? Or each member just has one text and memo field?
One-to-one relationship are used alot to separate private info from public.
You would then create a query to connect the two tables and select the fields from each table you want to display on the form. If you want the form to be updatable, include the primary keys. Then the form's recordsource would be the query.
Or you could just base the form on the main table and use Dlookup's to retrieve the private info and then write any updates back with DAO or ADO. But that depends on your knowledge of coding.

 

[sunwindandsea]

First, thank you for such a prompt response.

No, each member has 5 text and 2 memo fields in the confidential table. I've made CongregantNum the Primary/Key for each table. I assume each field is a separate query.

I've been unable to create a query that works to display any of field from the CongregantPrivate table on a form. I’ve written a Query named Query Private with using [mychurch]![CongregantNum] querying [CongregantPrivate]![PledgeHistory]. When I run the Query it returns all the member numbers and pledge histories. When I create a query reference on a form using the expression = [QueryPrivate]![ PledgeHistory] the error message returned is “#name?

I did not create a relationship between the two tables. What am I doing wrong?

It just occurred to me that all new records will be created on our web site. This means if a table is downloaded with new member(s) their will be no record in the confidential table for the new member(s). How can I create a record in the confidential table for new members?


Thanks for all you help,

Ed

 

[sunwindandsea]

Oops, I forgot to ask is the problem caused by no MemberNumber beging specified in the Query from the form?

 

[fneily]

When I create a query reference on a form using the expression = [QueryPrivate]![ PledgeHistory]" is confusing. Post your table structures which would make things clearer, eg.
tblCongregantPrivate
CongregantNum Primary Key
other fields

tblOther tables

"I did not create a relationship between the two tables". Tables must be related in a relational database. Maybe this article will help.
Fundamentals of Relational Database Design

http://r937.com/relational.html

For the moment, forget about the web. That's a different story. Post your table structures and then what expected results you want.

 

[sunwindandsea]

I created a relationship between the 2 tables based upon MemberNumber

Form name: Members

tblCongregantPrivate
CongregantNum Primary Key Long Integer
PledgeHistory Memo
MissionaryHistory Memo
LifetimeTotal Integer (Auto)
YearToDate Integer (Auto)
MonthToDate Integer (Auto)
MissionaryLifetime Integer (Auto)
MissionaryYearToDate Integer (Auto)


tblMyChurch (we use the church name but for obvious reasons…)
CongregantNum Primary Key Long Integer
LastName Text 35
FirstName Text 20
MiddleName Text 15
Email Text 50
ResidencePhone Text 12
OfficePhone Text 12
Address Text 35
Address1 Text 35
City Text 20
State Text 2
Zip Text 10
Country Text 25
Salutation Text 10
Position Text 15
Employer Text 35
Fellowships Memo
SpecialInterests Memo
Offspring Text 150
Siblings Text 150
DateBaptized Short Date
DateJoined Short Date
Loginname Text 10
Password Text 10


The members form has 4 tabs, Member, Missionary History, Retreats, Accounting

Over time we expect to have many additions to PledgeHistory and MissionaryHistory, we only have a little over 100 members but over 25% have gone on missions to Central America mostly Honduras but El Salvador as well. Everyone thinks the Meeting Secretary should always remember the dates and places of their missions but over the last five years there were over 125 missions by 33 different members. It is very helpful when discussing future missions if all members who have been to a city or camp can be identified and e-mailed, etc. Most of us are retired or nearing retirement age and have the time for mission activities unfortunately few young people are joining the Society of Friends.

What we would like is when the members form is opened and the Missionary History tab is viewed the contents of MissionaryHistory (tblCongregantPrivate) will be displayed in an editable form, so new missions can be entered and any corrections that are needed can entered and updated in tblCongregantPrivate.

We’re trying to enter the data in a lose format or date, country, mission ¬ - details and notes

Same with PledgeHistory.


Please let me know if you have further question. Thank you so such for your help.

Ed

 

[fneily]

Can't find MemberNumber you mentioned, in the tables. Maybe you meant CongregantNum.
Don't know what you mean by:
LifetimeTotal
YearToDate
MonthToDate
MissionaryLifetime
MissionaryYearToDate
Are they Pledge amounts(currency)? or Mission Counts?

One of your tabs says Retreats. Where is that info kept?
Another says accounting. Where's the currency kept?

Ok. This is what I see: Members who go on missions. This means to a city or camp. Members also donate money. You want to obviously track donations and where members went. Now a member can go on several missions and a single mission can have several members. This is a many-to-many relationship. See the article reference. You can't have this in Access so you need to build a junction table between the two tables.

I would have the following tables:
your tblMyChurch and -
tblMission
MissionID Primary Key
Description (this is the city or camp name)

tblPledges
PledgeID Primary Key
CongregantNum
DatePledged
Amount
PledgeNotes (memo field)

tblMissionHistory
MHID Primary Key
CongregantNum
MissionID
StartDate
EndDate
MissionNotes (memo Field)

So there's a one-many relationship between tblMyChurch and tblPledges.

For missions, tblMyChurch connects to tblMissionHistory that connects to tblMission.
Now you have one-many relationships between tblMyChurch and tblMissionHistory and a one-many relationship between tblMission and tblMisshionHistory.

Now pleges and missions are all tracked.
Stuff like YearToDate, MonthToDate, Lifetime are calulated through queries. They are not stored in tables.

To fill in tblMissionHistory, the CongregantNum and MissionID form controls can be comboboxes.

 

[sunwindandsea]

Can't find MemberNumber you mentioned, in the tables. Maybe you meant CongregantNum.
Sorry MemberNumber was in an earlier version.

Don't know what you mean by:
LifetimeTotal
YearToDate
MonthToDate
MissionaryLifetime
MissionaryYearToDate
They Pledge amounts(Integer 2 decimals places, maintained on the Accounting tab)?

One of your tabs says Retreats.

The committee decided that Missionary History was more descriptive than Retreats (where they were going to keep Missions and Retreats). Now, were only keeping Missions.

I would like to keep this very simple.

I think that in actual use many notes having nothing to do with pledges or missions will be added to the MissionaryHistory and PledgeHistory memo fields. In short, adding those additional tables may be more confusing to the Meeting Secretary and her helper. She just wants to look a record, click on the e-Mail icon, paste the message and click Send in Outlook. I think she believes that looking at each member’s missions and Pledge History she’ll come to remember them better. The Pledge History is hopefully just date and note on what was said, i.e., “I’m applying for a grant from the Yearly Meeting or the Templeton Foundation” or call so and so about obtaining an application for a grant or so and so or so will pay ½ of the air fare to… You get the idea, perhaps the name we gave the fields was not the most descriptive of the expected contents.

We simply want to have the contents of a memo field from two tables displayed in an editable form so that the contents of each field can be updated in each table.

I was trying DLookup(“MissionaryHistory”, “tblCongregantPrivate”, “CongregantNum = “& Forms![ MissionaryHistory]! CongregantNum) but I can’t seem to get it to work.

Thanks for you help,

Ed

 

[sunwindandsea]

Update I now have DLookup displaying the contents of a fields from the private table. Now how can I update the record with any changes? I'm working on that any suggestion?


You have really stuck with this, fneily. An I thank you from the every field in both tables.

Ed

 

[sunwindandsea]

This is the code that's working. I cannot edit the field so I can't update the table.

=DLookup("MissionHistory","CongregantPrivate","CongregantNum = " & Forms![Members]!CongregantNum)

 

[fneily]

You may want to see: (looking through the FAQ's or searching the forums can find you alot of info)
faq702-4398
faq702-3702
faq702-2039

Or:
Create form with a combobox based on tblMyChruch showing the CongregantNum and name. Save the form.

Create a query based on the fields of tblCongregantPrivate. Under the CongregantNum field on the criteria line put:
Form![YourFormNameOfCombobox]![Comboboxname]

Create a form based on the above query. Save the form.

On the AfterUPdate event of the Combobox on the first form put: DoCmd.OpenForm "NameOfSecondForm"


"adding those additional tables may be more confusing to the Meeting Secretary and her helper.". Sorry, don't agree. They should not know anything about tables. They should only be working through forms.

Pledges and Missions are two distinct tables.

You are trying to bend Access and me to do it your way. I spent 15 years (3 to 18 years old) at a Quaker school. I know they can be stubborn. But this is Access, not religion. So just redesign the correct way. Your design will cause problems later.

 

[sunwindandsea]

Essentially, what these fields have come to be are two fields where any bit of information vaguely relating to pledges or missions is to be entered. There was a discussion about having more fields and separate table but by the time everyone had their say, I surrendered. I can't help it.

I'm sorry. I've spent the entire weekend on two fields and I am in the same place I was Friday night.

Using the Wizard no memo fields were displayed. Will a Combobox work with memo fields?

Create form with a Combobox based on tblMyChruch showing the CongregantNum and name. Save the form. Can this combo box be placed on a tab within the form where all the other data is displayed? What do you mean by "name"?

Thanks for the time and trouble we've been or I've been.

Ed

 

[fneily]

You can't use memo fields in comboboxes. They truncate to 255 characters. Makes sense since memo fields can be 65+K.

Name - FirstName, LastName. You're not going to memorize everyone's congregantNum.
Combobox can be anywhere you want. Did you read the references?

Design by consensus? No. Collect data by consensus. Design by the rules of Access. Anyway, you should never listen to people who are giving design suggestions who know nothing of normalization.

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

http://support.microsoft.com/kb/148581

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

 

[sunwindandsea]

This line in the Record Source for the Form solved the problem:

SELECT Fellows.*, Fellows_Contacts.* FROM Fellows INNER JOIN Fellows_Contacts ON Fellows.MemberNumber=Fellows_Contacts.MemberNumber;

I want to thank everyone who replied especially fneily who very patient while becoming very frustrated with me. Thanks