Self Relationship

[TheLazyAce]

I want to create a self relationship to track parnets of Goats. each Mother
Goat has a tag and tag is the key field. An example of the tag could be
Yellow13 or Black4. As a mother has babies, I place a tag on baby, I want to
enter the baby in the Table with the new tag, and be able to do a lookup of
all tags already in the table.

Example: Mother Tag ID Yellow13. Mother has two babies. Baby 1 I tag with
Black14, as the other information is entered, I wish to be able to do a
lookup to pick the correct Mother ID tag.

this down the road will allow me to determine how may babies each mother has
etc...

I am very new to Access, I have programmed in the past using filemaker Pro
and dong this is very easy. I am wanting to switch to Access for all of my
Database needs so if some will help this newbe I would greatly in debt.

 

[tina]

yes, you should be able to do that. since babies (girl babies, anyway!) may
eventually become mothers themselves, suggest just one table, as

tblGoats
TagID (primary key)
GoatName (do you name your goats? <g>)
Gender ("m" = male, "f" = female)
DOB (date of birth)
MotherTagID
<other fields that describe a goat>

make sure that the TagID and MotherTagID fields both have the same data
type. also make sure that the Index property of field MotherTagID is set to
"duplicates ok". in the Relationships window, add tblGoats. then add it
again; the second table will show up as tblGoats_1. add a link *from*
tblGoats_1.TagID *to* tblGoats.MotherTagID

create a form bound to tblGoats. in form Design view, bind field MotherTagID
to a combo box control. set the control's RowSource property to
SELECT TagID FROM tblGoats WHERE Gender = "f" ORDER BY TagID;

now you can add a goat record in the form, and select the goat's mother from
the droplist of female goats already listed in the table. you can run Totals
queries on field MotherTagID to count the number of babies for each mother,
etc.

hth

 

[TheLazyAce]

yes, you should be able to do that. since babies (girl babies, anyway!) may
eventually become mothers themselves, suggest just one table, as

tblGoats
TagID (primary key)
GoatName (do you name your goats? <g>)
Gender ("m" = male, "f" = female)
DOB (date of birth)
MotherTagID
<other fields that describe a goat>

make sure that the TagID and MotherTagID fields both have the same data
type. also make sure that the Index property of field MotherTagID is set to
"duplicates ok". in the Relationships window, add tblGoats. then add it
again; the second table will show up as tblGoats_1. add a link *from*
tblGoats_1.TagID *to* tblGoats.MotherTagID

create a form bound to tblGoats. in form Design view, bind field MotherTagID
to a combo box control. set the control's RowSource property to
SELECT TagID FROM tblGoats WHERE Gender = "f" ORDER BY TagID;

This may have been my problem, the form end. I have done the above several
times along with attempting to using Queries... to no avail (the table and
table 1)I will give this a try. thank you and I will see what happens.

 

[TheLazyAce]

yes, you should be able to do that. since babies (girl babies, anyway!) may
eventually become mothers themselves, suggest just one table, as

tblGoats
TagID (primary key)
GoatName (do you name your goats? <g>)
Gender ("m" = male, "f" = female)
DOB (date of birth)
MotherTagID
<other fields that describe a goat>

make sure that the TagID and MotherTagID fields both have the same data
type. also make sure that the Index property of field MotherTagID is set to
"duplicates ok". in the Relationships window, add tblGoats. then add it
again; the second table will show up as tblGoats_1. add a link *from*
tblGoats_1.TagID *to* tblGoats.MotherTagID

create a form bound to tblGoats. in form Design view, bind field MotherTagID
to a combo box control. set the control's RowSource property to
SELECT TagID FROM tblGoats WHERE Gender = "f" ORDER BY TagID;

I now have a self relationship. I am not sure what I have done in all of my
tries. creating the form made a big difference. I went into the properties
and just had to set the RowSource to tbName and the RowSourceType to
Table/query. I had already done the 2 tables and linked the key fields.
Before the post I had also made an attempt to do a lookup with the lookup
wizard, and I choose to use the manual way to setting it up.

Your last instructions is where my next problem ws going to be. I am afraid
if I continue on i will be spinning my wheels, becasue you are already
hitting the nail on the head. MY POINT.... is your directions "SELECT TagID
FROM tblGoats WHERE Gender = "f" ORDER BY TagID" is this done with a query,
or was this just some drop down box I am missing in the property sheet?

You have put me on the correct path and you understand what I doing, i just
don't want to make the wrong turn.

 

[tina]

the SELECT... statement that i posted is a SQL statement. just put it
directly in the combo box control's RowSource property, as i said. of
course, you'll have to make sure that the field names and the tablename in
the SQL statement are the "real" names used in your "real" table.

and btw, i'm not sure from your post whether or not you actually created a
Lookup field in one of your *tables*. if you did, i strongly recommend that
you get rid of it immediately! for more information, see
http://www.mvps.org/access/lookupfields.htm

hth

 

[TheLazyAce]

the SELECT... statement that i posted is a SQL statement. just put it
directly in the combo box control's RowSource property, as i said. of
course, you'll have to make sure that the field names and the tablename in
the SQL statement are the "real" names used in your "real" table.

and btw, i'm not sure from your post whether or not you actually created a
Lookup field in one of your *tables*. if you did, i strongly recommend that
you get rid of it immediately! for more information, see
http://www.mvps.org/access/lookupfields.htm

hth

Thank you have done a great job. Now I have one small problem, when adding
more then one record I don't get a refresh on the SQL statement. i would not
like to push a refresh button everytime I add a record. Before I make
attempt at my own way of fixing this problem, I would think I could use an
event control maybe on focus refresh.... thoughts

 

[tina]

you're right, you'll need to requery the combobox RowSource. you can use the
form's Current event, or the combo box control's Enter event (i don't often
use GotFocus), adding a simple requery command, as

Me!ComboboxName.Requery

replace ComboboxName with the correct name of the combobox control name, of
course. usually, i use the control's Enter event when some action in the
*current* record may affect the recordset returned to the combobox droplist
(not the situation, in your case); otherwise, i use the form's Current
event.

hth

 

[TheLazyAce]

Thank you for your insight. I will continue on with my quest to learn
Access. this is a huge learning curve compared to my FileMaker Pro knowledge.

Ed