Single Field used many times

[dex]

I have what seems like a basic problem I just can't seem to figure out!
I am developing a DB to trap and report on plant breeding data.
I am wanting to store the unique code for each parent involved in any
cross in one field, beside a autonumber field in one table. This code
to be referenced through out the DB as that autonumber.

My problem comes from the issue that a parent(code) will be used many
times, and as either Male or Female!

I want to have 1 list of clones(Actual_Breeding_Code), and then another
referance at the time of cross as to the parent and gender.

What I have tried!
Table 1 has Breeding_Code_ID and Actual_Breeding_Code as fields
Table 2 has Clone_ID and Female_Parent_ID and Male_Parent_ID as fields.
With Table 1 "Breeding_Code_ID" related to
Table 2 "Female_Parent_ID" and "Male_Parent_ID".
I want to be able to data enter the female and male parent
"Actual_Breeding_Code" for all cross's and have that relationship
maintained.

This seems simple, but any help would be much appreciated... Thanks...

 

[Joan Wild]

I have what seems like a basic problem I just can't seem to figure
out! I am developing a DB to trap and report on plant breeding data.
I am wanting to store the unique code for each parent involved in any
cross in one field, beside a autonumber field in one table. This code
to be referenced through out the DB as that autonumber.

My problem comes from the issue that a parent(code) will be used many
times, and as either Male or Female!

I want to have 1 list of clones(Actual_Breeding_Code), and then
another referance at the time of cross as to the parent and gender.

What I have tried!
Table 1 has Breeding_Code_ID and Actual_Breeding_Code as fields
Table 2 has Clone_ID and Female_Parent_ID and Male_Parent_ID as
fields. With Table 1 "Breeding_Code_ID" related to
Table 2 "Female_Parent_ID" and "Male_Parent_ID".
I want to be able to data enter the female and male parent
"Actual_Breeding_Code" for all cross's and have that relationship
maintained.

This seems simple, but any help would be much appreciated... Thanks...

Perhaps I misunderstand but I think what you need is
tblIndividuals - a table of information about each plant (doesn't matter
whether it's used as male or female)
IndividualID
Location
etc.

tblCrosses - a table of the crosses done
CrossID
FemaleID
MaleID

tblIndividuals is related twice to tblCrosses 1 to many (IndividualID to
FemaleID and IndividualID to MaleID)
In the relationship window you add tblIndividuals twice to the display to
set up the two joins.

Does that help?

 

[dex]

Hi Joan... Yep thanks, it reinforces that I was on the right track with
my structure, but for some reason im still having problems.
I have a master reference table that contains its own "ID field that is
an auto-number and a P-Key"; it also contains all the other "ID fields"
for all the other tables (Number "data type").
I have created 1 to many relationships between the ID fields in the
master reference table and there namesake's (Autonumber & P-Key) in the
other tables.
Inclusive of the FemaleID and the MaleID from the Cross table times 2.
Should it be possible to create a query that feeds a form that allows
me to input data into the many fields in the several tables in a way
that maintains the relationship?
I have tried to add Non-ID fields to a query, which allow the data to
be put into the various fields in the various tables, but there does
not seem to be any reference to associate them back together again. I
was expecting to see ID numbers in the "master reference table"
that show the unique ID created by the autonumber ID field from the
various tables.
What have I done wrong?
It all seems so clear in my head, but I am missing something
fundamental that makes it work.

Thanks for any help you can offer....

....... Jason.......

 

[Joan Wild]

Hi Jason,

Hi Joan... Yep thanks, it reinforces that I was on the right track
with my structure, but for some reason im still having problems.
I have a master reference table that contains its own "ID field that
is an auto-number and a P-Key"; it also contains all the other "ID
fields" for all the other tables (Number "data type").

Why? What purpose does this serve?

I have created 1 to many relationships between the ID fields in the
master reference table and there namesake's (Autonumber & P-Key) in
the other tables.

This doesn't make sense, since that won't/can't be a 1 to many relationship.

Inclusive of the FemaleID and the MaleID from the Cross table times 2.
Should it be possible to create a query that feeds a form that allows
me to input data into the many fields in the several tables in a way
that maintains the relationship?

Sure. Just create a form for your cross table with two combo boxes for the
FemaleID and MaleID (that lookup to the Individuals table).

You need to further explain the purpose of your master reference table
though, as that doesn't fit in in my mind.

Perhaps you should post the tables and fields that you now have (I mean list
them, don't post an attachment).

 

[dex]

Hi Joan.....
I had an opertunity to show a programmer mate my DB, and it does
seem I overcomplicated it (too many tables), as well as messed up the
relationships too.
I am re-working it at the moment.
I had created referance tables, when I really wanted to store
data-entered info in them.
I obviouslly know less about Access than I thought, and really
appreciate your assistance in learning more.
I will post(list) tables once I have re-worked a bit more.

Thanks...
......Jason.....