child link to itself

[inungh]

I have a child table which has composite key ID and CID which both
link to parent table like following.

I would like to know is it bizard design like this?

tblParent

ID long integer primary key
MyDate Date Time

tblChild

ID long integer primary key
CID long integer primary key
ChildField Text(50)


Data will be

Parent table
ID MyDate
1 01/20/2008
2 03/15/2007
3 04/01/2008

Child table

ID CID ChildField
1 2 First time
1 3 Second time


Your information is great appreciated,

 

[strive4peace]

Consider doing this:

tblParent
- IDparent, autonumber

tblChild
- CID, autonumber
- IDparent, long, DefaultValue --> null
- CID_, long, DefaultValue --> null

When a table is liked to itself, I like to use its primary key fieldname
with an underscore

When you make the relationship on the relationship diagram, Access will
put another copy of the table on the diagram -- the copy will have a
"_1" on the end of the name

~~~

it was hard to answer your question when you refer to 'parent' and
'child' -- please use the real names of your data so we can give you a
better answer ... I am not really following how you want the
relationships to be set up


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[inungh]

Consider doing this:

tblParent
- IDparent, autonumber

tblChild
- CID, autonumber
- IDparent, long, DefaultValue --> null
- CID_, long, DefaultValue --> null

When a table is liked to itself, I like to use its primary key fieldname
with an underscore

When you make the relationship on the relationship diagram, Access will
put another copy of the table on the diagram -- the copy will have a
"_1" on the end of the name

~~~

it was hard to answer your question when you refer to 'parent' and
'child' -- please use the real names of your data so we can give you a
better answer ... I am not really following how you want the
relationships to be set up

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace

  *
    have an awesome day
  *










- Show quoted text -

Thanks for the information,

 

[strive4peace]

"Thanks for the information"

you're welcome happy to help

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[inungh]

"Thanks for the information"

you're welcome  happy to help

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace

  *
    have an awesome day
  *





- Show quoted text -

I had table to link to itself like supervisor links to employee table
itself. It is my first time that child links to its parent.

I have problem to find records which child does not exists.
Are there any specific way to find child link does not exist?

For exmaple,

Parent table have following data

ID
1
2
3
4

Child has following data

ID ChildID
1 2
1 4


Child ID link to Parent table ID.
Are there any way to find ID 3 in the parent table?

Your help is great appreciated,

 

[strive4peace]

"Are there any specific way to find child link does not exist?"

SELECT Parent.ID
FROM Parent LEFT JOIN Child On Parent.ID=Child.ID
WHERE Child.ID Is Null


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[inungh]

"Are there any specific way to find child link does not exist?"

SELECT Parent.ID
FROM Parent LEFT JOIN Child On Parent.ID=Child.ID
WHERE Child.ID Is Null

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Accesshttp://www.AccessMVP..com/strive4peace

  *
    have an awesome day
  *



inunghwrote:








- Show quoted text -

Thanks for helping,
I beleive my problem is the child links to parent.
Actually, Child is parent of parent table,
In this case , child is parent. am I right?

Thanks again,

 

[inungh]

Thanks for helping,
I beleive my problem is the child links to parent.
Actually, Child is parent of parent table,
In this case , child is parent. am I right?

Thanks again,- Hide quoted text -

- Show quoted text -

Thanks I got this works.
in this case, I have my child as parent then it works now.
Thanks again,

 

[strive4peace]

you're welcome happy to help


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*