473,387 Members | 1,303 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Auto Populate Based on fields from another table

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

Mar 1 '07 #1
5 17649

<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
Mar 2 '07 #2
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

Mar 2 '07 #3
On Mar 1, 4:10 pm, "Phil Stanton" <p...@stantonfamily.co.ukwrote:
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

<joshua.nicho...@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
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

Mar 2 '07 #4
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" <p...@stantonfamily.co.ukwrote:
>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

<joshua.nicho...@gmail.comwrote 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

Mar 3 '07 #5
On Mar 3, 4:02 pm, "Phil Stanton" <p...@stantonfamily.co.ukwrote:
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

<joshua.nicho...@gmail.comwrote in message

news:11*********************@h3g2000cwc.googlegrou ps.com...
On Mar 1, 4:10 pm, "Phil Stanton" <p...@stantonfamily.co.ukwrote:
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
<joshua.nicho...@gmail.comwrote 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.

Mar 14 '07 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Mike | last post by:
Here is my situation; I have two tables in a MS-SQL DB. One table with dollar amounts and service codes. I have a second table that I want to move some information into from the first table. The...
2
by: Daniel | last post by:
I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data...
2
by: PerryC | last post by:
Scenario: FormA: Field1, Field2 (Field1 is a combo box based on a NameTable w/ Name and phone fields) When users choose Field1 with Name1 (in record1 of NameTable), Field2 will...
3
by: bitoulis | last post by:
Hi, is it possible to use the records of a table as the field names of another table? If yes, how is it done? Thanks in advance Laertes
0
by: Leonardo Gangemi | last post by:
How to align right a table based on another table created dinamically? Leonardo
28
by: Rotor | last post by:
Hello everyone, I am new to access, coming over from FMP5.0. I am working with a table#1. In this table I have two fields (among others). Field one is "Card Name" field two is "Card Number". ...
3
by: Ronald S. Cook | last post by:
I have a table of keywords (hundreds/thousands of records): KeywordID KeywordName --------- ----------- 1 Apple 2 Orange 3 Pear I then have a table of...
4
by: scolivas | last post by:
I think this is a me thing. but can't remember how to do it. I have a form that I am using and would like for a txt box to automatically populate based on what is selected in a combo box. here...
2
by: Haas C | last post by:
Hi all! I am new to this and trying to learn as best as i can. This group is defiintely helpful in that regards. Anyways, I was wondering if someone can help me with this: I have a table...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.