Re: Need help grouping members into families
From: Carolyn McNabb (CarolynMcNabb_at_discussions.microsoft.com)
Date: 01/13/05
- Next message: Carolyn McNabb: "Re: Need help grouping members into families"
- Previous message: John Vinson: "Re: ACCESS AND TABLES"
- In reply to: Brendan Reynolds: "Re: Need help grouping members into families"
- Next in thread: Carolyn McNabb: "Re: Need help grouping members into families"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 12 Jan 2005 22:39:03 -0800
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!
"Brendan Reynolds" wrote:
> 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" <CarolynMcNabb@discussions.microsoft.com> wrote in message
> news:74154658-F867-4712-878A-32648427ED79@microsoft.com...
> > Okay. I still have the issue about data types. If the IDs (MemberID,
> > FamilyID, etc.) are the SQL Server data type uniqueidentifier, what should
> > the foreign keys be (Parent1ID, Parent2ID, etc.) so they can be
> > successfully
> > joined?
> > Looking at the response from John Spencer, I see he says data type Long,
> > but
> > that is not one of the data types listed in the data type column dropdown
> > in
> > Table design.
> > Here they are: bigint, binary, bit, char, datetime, decimal, float, image,
> > int, money, nchar, ntext, numeric, nvarchar, real, smalldatetime,
> > smallint,
> > smallmoney, sql_variant, text, timestamp, tinyint, uniqueidentifier,
> > varbinary, varchar
> >
> > Of course I know the datetime & money ones will not work. Looking at the
> > data created in the uniqueidentifier field I don't know which of the other
> > data types would work since they contain both numbers and letters. SQL
> > Server help did not help with this either.
> >
> > Thank You,
> > Carolyn
> >
> > "PC Datasheet" wrote:
> >
> >> See below ----
> >> "Carolyn McNabb" <CarolynMcNabb@discussions.microsoft.com> wrote in
> >> message
> >> news:CDF9F981-C6CB-4A7B-8AC9-BDEAE98F5FA9@microsoft.com...
> >> > 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
> >>
> >>
> >> >
> >> > 2) If you don't suggest joining the tables, how do I ensure
> >> > referential
> >> > integrity?
> >> >
> >> > Thanks Again,
> >> > Carolyn
> >> >
> >> > "PC Datasheet" wrote:
> >> >
> >> > > 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.
> >> > >
> >> > > --
> >> > > PC Datasheet
> >> > > Your Resource For Help With Access, Excel And Word Applications
> >> > > resource@pcdatasheet.com
> >> > > www.pcdatasheet.com
> >> > >
> >> > >
> >> > > "Carolyn McNabb" <CarolynMcNabb@discussions.microsoft.com> wrote in
> >> message
> >> > > news:06958D1C-973B-45EC-8E09-32E17ED90A47@microsoft.com...
> >> > > > 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
> >> > >
> >> > >
> >> > >
> >>
> >>
> >>
>
>
>
- Next message: Carolyn McNabb: "Re: Need help grouping members into families"
- Previous message: John Vinson: "Re: ACCESS AND TABLES"
- In reply to: Brendan Reynolds: "Re: Need help grouping members into families"
- Next in thread: Carolyn McNabb: "Re: Need help grouping members into families"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
