Auto Populate Based on fields from another table

[joshua.nicholes]

I have an access database that consists of two tables.A data
collection table and a species list table. The data collection table
has about 1500 records in it and the species list has about 600. The
species list has 7 fields the first is a four digit unique identifier
(species) it is set as the primary key. I have created a relationship
to the data collection table which also has a species field (4 digit
id). In my form I have the species field as a drop-down pulling from
the species list table. I want to make access auto populate seven
fields on the data collection table with the same data as the species
list based on that species id. I can not figure this out.

Thanks

[Larry Linson]

<jo*************@gmail.comwrote in message
news:11*********************@j27g2000cwj.googlegro ups.com...

>I have an access database that consists of two tables.A data
collection table and a species list table. The data collection table
has about 1500 records in it and the species list has about 600. The
species list has 7 fields the first is a four digit unique identifier
(species) it is set as the primary key. I have created a relationship
to the data collection table which also has a species field (4 digit
id). In my form I have the species field as a drop-down pulling from
the species list table. I want to make access auto populate seven
fields on the data collection table with the same data as the species
list based on that species id. I can not figure this out.

Unless it is time-sensitive data that will change, and has to be captured
"as it is at time of update", you don't want to populate the collection
table with data from the species table... you want to use the 4-digit id as
a foreign key to the species table in Queries, and retrieve the information
from there when you _use_ it, e.g., when you show it in a Form or in a
Report. (You shouldn't be using datasheet view, either of Tables or of
Queries, except occasionally during development.)

Larry Linson
Microsoft Access MVP

[Phil Stanton]

Not good practice Joshua. Information should only be held once. If you
change the data in the Species Table, it means that you have to find each
record in the Data table that relates to that species, and change it
manually. OK I know it can be done automatically, but that's a fag to do.

The form should be based on a query containing the 2 tables and, having
selected the correct species from your drop down list, the form will display
the appropriate information from your species table as well as the new data
you are entering in to your data table.

Incidently, 600 species on a dropdown is a lot. Can they be grouped in any
way?

And, why a 4 digit key. Is the number significant?

HTH

Phil
<jo*************@gmail.comwrote in message
news:11*********************@j27g2000cwj.googlegro ups.com...

>I have an access database that consists of two tables.A data
collection table and a species list table. The data collection table
has about 1500 records in it and the species list has about 600. The
species list has 7 fields the first is a four digit unique identifier
(species) it is set as the primary key. I have created a relationship
to the data collection table which also has a species field (4 digit
id). In my form I have the species field as a drop-down pulling from
the species list table. I want to make access auto populate seven
fields on the data collection table with the same data as the species
list based on that species id. I can not figure this out.

Thanks

[joshua.nicholes]

On Mar 1, 4:10 pm, "Phil Stanton" <[e-mail]:

Not good practice Joshua. Information should only be held once. If you
change the data in the Species Table, it means that you have to find each
record in the Data table that relates to that species, and change it
manually. OK I know it can be done automatically, but that's a fag to do.

The form should be based on a query containing the 2 tables and, having
selected the correct species from your drop down list, the form will display
the appropriate information from your species table as well as the new data
you are entering in to your data table.

Incidently, 600 species on a dropdown is a lot. Can they be grouped in any
way?

And, why a 4 digit key. Is the number significant?

HTH

Phil

<[e-mail] in message

news:11*********************@j27g2000cwj.googlegro ups.com...

I have an access database that consists of two tables.A data
collection table and a species list table. The data collection table
has about 1500 records in it and the species list has about 600. The
species list has 7 fields the first is a four digit unique identifier
(species) it is set as the primary key. I have created a relationship
to the data collection table which also has a species field (4 digit
id). In my form I have the species field as a drop-down pulling from
the species list table. I want to make access auto populate seven
fields on the data collection table with the same data as the species
list based on that species id. I can not figure this out.

Thanks

The 4 digit identifier is the short form of the scientific name of the
plant. Unfortunately I can not reduce the species list any further 600
is about the limit. It is sorted alphabetically so anyone that is
farmiliar with plant id can quickly scroll-down and find the
appropriate species. Mostly I need a step-by step on writing the
query. I need to have the query compare the species field in the data
collection table with the species field in the species list and find
the matching record. Then look at that row in the species table and
pull out the other six values and put them in the data collection
table.

Thanks

[Phil Stanton]

So, If I understand corectly
TblSpecies
SpeciesID Primary Key (4 figure number)
SpeciesName
SpeciesField3
SpeciesField4
SpeciesField5
SpeciesField6
SpeciesField7

TblData
DataID Primary Key
SpeciesID Foreign Key
SpeciesField3
SpeciesField4
SpeciesField5
SpeciesField6
SpeciesField7
DataField1
DataField2
etc

In your "Data form", you select which species of plant the Data applies to
from a drop down list, and then add the additional bits of data. The form
shows this data only

DataID Of no significance, so may or may not be
shown.
SpeciesID
SpeciesName
SpeciesField3
SpeciesField4
SpeciesField5
SpeciesField6
SpeciesField7
DataField1
DataField2
etc

Is there any possibility that the information on the "Data form" in the
SpeciesFields will differ from the information in the Species Table? ie are
you using the information from the Species Table purely as a template and
then alter the information eg if the Species table says "Pale Green Leaves",
will you ever alter the Data Table to say "Dark Green Leaves"

Phil

<jo*************@gmail.comwrote in message
news:11*********************@h3g2000cwc.googlegrou ps.com...

On Mar 1, 4:10 pm, "Phil Stanton" <[e-mail]:

>Not good practice Joshua. Information should only be held once. If you
change the data in the Species Table, it means that you have to find each
record in the Data table that relates to that species, and change it
manually. OK I know it can be done automatically, but that's a fag to do.

The form should be based on a query containing the 2 tables and, having
selected the correct species from your drop down list, the form will
display
the appropriate information from your species table as well as the new
data
you are entering in to your data table.

Incidently, 600 species on a dropdown is a lot. Can they be grouped in
any
way?

And, why a 4 digit key. Is the number significant?

HTH

Phil

<[e-mail] in message

news:11*********************@j27g2000cwj.googlegr oups.com...

>I have an access database that consists of two tables.A data
collection table and a species list table. The data collection table
has about 1500 records in it and the species list has about 600. The
species list has 7 fields the first is a four digit unique identifier
(species) it is set as the primary key. I have created a relationship
to the data collection table which also has a species field (4 digit
id). In my form I have the species field as a drop-down pulling from
the species list table. I want to make access auto populate seven
fields on the data collection table with the same data as the species
list based on that species id. I can not figure this out.

Thanks

The 4 digit identifier is the short form of the scientific name of the
plant. Unfortunately I can not reduce the species list any further 600
is about the limit. It is sorted alphabetically so anyone that is
farmiliar with plant id can quickly scroll-down and find the
appropriate species. Mostly I need a step-by step on writing the
query. I need to have the query compare the species field in the data
collection table with the species field in the species list and find
the matching record. Then look at that row in the species table and
pull out the other six values and put them in the data collection
table.

Thanks

[joshua.nicholes]

On Mar 3, 4:02 pm, "Phil Stanton" <[e-mail]:

So, If I understand corectly
TblSpecies
SpeciesID Primary Key (4 figure number)
SpeciesName
SpeciesField3
SpeciesField4
SpeciesField5
SpeciesField6
SpeciesField7

TblData
DataID Primary Key
SpeciesID Foreign Key
SpeciesField3
SpeciesField4
SpeciesField5
SpeciesField6
SpeciesField7
DataField1
DataField2
etc

In your "Data form", you select which species of plant the Data applies to
from a drop down list, and then add the additional bits of data. The form
shows this data only

DataID Of no significance, so may or may not be
shown.
SpeciesID
SpeciesName
SpeciesField3
SpeciesField4
SpeciesField5
SpeciesField6
SpeciesField7
DataField1
DataField2
etc

Is there any possibility that the information on the "Data form" in the
SpeciesFields will differ from the information in the Species Table? ie are
you using the information from the Species Table purely as a template and
then alter the information eg if the Species table says "Pale Green Leaves",
will you ever alter the Data Table to say "Dark Green Leaves"

Phil

<[e-mail] in message

news:11*********************@h3g2000cwc.googlegrou ps.com...

On Mar 1, 4:10 pm, "Phil Stanton" <[e-mail]:

Not good practice Joshua. Information should only be held once. If you
change the data in the Species Table, it means that you have to find each
record in the Data table that relates to that species, and change it
manually. OK I know it can be done automatically, but that's a fag to do.

The form should be based on a query containing the 2 tables and, having
selected the correct species from your drop down list, the form will
display
the appropriate information from your species table as well as the new
data
you are entering in to your data table.

Incidently, 600 species on a dropdown is a lot. Can they be grouped in
any
way?

And, why a 4 digit key. Is the number significant?

HTH

Phil

<[e-mail] in message

>news:11*********************@j27g2000cwj.googlegr oups.com...

I have an access database that consists of two tables.A data
collection table and a species list table. The data collection table
has about 1500 records in it and the species list has about 600. The
species list has 7 fields the first is a four digit unique identifier
(species) it is set as the primary key. I have created a relationship
to the data collection table which also has a species field (4 digit
id). In my form I have the species field as a drop-down pulling from
the species list table. I want to make access auto populate seven
fields on the data collection table with the same data as the species
list based on that species id. I can not figure this out.

Thanks

The 4 digit identifier is the short form of the scientific name of the
plant. Unfortunately I can not reduce the species list any further 600
is about the limit. It is sorted alphabetically so anyone that is
farmiliar with plant id can quickly scroll-down and find the
appropriate species. Mostly I need a step-by step on writing the
query. I need to have the query compare the species field in the data
collection table with the species field in the species list and find
the matching record. Then look at that row in the species table and
pull out the other six values and put them in the data collection
table.

Thanks

Sorry it took so long to reply. You are correct in your layout of the
table3s. Yes the species table is a template of sorts and I am not
familiar enough with access querys to know specifically how to write
one. I appreciate your help.I might mention that these tables have
been imported from two excel spreadsheets and I simply want to append
the species information to the existing records without modifying the
data because of the large number of records.