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
5  17682 
<jo************ *@gmail.comwrot e in message
news:11******** *************@j 27g2000cwj.goog legroups.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
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.comwrot e in message
news:11******** *************@j 27g2000cwj.goog legroups.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
On Mar 1, 4:10 pm, "Phil Stanton" <p...@stantonfa mily.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.comwrot e in message
news:11******** *************@j 27g2000cwj.goog legroups.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
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.comwrot e in message
news:11******** *************@h 3g2000cwc.googl egroups.com...
On Mar 1, 4:10 pm, "Phil Stanton" <p...@stantonfa mily.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.comwro te in message
news:11******* **************@ j27g2000cwj.goo glegroups.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
On Mar 3, 4:02 pm, "Phil Stanton" <p...@stantonfa mily.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.comwrot e in message
news:11******** *************@h 3g2000cwc.googl egroups.com...
On Mar 1, 4:10 pm, "Phil Stanton" <p...@stantonfa mily.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.comwrot e in message
>news:11******* **************@ j27g2000cwj.goo glegroups.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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 catch is I want to move one field as is from the first
table to the second, but the rest of the fields in the second table are
calculations based on fields in the first table.
The first table is called XFILE. It has fields SVCCODE, PRICE,...
|
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 in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and the
data that I am pulling into it. I just have one challenge that may
require a lot of work and I...
|
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 automatically reflect Phone1.
|
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
|
by: Leonardo Gangemi |
last post by:
How to align right a table based on another table created dinamically?
Leonardo
| | |
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". In table#2 is the list of Card names with their corresponding card numbers. What I need is when my user selects the "Card Name" from list in Table#1 (the list populated via query for the field), then "Card Number" should be populated with the...
|
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 products (also hundreds/thousands of records):
|
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 is what I have for form fields:
Carton # <auto number>
Item Title <txt>
Item Accquisition Date <txt (date)>
Is item consigned <yes/no>
|
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 called for example BigTable and I have another smaller
table called SmallTable - both tables are joined at the Account field.
I want to set up a query in Access which would allow me to delete all
records from BigTable which are NOT in SmallTable based...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
