Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-28-2015, 05:53 AM   #1
accessprogramer
Newly Registered User
 
Join Date: Jan 2014
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
accessprogramer is on a distinguished road
Primary Keys

I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... Any suggestions? My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...

*Child_ID
Parent_First_Name
*Primary/Secondary/Other

accessprogramer is offline   Reply With Quote
Old 05-28-2015, 06:19 AM   #2
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Primary Keys

what tables do you have?

you only really need one table for children AND parents, you know, to do this.
You may need a second table for parenthood, but you may be able to do this equally well with just 3 extra fields in the table

one for motherID, one for fatherID, (and one for main parent if you like - I mean a mother/father main parent indicator of some sort).

all this assumes a child can only have 1 mother and 1 father! if you need to track guardians, carers, adoptive parents as well as natural parents it's a bit trickier.


a child's parents are easy
a parent's children are those children referenced by the motherID and/or fatherID.


[edit. actually you OUGHT to do it the way I suggested. Parenthood is a naturally recursive relationship. A grandparent is just a parent's parent. A family tree can easily be traced with such a table. It may be relevant at some point]


sort of these fields

person table

id
person name
motherID (id of another record in this table)
fatherID (id of another record in this table)
maincarerflag
other info you need
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.

Last edited by gemma-the-husky; 05-28-2015 at 06:29 AM.
gemma-the-husky is offline   Reply With Quote
Old 05-28-2015, 06:43 AM   #3
ButtonMoon
Newly Registered User
 
Join Date: Jun 2012
Posts: 304
Thanks: 11
Thanked 56 Times in 55 Posts
ButtonMoon will become famous soon enough
Re: Primary Keys

You can enforce this kind of rule using a CHECK constraint rather than a uniqueness constraint. For example:

ALTER TABLE YourTable
ADD CONSTRAINT primary_secondary_parents_must_be_unique
CHECK (NOT EXISTS
(SELECT 1 FROM YourTable WHERE Type <> 'Other'
GROUP BY ChildId, Type HAVING COUNT(*)>1));


Note: unfortunately you can't execute this SQL in a query window, you have to use the VBA Execute method instead.

ButtonMoon is offline   Reply With Quote
Old 06-02-2015, 04:32 AM   #4
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: Primary Keys

I believe you need a table for tblChildren.

This table has a field linked to a second table tblParents.

This is a simple one to many relationship. You can have one or more parents.

Perhaps you are looking for something more advanced but this is what I believe you need based upon the information you have given.

I have no idea where Buton Moon is coming from. He may have got his posts mixed up.
__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,

RainLover is offline   Reply With Quote
Old 06-02-2015, 10:17 AM   #5
ButtonMoon
Newly Registered User
 
Join Date: Jun 2012
Posts: 304
Thanks: 11
Thanked 56 Times in 55 Posts
ButtonMoon will become famous soon enough
Re: Primary Keys

The question actually seemed pretty clear to me but I am the only person who has answered it:

Quote:
I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... Any suggestions?
ButtonMoon is offline   Reply With Quote
Old 06-03-2015, 03:29 AM   #6
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: Primary Keys

Quote:
I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other
You can't have the one field as Primary part of the time. It is either Primary or it is not.

Suggest that this field not be used as the Primary. I am sure you have some good reasons behind this. Could you please explain where you are wanting to go and why.

If you can't find a different field that is suitable for use as Primary then you should consider using Auto Number.

If you understand Button's code then you could use it. Otherwise you can make it an index and not allow duplicates. This is done in the properties of the table.

One final point is that if you use more than one field for a Parent then this is a violation of Normalisation.
__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,

RainLover is offline   Reply With Quote
Old 06-03-2015, 03:34 AM   #7
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Primary Keys

I don't think he means primary as in PK.

He just wants that given a child with two parents, he wants to be able to designate one (and only one) of the parents as having "primary care responsibility". That's what I think he is after.

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 06-03-2015, 03:44 AM   #8
ButtonMoon
Newly Registered User
 
Join Date: Jun 2012
Posts: 304
Thanks: 11
Thanked 56 Times in 55 Posts
ButtonMoon will become famous soon enough
Re: Primary Keys

Quote:
If you understand Button's code then you could use it. Otherwise you can make it an index and not allow duplicates.
The OP explained in his initial post that a uniqueness constraint (whether primary key or unique index) doesn't help because he wants to allow more than one parent of type "Other".

Quote:
if you use more than one field for a Parent then this is a violation of Normalisation
That's not true. Having two (non-nullable) parent attributes is not a violation of any Normal Form. I don't think that is what is being proposed here in any case.
ButtonMoon is offline   Reply With Quote
Old 06-03-2015, 05:24 AM   #9
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: Primary Keys

Quote:
Originally Posted by gemma-the-husky View Post
I don't think he means primary as in PK.

He just wants that given a child with two parents, he wants to be able to designate one (and only one) of the parents as having "primary care responsibility". That's what I think he is after.
Thanks Dave.

My advise would still stand would it not. One Field for all parents just add a descriptor Field as Mother, Father etc.

__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,

RainLover is offline   Reply With Quote
Reply

Tags
primary key , reports

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Square Keys, Round Keys, Yellow Keys, Purple Keys, Multiple Location Keys Thales750 Theory and practice of database design 10 11-23-2012 01:23 PM
Help, need to update table with 2 foreign keys with autonumber from primary keys. meelos Tables 3 11-23-2009 07:05 AM
primary keys jacks Tables 7 06-28-2007 04:13 AM
Primary Keys Jeanette Tables 2 04-07-2003 01:31 PM
primary keys! badangel Tables 4 02-10-2003 08:16 AM




All times are GMT -8. The time now is 08:57 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World