Need help grouping members into families

[Carolyn McNabb]

Hello,
I am really frustrated. I've been researching how to do this in Access 2003
help, in this community and in the sample Northwinds Access project.
Here's the info:
I'm using MS Access 2003 on Windows XP.
I have set up a project accessing a database in the MSDE I have installed on
my laptop.
At this point I only have one table. I'm trying to figure out how to do the
following:
I am creating a database for my church for membership and for a caregiving
ministry I will be administering. I am stuck at trying to group members into
families...

From what I've researched I thought I'd have one table to accomplish this:
Table Name: Members
Columns:
MemberID (data type = uniqueidentifier)
FirstName
LastName
Address ... (etc.)
FamilyHead (This is what I was trying to use for the ForeignKey)

It doesn't make sense to have FamilyHead as a uniqueidentifer datatype, yet
when I join MemberID and FamilyHead it requires them to be the same data
type. Feeling I was at an impasse, this is when I started doing research,
without much luck.

Then in the Northwinds sample ADP, I saw that they didn't join the Employee
table to itself to make the hierarchy of ReportsTo. They simply used the
following query:
SELECT Employees.EmployeeID, LastName +', ' + FirstName AS ReportsTo FROM
Employees ORDER BY Employees.LastName, Employees.FirstName;

When I tried to do the same thing in my database, LastName +',' + FirstName
does not resolve. I only get the Last Name! :-( I've got to walk away
now...I've been working on this way too long without any progress...

Please Help!
Thank You In Advance,
Carolyn

 

[PC Datasheet]

Family and members of the family are a one-to-many relationship so you need
two tables for this. Then you need a table for members of your church so you
need three tables all together:
TblMember
MemberID
FNmae
LName
etc

TblFamily
FamilyID
FamilyName
Parent1ID 'Record a MemberID here
Parent2ID 'Record a MemberID here

TblFamilyMember
FamilyMemberID
FamilyID
MemberID

Note that the parents are recorded in both TblFamily and TblFamilyMember.

 

[Carolyn McNabb]

Thank you for these suggestions. I have a couple of questions about this
solution.
1) Do I join the tables? If so, how? I have some guesses, but I'd rather
you describe it than for me to write my guesses.

2) If you don't suggest joining the tables, how do I ensure referential
integrity?

Thanks Again,
Carolyn

 

[John Spencer (MVP)]

YOu can do this with one table in a query. You just need to include the table
twice in the query and the MemberID and FamilyHead have to be of the data type.
If MemberID is an autonumber then FamilyHead should be a number of type Long.

You can then create a query that has the Members table in it twice and join the
tables on MemberID (in one table) and FamilyHead (in the second table).

SOMETHING LIKE the UNTESTED SQL statement that follows.

SELECT M.FirstName, M.LastName, F.FirstName, F.LastName
FROM Members as M Inner Join Members as F

 

[PC Datasheet]

See below ----

Thank you for these suggestions. I have a couple of questions about this
solution.
1) Do I join the tables? If so, how? I have some guesses, but I'd rather
you describe it than for me to write my guesses.

Include TblFamily in the relationship window twice. The second time the name
will be TblFamily1
MemberID in TblMember to Parent1ID in TblFamily
MemberID in TblMember to Parent2ID in TblFamily1
FamilyID in TblFamily to FamilyID in TblFamilyMember
MemberID in TblMember to MemberID in TblFamilyMember

 

[Brendan Reynolds]

I'm not an expert on SQL Server, Carolyn, but from my reading of the
'uniqueidentifier data type' topic in SQL Server Books Online, I suspect
that you would probably be better served by an int field with the identity
property set. Check out the topic in Books Online yourself (it's available
on-line at http://msdn.microsoft.com if you don't have it on your hard disk)
and, if you're still in doubt, and/or unless someone else here can give a
more definitive answer than I can, you may want to ask about it in a SQL
Server newsgroup.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Carolyn McNabb]

Thank you so much Brendan. Your advice helped with the issue of how to join
these columns. Using the integer data type and the identify property worked!

 

[Carolyn McNabb]

Hello PC Datasheet,
It took a little while for me to test this out because I ran into some
issues and decided to explore other ways of resolving my original problem.
Well, I'm back to your suggestion and will let you know how it turns out.
Unfortunately I don't have as much time to spend on this as I would like
since I'm homeschooling my 3rd grade daughter and have an 11 month old son
running around!

Thank you for your time, suggestions and detailed responses,
Carolyn