The Living & The Dead (1 Viewer)

[WuWu]

I am coming back to DB design after almost 20 years away and have a challenging conundrum.

For our Temple, we have a long contact list. Some contacts are alive, others have passed on.

We have a Columbarium where people buy niches when alive and designate whose ashes are to be kept there. Often these individuals are still active members, but eventually they come to take their spot.

At the same time, we have monthly memorial services for all those who passed away in a given month. Each memorial has at least one or two contacts that are notified of the upcoming event. These contacts also become subjects of memorials as the time goes on.

The many-to-many relationship of the niches was not a problem as I can indicate who purchased, who passed away and who was inurned from the related tables.

For the memorials I can have any number of members be the contact for the memorial of a deceased member, and cease to be so once they pass away themselves. I amt trying to wrap my head around how I can
1. Look up a name of a deceased member and see their contacts
2. Run a report that shows all the passed members who are being commemorated that month, along with their contacts (which could be listed in more than one instance)

I tried looking into groupings, nested tables, queries and passed values, but I am stuck.

Any suggestions for how to structure this and where to look for samples or videos would be greatly appreciated.

 

[The_Doc_Man]

I would have to know a bit more about the contact list. Is it always limited to members (or former members)? If I make the assumption that your contacts are only drawn from your members then...

Problem 1 is a SELF-JUNCTION where you link a member to another member through a "contacts" table. In Access when you design this, you have a contact "Person 1" and "Person 2" field. Then your JOIN for person 1 is in your members table. You can also JOIN person 2 through your members table as well. You can make this a reciprocal relationship. That is, if person 1 is a contact to person 2 then person 2 is also a contact to person 1. To have a double-ended relationship, you can add the members table to the relationship diagram a second time, in which case Access will show the 2nd instance as something like "Members(2)" - but that's OK, it is the same table operating under an alias.

Then your query list might be similar (conceptually) to this:

SELECT M1.MbrID, M1.FName, M1.MName, M1.LName, M2.FName, M2.MName, M2.LName 
FROM  ( Members M1 INNER JOIN Contacts ON M1.MbrID = Contacts.MbrID1 ) INNER JOIN Members(2) M2 ON M2.MbrID = Contacts.MbrID2 
WHERE M2.Deceased = False
UNION
SELECT M2.MbrID, M2.FName, M2.MName, M2.LName, M1.FName, M1.MName,M1.LName
FROM ( Members(2) M2 INNER JOIN Contacts ON M2.MbrID = Contacts.MbrID2 ) INNER JOIN Members M1 ON M1.MbrID = Contacts.MbrID1 
WHERE M1.Deceased = False ;

Problem 2 would use the first query, give it a name like qryContacts, as a data source. Then you simply do

SELECT MbrID, FName, MName, LName FROM qryContacts WHERE MbrID = <ID of the memorialized member>;

There is the slightly bulkier but easier approach in which if you make a relation between two members, you make a contact entry TWICE, the second one with the two IDs reversed, in which case you don't need the UNION or 2nd half of that query.

 

[WuWu]

Thanks - starting to wrap my head around this -
The contact list also contains donors that are not members, but I can sort that by having a y/n member field.
The challenge is that there could be several contacts attached to one of the deceased, and each living member could be a contact for more than one deceased.
I figured I would need some kid of Intermediary join table - could call this table 'funerals' then link all the individuals to the event, and report the connections from there - identify the deceased person with a T/F statement. I am thinking to add data on date of death and funeral info, etc to the join table - that would save space as I would not need all the fields that only apply to deceased members to all records in the contact database.

1. would list all funerals and attached people - that seems like the easier part
2. the Report that should be sorted by month, then deceased contact and then the people connected to that contact. Can I group by funeral ID, but sort by alpha based on the only deceased member in that group? How?

 

[deletedT]

Being addicted to movies, reading the title of the question, I thought it's a talk about this.

 

[The_Doc_Man]

Can I group by funeral ID, but sort by alpha based on the only deceased member in that group? How?

Yes, of course you can. But when you say "funeral ID" is that a separately recorded thing? There, you can be more straight-forward. After all, a person only dies once. So in the member table, you can have a 'deceased' flag. And you can build a funeral table that can have its own funeral ID if needed, but the "guest of honor" slot would simply be a pointer to the appropriate member in the member table.

If you want to sort by month, then a simple rule of Access: Access won't tell you anything you didn't tell it first. SO you have to have a funeral date in order to sort by funeral month. If you have relationships defined to represent these associations, then your query wizard will recognize and automatically (or automagically) take the relationships into account when building the queries.

The syntax of the question makes me wonder WHO is being sorted - but nonetheless, it would be possible to sort by the name fields if you wish. If you used the junction table as two-way association, my earlier UNION would give you the contacts. OR if you take the approach of always creating two contact entries that says A is a contact of B and a separate entry that says B is a contact of A, then this becomes simple. To find the contacts of the deceased, you just find all entries in the contacts table for which MbrID1 is that of the deceased - which you know from the table that lists funerals and points to the deceased's member record.

 

[WuWu]

I would have to know a bit more about the contact list. Is it always limited to members (or former members)? If I make the assumption that your contacts are only drawn from your members then...

Problem 1 is a SELF-JUNCTION where you link a member to another member through a "contacts" table. In Access when you design this, you have a contact "Person 1" and "Person 2" field. Then your JOIN for person 1 is in your members table. You can also JOIN person 2 through your members table as well. You can make this a reciprocal relationship. That is, if person 1 is a contact to person 2 then person 2 is also a contact to person 1. To have a double-ended relationship, you can add the members table to the relationship diagram a second time, in which case Access will show the 2nd instance as something like "Members(2)" - but that's OK, it is the same table operating under an alias.

Then your query list might be similar (conceptually) to this:

SELECT M1.MbrID, M1.FName, M1.MName, M1.LName, M2.FName, M2.MName, M2.LName
FROM  ( Members M1 INNER JOIN Contacts ON M1.MbrID = Contacts.MbrID1 ) INNER JOIN Members(2) M2 ON M2.MbrID = Contacts.MbrID2
WHERE M2.Deceased = False
UNION
SELECT M2.MbrID, M2.FName, M2.MName, M2.LName, M1.FName, M1.MName,M1.LName
FROM ( Members(2) M2 INNER JOIN Contacts ON M2.MbrID = Contacts.MbrID2 ) INNER JOIN Members M1 ON M1.MbrID = Contacts.MbrID1
WHERE M1.Deceased = False ;

Problem 2 would use the first query, give it a name like qryContacts, as a data source. Then you simply do

SELECT MbrID, FName, MName, LName FROM qryContacts WHERE MbrID = <ID of the memorialized member>;

There is the slightly bulkier but easier approach in which if you make a relation between two members, you make a contact entry TWICE, the second one with the two IDs reversed, in which case you don't need the UNION or 2nd half of that query.

Thanks for the leads and suggestions!

I worked out most of the problems, but now have an issue with a query where I used two instances of the 'Contacts' table. Visually it represented these as Contacts and Contacts_1, and I was able to make the joins and got all the data working when I connected the tables.

The problem is now, when I add NEW contacts to the 'Contacts' table, they show up fine there, and I can add them to the various tables, but the query that is based on the two instances of the same table does not seem to refresh or recognize the new entries to 'Contacts' as part of the alias 'Contacts_1', and all new items do not show up.

Is there some way of refreshing that Alias in Memory so the structure of the query still works?

Here is the SQL for you if that helps!

SELECT Memorials.ID, MonthName([MOD],False) AS [Month], Contacts.[First Name], Contacts.ID, Contacts.[Last Name], Memorials.FuneralDate, Memorials.Homyo, Memorials.Shotsuki, Contacts_1.[First Name], Contacts_1.[Last Name], Contacts_1.ID, Contacts_1.Alive, Contacts_1.Add1, Contacts_1.Tel, Contacts_1.Cell, Contacts_1.Email, Memorials.[MOD], Memorials.DOD, Memorials.YOD, LT_Host.Type, LT_Funeral.Location, Memorials.MC, Memorials.Note, MemContacts.Contact
FROM LT_RelationshipTypes RIGHT JOIN (LT_Host RIGHT JOIN (LT_Funeral RIGHT JOIN (Contacts INNER JOIN (Memorials INNER JOIN (Contacts AS Contacts_1 INNER JOIN MemContacts ON Contacts_1.ID = MemContacts.Contact) ON Memorials.ID = MemContacts.Mem) ON Contacts.ID = Memorials.Celebrant) ON LT_Funeral.ID = Memorials.FuneralID) ON LT_Host.ID = Memorials.HostID) ON LT_RelationshipTypes.ID = MemContacts.RelationshipID

I would really appreciate it if you had a solution, this 'little' volunteer project has taken on a life of it's own!

 

[The_Doc_Man]

What you described would be the behavior of a separate table, not a true alias. Was this next query supposed to show you something along the lines of your question?

SELECT
    Memorials.ID,
    MonthName([MOD],False) AS [Month],
    Contacts.[First Name],
    Contacts.ID,
    Contacts.[Last Name],
    Memorials.FuneralDate,
    Memorials.Homyo,
    Memorials.Shotsuki,
    Contacts_1.[First Name],
    Contacts_1.[Last Name],
    Contacts_1.ID,
    Contacts_1.Alive,
    Contacts_1.Add1,
    Contacts_1.Tel,
    Contacts_1.Cell,
    Contacts_1.Email,
    Memorials.[MOD],
    Memorials.DOD,
    Memorials.YOD,
    LT_Host.Type,
    LT_Funeral.Location,
    Memorials.MC,
    Memorials.Note,
    MemContacts.Contact
FROM
    LT_RelationshipTypes RIGHT JOIN
        (LT_Host RIGHT JOIN
            (LT_Funeral RIGHT JOIN
                (Contacts INNER JOIN
                    (Memorials INNER JOIN
                        (Contacts AS Contacts_1 INNER JOIN
                            MemContacts ON Contacts_1.ID = MemContacts.Contact)
                        ON Memorials.ID = MemContacts.Mem)
                    ON Contacts.ID = Memorials.Celebrant)
                ON LT_Funeral.ID = Memorials.FuneralID)
            ON LT_Host.ID = Memorials.HostID)
        ON LT_RelationshipTypes.ID = MemContacts.RelationshipID

I don't understand your problem. You have taken this in a direction I don't quite follow.