Multiple member List (1 Viewer)

[BlueSpruce]

are you sure that the Denomination is Christian?
Christian don't allow multiple marriage when his/her partner is still alive.
Muslim can have 4 wifes the max.

anyway, there are two queries on the demo db.
qryMarriedMen only list all men that are married.
qryHusbandAndWives is the one you need (hopefully).

Perhaps the OP means that a member can only have one spouse at a time and he wants to track prior spouses with a history table?

 

[LarryE]

Perhaps the OP means that a member can only have one spouse at a time and he wants to track prior spouses with a history table?

That's what I was thinking but it's just a guess.

 

[BlueSpruce]

That's what I was thinking but it's just a guess.

Your main goal is to send wedding anniversary cards, and other letters to currently married Christian couples, so is there a need to track prior marriages?

 

[LarryE]

Your main goal is to send wedding anniversary cards, and other letters to currently married Christian couples, so is there a need to track prior marriages?

People die and get divoced so in my opinion you need to track partnership activity. Members can get remarried.

 

[BlueSpruce]

People die and get divoced so in my opinion you need to track partnership activity. Members can get remarried.

Agreed, but the OP is not sending divorce anniversary cards. However, he might want to send death anniversary letters to widowers who have not remarried, so the married couples record needs to have a current status field for each spouse and a status change date field. Some persons might also want to opt out from receiving any communications.

 

[Ken Sheridan]

Perhaps the OP means that a member can only have one spouse at a time and he wants to track prior spouses with a history table?

A History table is unnecessary, it's inherent in the model:

SELECT
    M1.FirstName & " " & M1.LastName AS Husband,
    M2.FirstName & " " & M2.LastName AS Wife,
    WeddingDate
FROM
    Members AS M2
    INNER JOIN (
        Members AS M1
        INNER JOIN Marriages ON M1.MemberID = Marriages.HusbandID
    ) ON M2.MemberID = Marriages.WifeID
    ORDER BY
        M1.LastName,
        M1.FirstName,
        M1.MemberID,
        WeddingDate DESC;

The instances of the Members table can of course be transposed to return the 'history' from each wife's perspective. Note the inclusion of the MemberID column in the ORDER BY clause to ensure that two or more members of the same name sort correctly.

 

[GPGeorge]

A History table is unnecessary, it's inherent in the model:

...

In my own family tracking database, I've wrestled with the appropriate terminology for roles and relationships for the people involved in households and families.

Traditionally, we have talked about marriages consisting of husband and wife and families consisting of spouses and children. However, I know of more than one household which consists of two unmarried partners, where neither of those partners think of themselves as a husband or as a wife. They still maintain a family or household relationship. And still others where there are two men or two women in a well established household.

Moreover, I am also aware of households consisting of one or two grandparents and one or more grandchildren, with no one of the intervening generation present in that household.

Those households are every bit as valid as those involving a married couple of man and woman and children. And tracking them in a family database requires recognizing that fact.

I think it all hangs together better when using more general terms, albeit the logical structure is very much the same.

 

[BlueSpruce]

I am trying to make a Wedding Anniversary list from which I create Offertory envelops etc.

Wedding Anniversaries are for currently married couples. Divorced, Separated, and Widowed persons do not qualify for the OP's list. Do the couples have to be married by the Christian Church to qualify for the list?

 

[LarryE]

Wedding Anniversaries are for currently married couples. Divorced, Separated, and Widowed persons do not qualify for the OP's list. Do the couples have to be married by the Christian Church to qualify for the list?

The Prayer Groups are Christian, so I would say probably, yes, however the ACCESS model used doesn't care about religious denomination.

 

[BlueSpruce]

The Prayer Groups are Christian, so I would say probably, yes, however the ACCESS model used doesn't care about religious denomination.

So then it's up to the OP to determine if he wants to add a couple to the list. If more than one married couple lives in the same household, then a master household table linked to a detail couples table is needed.

 

[LarryE]

Hello to All!
To be honest am a beginner trying to learn and work with Access DB's. Am facing a problem with my Access database of members which I build for a Christian Prayer Fellowship. I am trying to make a Wedding Anniversary list from which I create Offertory envelops etc.
My DB has MemberID, MemberName, Gender, DateOfBirth, DateOfMarriage, PrayerGroup, FamilyName etc. Sometimes in a family there could be couple of members whose DateofMarriage falls on the same date. For.Eg. A married with B, C married with D, E married with F. So when I create a list the result comes like this A >B, A>D, A> F & C>D, C>F, C>B & E>F, E>B, E<D means each member will have 3 entries with 3 different wives. Out there could anyone help me to resolve this issue please. Either VBA or SQL Query.

@ktgmathew:
It's my understanding that you wish to keep track of:

1. Family Last Names
2. Prayer Groups
3. The members who belong to those Family Names and Prayer Groups
4. Who each member's married partner currently is
5. Their marriage Anniversary Date

For the purpose of printing a report with members names and anniversary date.

Is this assumption correct? All of us are just making guesses at what you want to keep track of and what the final product is supposed to be.

 

[Ken Sheridan]

It seems to me that the OP already has the table he needs for recording the membership of the prayer groups in a manner satisfactory to his preferences. It would benefit from some minor improvements such as the splitting of the member's name into FirstName and LastName columns, and the creation of a referenced PrayerGroups table, but what he is having trouble with is how to to model the Marriages relationship type between members. MajP and I gave him a solution in our first replies, which would enable current marriages only, or multiple marriages per member over time to be recorded as he sees fit. The only difference would be whether the marriage date is part of a candidate key or not.