Multiple member List (1 Viewer)

[ktgmathew]

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.

 

[DHookom]

Consider storing a spouse ID. Dates are rarely reliable as unique.

 

[Ken Sheridan]

In relational database terms marriage is a relationship between two entities of, in your case, type Member. To accommodate attributes such as WeddingDate the relationship can be modelled by a second table along the following lines:

Marriages
....HusbandID (FK)
....WifeID (FK)
....WeddingDate

You can then return a list of all weddings with a query which joins two instances of the Members table and the Marriages table:

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;

For returning the upcoming wedding anniversary dates you might find the functions in the attached demo file useful. As you'll see it includes examples to return the last and next birthdays, and birthdays within n days.

PS: You can of course drop the DateOfMarriage column from the members table. This is an attribute of the Marriages relationship type, not of members.

Edited: In my original draft I referred in the first paragraph to a third table. I should have said second table as edited above..

 

[MajP]

I would go with Ken's approach it is far easier. You could do what duane suggests and make a self referencing table but that is confusing unless you have done this before. Both work.

Here is a version of Ken's to show how to do it. Also I modified it to handle an inclusive church where you can can have unions of two husbands, two wives, and who knows what else. However, does not support polygamous unions since that would be a more difficult table structure.

Table Members

The yellow fields are only used in the self referencing approach. These would not exist in the simpler approach
I put a role to handle same sex unions. If not an issue you can alias like Ken.

Ken's Approach (recommended for simplicity)
1. Requires a Marriage linking table.

you can see that the first record is a key to charlie and alice Brown
You would have a form with two comboboxes that allows you to pick Spouse 1 and Spouse 2.
You would also want some indexes and code so that you cannot assign the same person to more than one marriage. Also may want to ensure that if you have 1, 2 you do not add a record for 2,1. Not a big deal but you will show a record for Charile and Alice, and Alice and Charlie. That would be confusing.
2. Now link them as follows.

3. That looks confusing and hard to work with so use the properties to add an alias to the two tables and then the columns

4. The results

5. Using the same query above you can make it more readable with some concatenation and date functions

 

[CJ_London]

You might need a field to allow for annulled marriages

 

[MajP]

Self Referencing Approach
If you wanted to do a self referencing table then keep the yellow fields

You create your query by dropping the member table twice. Again alias it for ease of use

2. Create Query. This is a little more involved so that you do not get 1,2 and 2,1 for the same marriage. See the criteria

3. Results

Self referencing tables can be very useful but takes some time to understand how to work with them. If interested

Hierarchical Data, Self Referencing Tables, and Recursion Part 2

This is part two to this thread. https://www.access-programmers.co.uk/forums/threads/hierarchical-data-recursion-tree-views-and-a-custom-class-to-assist.309753/ However the original thread dealt more specifically with tree views. This does not. The above threads has many links to other treeview...

www.access-programmers.co.uk

 

[XPS35]

Can it happen that only one of the spouses is a member?

 

[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.

It sounds like:

1. Each Prayer Group has multiple members
2. Each Member may have multiple Partners (each with a Marriage Date)

So, if that is the case, then a table and relationship design like this may be appropriate:

With this design a member could be either gender with a partner of either gender but with a single Marriage Date.
Attached is the file with the tables and relationships defined.

 

[MajP]

1. Each Member may have multiple Partners (each with a Marriage Date)

So let me see if I understand this. You interpret the OP to be at some kind of Mormon compound where members are married to multiple people? I did not see that coming.

Even if so why would you have a Partner table separate from the members table? Is your assumption Partners are not members? Even if that was the case why would you not have a single People table with a boolean Member field? For sure if Partners are also Members then you have violated normalization since you are duplicating data in two tables.

 

[LarryE]

So let me see if I understand this. You interpret the OP to be at some kind of Mormon compound where members are married to multiple people? I did not see that coming.

Even if so why would you have a Partner table separate from the members table? Is your assumption Partners are not members? Even if that was the case why would you not have a single People table with a boolean Member field? For sure if Partners are also Members then you have violated normalization since you are duplicating data in two tables.

No. That is not what I meant. Each "member" could have multiple partners over time or not. Maybe a partner (husband or wife) died or got divorced and the member married a new partner. I don't know if the OP would consider any partner to also be a "member" or not. Maybe he does or maybe he doesn't. A "member" is undefined by the OP. I am leaving what a "partner" is up to the OP. The OP already indicated that each "member" might have multiple wives, but I assume not at the same time.

I do admit I have not a clue what the OP means when he wrote this:

"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"

 

[MajP]

Each "member" could have multiple partners over time or not.

Ken's "Mariages table" or "tblUnions" as I called it would still be a clearner way to do that and still maintain a history of marriages.
In mine
--Spouse1
--Spouse2
--MarriageDate
--Status (Current, Annulled, Deceased spouse)

In fact you will have to put some code and or indices so that the couple only can have 1 current marriage.

"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"

My guess is that they did a cartesian join by accident and instead of 1 record for each couple they got 3 records for each.

But what I really cannot guess at is

Sometimes in a family there could be couple of members whose DateofMarriage falls on the same date.

The only thing I could think is that me and my wife could have the same wedding day and month as my son and his wife. That is possible but for this to happen "sometimes" seems like a pretty rare thing.

 

[LarryE]

Ken's "Mariages table" or "tblUnions" as I called it would still be a clearner way to do that and still maintain a history of marriages.
In mine
--Spouse1
--Spouse2
--MarriageDate
--Status (Current, Annulled, Deceased spouse)

In fact you will have to put some code and or indices so that the couple only can have 1 current marriage.


My guess is that they did a cartesian join by accident and instead of 1 record for each couple they got 3 records for each.

But what I really cannot guess at is

The only thing I could think is that me and my wife could have the same wedding day and month as my son and his wife. That is possible but for this to happen "sometimes" seems like a pretty rare thing.

If every partner is also a member, then you could do a design like:

But knowing nothing further about the project, we are mostly making assumptions which may or may not be true. The OP stated they were just beginners, so I went with a very simple design. I do think the OP should include accommodation for relationship status and dates of status change. Status examples might be "Marriage", "Divorce", "Member Deceased" or others as required.