Inputting Large Amounts of Data

[Thorson]

Is there a way to update one field for a large amount of records, if each
record will have a different value for that field, without typing each field
value in individually?

 

[KARL DEWEY]

Will not an update query do it for you?

 

[Thorson]

no because the field for each record needs to be different, from my
understanding in an update query it updates the field to the same value for
each record.

 

[KARL DEWEY]

What determines the new value? Maybe you can use a translation table in
the update query.

 

[Clif McIrvin]

What determines the new value? Maybe you can use a translation
table in
the update query.

or perhaps a VBA Function?

 

[John W. Vinson]

no because the field for each record needs to be different, from my
understanding in an update query it updates the field to the same value for
each record.

The Update To need not be a constant - but there must be SOME way for Access
to determine which record gets what value. How do you determine what the new
value should be? Can it be derived or looked up given existing values in the
record? Where does the new value COME from?

Obviously, if it comes from a human brain, then there must be a path from
brain to fingers to keyboard to form; the Access telepathic interface module
won't be coming out until Access version <mmmpf> <THAT INFORMATION IS NDA>

SIGNAL INTERRUPTED
CONNECTION HAS BEEN LOST

 

[Thorson]

Sorry it took me so long to respond, this was all going to my spam box in my
e-mail....

The data that is input does just come from a spreadsheet listing. I have a
table that lists animal's birth information, when that information is entered
some animals are missing their Sire (father) listing, later this field is
updated by entering in the sire or by updating the currently listed sire to
the correct one. This is usually done ~400 or more animals at once, it is
time consuming to go through the table and enter them all or change them
manually. It doesn't happen that often so I don't know that I need a code
that is regularly available to use. There is obviously no pattern to which
sire goes with which animal so I'm not sure how to do this.

let me know if you need more info. Thank you for the help.

 

[John W. Vinson]

Sorry it took me so long to respond, this was all going to my spam box in my
e-mail....

The data that is input does just come from a spreadsheet listing. I have a
table that lists animal's birth information, when that information is entered
some animals are missing their Sire (father) listing, later this field is
updated by entering in the sire or by updating the currently listed sire to
the correct one. This is usually done ~400 or more animals at once, it is
time consuming to go through the table and enter them all or change them
manually. It doesn't happen that often so I don't know that I need a code
that is regularly available to use. There is obviously no pattern to which
sire goes with which animal so I'm not sure how to do this.

Again:

How can Access determine - based on information in a table or in a spreadsheet
- WHICH animal is the sire? Is that information available in computer-readable
form, or is this something coming from a sheet of paper via a human mind?

You certainly would NOT do this using a table datasheet directly, except
perhaps as a one-time emergency operation; you'ld use a Form instead, and
perhaps an Update Query if you can identify a group of animals all with the
same sire.

But you have not explained how that information can be identified.

 

[Thorson]

I'm sorry I must have mis-understood last time... The information is stored
on an excel spreadsheet. A person out at the farm enters it into excel and
then if any changes are needed in the future they once again go into excel
and enter the new sire or other changes.

The hope is that soon the farm workers will enter the information straight
into the database, so if there are any changes they will skip the step of
entering it into excel and just enter it straight into the database. So
this will hopefully be a short-term fix until the database is complete and
running.

So I have the calf ID (field name: EarTag) and the Sire information listed
in a spreadsheet in excel, I would like to update the sire information in the
database on the tblBirthInformation. Hopefully that clarifies it more.

 

[Paul Shapiro]

You can import the Excel data into a temporary table, or even just link to
the Excel data as if it were a table. Either way, then you run an update
query by joining the new information to the existing table. I would use a
Where clause to only perform the updates for the rows where the new info
differs from the existing data.

 

[maxtaurus1]

Again:

How can Access determine - based on information in a table or in a
spreadsheet
- WHICH animal is the sire? Is that information available in
computer-readable
form, or is this something coming from a sheet of paper via a human mind?

You certainly would NOT do this using a table datasheet directly, except
perhaps as a one-time emergency operation; you'ld use a Form instead, and
perhaps an Update Query if you can identify a group of animals all with
the
same sire.

But you have not explained how that information can be identified.