Updating Reords from an Excel file

I second Jellifish's motion of using an Access form as "an easier way"
wrote in message

> Why not use Access forms?
>
> You are digging a very big hole for yourself by using Excel in this
> manner.
>
> "dhstein" wrote in message
>
>> We have a situation where we will be entering data into an Excel
>> spreadsheet
>> each day which updates 1 field in records in an Access database.
>> The excel
>> file is created from the Access table, the headings are identical.
>> The excel
>> user will be updating one field in the records in the file and we
>> will read
>> that file. I can write a program to read the file and locate the
>> appropriate record and update the 1 field that is changing. But I'm
>> wondering if there is any easier way. Thanks for any suggestions on
>> this.
>
>
 >> Stay informed about: Updating Reords from an Excel file 

Thanks for the responses.
1) Both the CEO who wants the program (major reason) and the person who will
be entering the data prefer Excel.
2) This is a temporary situation - after we get this data into the system
over the next few weeks this need goes away.
3) We need this to start immediately - there is no time to develop the form,
create a run time, distribute the run-time - get everyone familiar with using
it etc. etc. - The excel sheet is ready now.
4) The spreadsheet will be on a laptop - brought into the warehouse to take
inventory. No wireless network access there.

Thanks.

"Clif McIrvin" wrote:

> I second Jellifish's motion of using an Access form as "an easier way"
> wrote in message

> > Why not use Access forms?
> >
> > You are digging a very big hole for yourself by using Excel in this
> > manner.
> >
> > "dhstein" wrote in message
> >
> >> We have a situation where we will be entering data into an Excel
> >> spreadsheet
> >> each day which updates 1 field in records in an Access database.
> >> The excel
> >> file is created from the Access table, the headings are identical.
> >> The excel
> >> user will be updating one field in the records in the file and we
> >> will read
> >> that file. I can write a program to read the file and locate the
> >> appropriate record and update the 1 field that is changing. But I'm
> >> wondering if there is any easier way. Thanks for any suggestions on
> >> this.
> >
> >
>
>
>
> --
> Clif
>
>
>
 >> Stay informed about: Updating Reords from an Excel file 

Sound's like you're going to be investigating the linked spreadsheet /
update query approach, then.

Post back if you need more specific assistance.
 >> Stay informed about: Updating Reords from an Excel file 

I'm trying to experiment with creating the update query. I'm not sure how to
specify the criteria - to get a record in linked table xyz with a primary key
value of 1234 to update a field in a record in table ABC with the same key
value. Any example of how this is done would be appreciated.

"dhstein" wrote:

> We have a situation where we will be entering data into an Excel spreadsheet
> each day which updates 1 field in records in an Access database. The excel
> file is created from the Access table, the headings are identical. The excel
> user will be updating one field in the records in the file and we will read
> that file. I can write a program to read the file and locate the
> appropriate record and update the 1 field that is changing. But I'm
> wondering if there is any easier way. Thanks for any suggestions on this.
 >> Stay informed about: Updating Reords from an Excel file 

Ok, some general advice:

Make sure you include the Primary Key in the Excel spreadsheet, this will
make the updates more reliable.

Ensure that the person who is ammending the spreadsheet knows exactly what
he/she is doing and why, you don't want to do this twice I would imagine.


"dhstein" wrote in message

> Thanks for the responses.
> 1) Both the CEO who wants the program (major reason) and the person who
> will
> be entering the data prefer Excel.
> 2) This is a temporary situation - after we get this data into the system
> over the next few weeks this need goes away.
> 3) We need this to start immediately - there is no time to develop the
> form,
> create a run time, distribute the run-time - get everyone familiar with
> using
> it etc. etc. - The excel sheet is ready now.
> 4) The spreadsheet will be on a laptop - brought into the warehouse to
> take
> inventory. No wireless network access there.
>
> Thanks.
>
> "Clif McIrvin" wrote:
>
>> I second Jellifish's motion of using an Access form as "an easier way"
>> wrote in message
>
>> > Why not use Access forms?
>> >
>> > You are digging a very big hole for yourself by using Excel in this
>> > manner.
>> >
>> > "dhstein" wrote in message
>> >
>> >> We have a situation where we will be entering data into an Excel
>> >> spreadsheet
>> >> each day which updates 1 field in records in an Access database.
>> >> The excel
>> >> file is created from the Access table, the headings are identical.
>> >> The excel
>> >> user will be updating one field in the records in the file and we
>> >> will read
>> >> that file. I can write a program to read the file and locate the
>> >> appropriate record and update the 1 field that is changing. But I'm
>> >> wondering if there is any easier way. Thanks for any suggestions on
>> >> this.
>> >
>> >
>>
>>
>>
>> --
>> Clif
>>
>>
>>
 >> Stay informed about: Updating Reords from an Excel file 

OK so if I include the primary key in the excel file, is that sufficient for
the update query - the primary key and the field to update ? Or do I need to
have the excel spreadsheet look identical to the table as far as field
definitions? Thanks.



"Jellifish" wrote:

> Ok, some general advice:
>
> Make sure you include the Primary Key in the Excel spreadsheet, this will
> make the updates more reliable.
>
> Ensure that the person who is ammending the spreadsheet knows exactly what
> he/she is doing and why, you don't want to do this twice I would imagine.
>
>
> "dhstein" wrote in message
>
> > Thanks for the responses.
> > 1) Both the CEO who wants the program (major reason) and the person who
> > will
> > be entering the data prefer Excel.
> > 2) This is a temporary situation - after we get this data into the system
> > over the next few weeks this need goes away.
> > 3) We need this to start immediately - there is no time to develop the
> > form,
> > create a run time, distribute the run-time - get everyone familiar with
> > using
> > it etc. etc. - The excel sheet is ready now.
> > 4) The spreadsheet will be on a laptop - brought into the warehouse to
> > take
> > inventory. No wireless network access there.
> >
> > Thanks.
> >
> > "Clif McIrvin" wrote:
> >
> >> I second Jellifish's motion of using an Access form as "an easier way"
> >> wrote in message
> >
> >> > Why not use Access forms?
> >> >
> >> > You are digging a very big hole for yourself by using Excel in this
> >> > manner.
> >> >
> >> > "dhstein" wrote in message
> >> >
> >> >> We have a situation where we will be entering data into an Excel
> >> >> spreadsheet
> >> >> each day which updates 1 field in records in an Access database.
> >> >> The excel
> >> >> file is created from the Access table, the headings are identical.
> >> >> The excel
> >> >> user will be updating one field in the records in the file and we
> >> >> will read
> >> >> that file. I can write a program to read the file and locate the
> >> >> appropriate record and update the 1 field that is changing. But I'm
> >> >> wondering if there is any easier way. Thanks for any suggestions on
> >> >> this.
> >> >
> >> >
> >>
> >>
> >>
> >> --
> >> Clif
> >>
> >>
> >>
>
>
>
 >> Stay informed about: Updating Reords from an Excel file 

You're going to be updating a single column in the Access database once you
have it all completed in Excel, so all you need is the Primary Key and the
column you want to update (Primary Keys are unique and do not allow nulls,
so there is no ambiguity in which row is being updated).

You may want to include other columns to help the person who is going to be
doing the work though. Make sure they know that any changes they make to
these other columns will have no effect in the long-term as only one column
will be updated.

"dhstein" wrote in message

> OK so if I include the primary key in the excel file, is that sufficient
> for
> the update query - the primary key and the field to update ? Or do I need
> to
> have the excel spreadsheet look identical to the table as far as field
> definitions? Thanks.
>
>
>
> "Jellifish" wrote:
>
>> Ok, some general advice:
>>
>> Make sure you include the Primary Key in the Excel spreadsheet, this will
>> make the updates more reliable.
>>
>> Ensure that the person who is ammending the spreadsheet knows exactly
>> what
>> he/she is doing and why, you don't want to do this twice I would imagine.
>>
>>
>> "dhstein" wrote in message
>>
>> > Thanks for the responses.
>> > 1) Both the CEO who wants the program (major reason) and the person who
>> > will
>> > be entering the data prefer Excel.
>> > 2) This is a temporary situation - after we get this data into the
>> > system
>> > over the next few weeks this need goes away.
>> > 3) We need this to start immediately - there is no time to develop the
>> > form,
>> > create a run time, distribute the run-time - get everyone familiar with
>> > using
>> > it etc. etc. - The excel sheet is ready now.
>> > 4) The spreadsheet will be on a laptop - brought into the warehouse to
>> > take
>> > inventory. No wireless network access there.
>> >
>> > Thanks.
>> >
>> > "Clif McIrvin" wrote:
>> >
>> >> I second Jellifish's motion of using an Access form as "an easier way"
>> >> wrote in message
>> >
>> >> > Why not use Access forms?
>> >> >
>> >> > You are digging a very big hole for yourself by using Excel in this
>> >> > manner.
>> >> >
>> >> > "dhstein" wrote in message
>> >> >
>> >> >> We have a situation where we will be entering data into an Excel
>> >> >> spreadsheet
>> >> >> each day which updates 1 field in records in an Access database.
>> >> >> The excel
>> >> >> file is created from the Access table, the headings are identical.
>> >> >> The excel
>> >> >> user will be updating one field in the records in the file and we
>> >> >> will read
>> >> >> that file. I can write a program to read the file and locate the
>> >> >> appropriate record and update the 1 field that is changing. But
>> >> >> I'm
>> >> >> wondering if there is any easier way. Thanks for any suggestions
>> >> >> on
>> >> >> this.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >> Clif
>> >>
>> >>
>> >>
>>
>>
>>
 >> Stay informed about: Updating Reords from an Excel file 

Assuming that your Primary Key is a meaningless autonumber field, you
may wish to hide (and protect!) that column before letting others see
the worksheet.
 >> Stay informed about: Updating Reords from an Excel file 

Use the following SQL as a guide:

UPDATE tblAccessTable INNER JOIN tblExcelData
ON tblAccessTable.PK = tblExcelData.ExcelPK
SET tblAccessTable.MyField = [tblExcelData].[ExcelField];


"dhstein" wrote in message

> I'm trying to experiment with creating the update query. I'm not sure how
> to
> specify the criteria - to get a record in linked table xyz with a primary
> key
> value of 1234 to update a field in a record in table ABC with the same key
> value. Any example of how this is done would be appreciated.
>
> "dhstein" wrote:
>
>> We have a situation where we will be entering data into an Excel
>> spreadsheet
>> each day which updates 1 field in records in an Access database. The
>> excel
>> file is created from the Access table, the headings are identical. The
>> excel
>> user will be updating one field in the records in the file and we will
>> read
>> that file. I can write a program to read the file and locate the
>> appropriate record and update the 1 field that is changing. But I'm
>> wondering if there is any easier way. Thanks for any suggestions on
>> this.
 >> Stay informed about: Updating Reords from an Excel file 

Thanks that worked perfectly. I have 2 more questions if you have time to
answer.

1) If I want to update 2 fields in the same Update query - is it 2 separate
"UPDATE" statements - or can I put 2 fields in the same statement somehow?

2) If I don't use the PK but want to use a composite index like
"tableField1 = ExcelField1 AND tableField2 = ExcelField 2" is it just a
matter of using the "AND" operator in the criteria after the "ON" ?


"Jellifish" wrote:

> Use the following SQL as a guide:
>
> UPDATE tblAccessTable INNER JOIN tblExcelData
> ON tblAccessTable.PK = tblExcelData.ExcelPK
> SET tblAccessTable.MyField = [tblExcelData].[ExcelField];
>
>
> "dhstein" wrote in message
>
> > I'm trying to experiment with creating the update query. I'm not sure how
> > to
> > specify the criteria - to get a record in linked table xyz with a primary
> > key
> > value of 1234 to update a field in a record in table ABC with the same key
> > value. Any example of how this is done would be appreciated.
> >
> > "dhstein" wrote:
> >
> >> We have a situation where we will be entering data into an Excel
> >> spreadsheet
> >> each day which updates 1 field in records in an Access database. The
> >> excel
> >> file is created from the Access table, the headings are identical. The
> >> excel
> >> user will be updating one field in the records in the file and we will
> >> read
> >> that file. I can write a program to read the file and locate the
> >> appropriate record and update the 1 field that is changing. But I'm
> >> wondering if there is any easier way. Thanks for any suggestions on
> >> this.
>
>
>
 >> Stay informed about: Updating Reords from an Excel file 

> Thanks that worked perfectly. I have 2 more questions if you have time to
> answer.
>
> 1) If I want to update 2 fields in the same Update query - is it 2
> separate
> "UPDATE" statements - or can I put 2 fields in the same statement somehow?

You can update two fields at once:

UPDATE tblAccessTable INNER JOIN tblExcelData
ON tblAccessTable.PK = tblExcelData.ExcelPK
SET tblAccessTable.MyField = [tblExcelData].[ExcelField],
tblAccessTable.MyOtherField = [tblExcelData].[OtherExcelField];

> 2) If I don't use the PK but want to use a composite index like
> "tableField1 = ExcelField1 AND tableField2 = ExcelField 2" is it just a
> matter of using the "AND" operator in the criteria after the "ON" ?

Yep:

UPDATE tblAccessTable INNER JOIN tblExcelData
ON tblAccessTable.Fld1 = tblExcelData.ExcelFld1
AND tblAccessTable.Fld2 = tblExcelData.ExcelFld2
SET tblAccessTable.MyField = [tblExcelData].[ExcelField];


You can also combine both statements:

UPDATE tblAccessTable INNER JOIN tblExcelData
ON tblAccessTable.Fld1 = tblExcelData.ExcelFld1
AND tblAccessTable.Fld2 = tblExcelData.ExcelFld2
SET tblAccessTable.MyField = [tblExcelData].[ExcelField],
tblAccessTable.MyOtherField = [tblExcelData].[OtherExcelField];

SQL is a very powerful language, it's so easy to do stuff like this once you
know what you are doing, until then it's best to make regular backups of
your tables while you are experimenting with updates and deletes (speaking
from experience).




> "Jellifish" wrote:
>
>> Use the following SQL as a guide:
>>
>> UPDATE tblAccessTable INNER JOIN tblExcelData
>> ON tblAccessTable.PK = tblExcelData.ExcelPK
>> SET tblAccessTable.MyField = [tblExcelData].[ExcelField];
>>
>>
>> "dhstein" wrote in message
>>
>> > I'm trying to experiment with creating the update query. I'm not sure
>> > how
>> > to
>> > specify the criteria - to get a record in linked table xyz with a
>> > primary
>> > key
>> > value of 1234 to update a field in a record in table ABC with the same
>> > key
>> > value. Any example of how this is done would be appreciated.
>> >
>> > "dhstein" wrote:
>> >
>> >> We have a situation where we will be entering data into an Excel
>> >> spreadsheet
>> >> each day which updates 1 field in records in an Access database. The
>> >> excel
>> >> file is created from the Access table, the headings are identical.
>> >> The
>> >> excel
>> >> user will be updating one field in the records in the file and we will
>> >> read
>> >> that file. I can write a program to read the file and locate the
>> >> appropriate record and update the 1 field that is changing. But I'm
>> >> wondering if there is any easier way. Thanks for any suggestions on
>> >> this.
>>
>>
>>
 >> Stay informed about: Updating Reords from an Excel file 

Thanks Jellifish - that worked out perfectly. I appreciate the help and your
time.

David


"Jellifish" wrote:

> > Thanks that worked perfectly. I have 2 more questions if you have time to
> > answer.
> >
> > 1) If I want to update 2 fields in the same Update query - is it 2
> > separate
> > "UPDATE" statements - or can I put 2 fields in the same statement somehow?
>
> You can update two fields at once:
>
> UPDATE tblAccessTable INNER JOIN tblExcelData
> ON tblAccessTable.PK = tblExcelData.ExcelPK
> SET tblAccessTable.MyField = [tblExcelData].[ExcelField],
> tblAccessTable.MyOtherField = [tblExcelData].[OtherExcelField];
>
> > 2) If I don't use the PK but want to use a composite index like
> > "tableField1 = ExcelField1 AND tableField2 = ExcelField 2" is it just a
> > matter of using the "AND" operator in the criteria after the "ON" ?
>
> Yep:
>
> UPDATE tblAccessTable INNER JOIN tblExcelData
> ON tblAccessTable.Fld1 = tblExcelData.ExcelFld1
> AND tblAccessTable.Fld2 = tblExcelData.ExcelFld2
> SET tblAccessTable.MyField = [tblExcelData].[ExcelField];
>
>
> You can also combine both statements:
>
> UPDATE tblAccessTable INNER JOIN tblExcelData
> ON tblAccessTable.Fld1 = tblExcelData.ExcelFld1
> AND tblAccessTable.Fld2 = tblExcelData.ExcelFld2
> SET tblAccessTable.MyField = [tblExcelData].[ExcelField],
> tblAccessTable.MyOtherField = [tblExcelData].[OtherExcelField];
>
> SQL is a very powerful language, it's so easy to do stuff like this once you
> know what you are doing, until then it's best to make regular backups of
> your tables while you are experimenting with updates and deletes (speaking
> from experience).
>
>
>
>
> > "Jellifish" wrote:
> >
> >> Use the following SQL as a guide:
> >>
> >> UPDATE tblAccessTable INNER JOIN tblExcelData
> >> ON tblAccessTable.PK = tblExcelData.ExcelPK
> >> SET tblAccessTable.MyField = [tblExcelData].[ExcelField];
> >>
> >>
> >> "dhstein" wrote in message
> >>
> >> > I'm trying to experiment with creating the update query. I'm not sure
> >> > how
> >> > to
> >> > specify the criteria - to get a record in linked table xyz with a
> >> > primary
> >> > key
> >> > value of 1234 to update a field in a record in table ABC with the same
> >> > key
> >> > value. Any example of how this is done would be appreciated.
> >> >
> >> > "dhstein" wrote:
> >> >
> >> >> We have a situation where we will be entering data into an Excel
> >> >> spreadsheet
> >> >> each day which updates 1 field in records in an Access database. The
> >> >> excel
> >> >> file is created from the Access table, the headings are identical.
> >> >> The
> >> >> excel
> >> >> user will be updating one field in the records in the file and we will
> >> >> read
> >> >> that file. I can write a program to read the file and locate the
> >> >> appropriate record and update the 1 field that is changing. But I'm
> >> >> wondering if there is any easier way. Thanks for any suggestions on
> >> >> this.
> >>
> >>
> >>
>
>
>
 >> Stay informed about: Updating Reords from an Excel file 

No worries.


"dhstein" wrote in message

> Thanks Jellifish - that worked out perfectly. I appreciate the help and
> your
> time.
>
> David
>
>
> "Jellifish" wrote:
>
>> > Thanks that worked perfectly. I have 2 more questions if you have time
>> > to
>> > answer.
>> >
>> > 1) If I want to update 2 fields in the same Update query - is it 2
>> > separate
>> > "UPDATE" statements - or can I put 2 fields in the same statement
>> > somehow?
>>
>> You can update two fields at once:
>>
>> UPDATE tblAccessTable INNER JOIN tblExcelData
>> ON tblAccessTable.PK = tblExcelData.ExcelPK
>> SET tblAccessTable.MyField = [tblExcelData].[ExcelField],
>> tblAccessTable.MyOtherField = [tblExcelData].[OtherExcelField];
>>
>> > 2) If I don't use the PK but want to use a composite index like
>> > "tableField1 = ExcelField1 AND tableField2 = ExcelField 2" is it
>> > just a
>> > matter of using the "AND" operator in the criteria after the "ON" ?
>>
>> Yep:
>>
>> UPDATE tblAccessTable INNER JOIN tblExcelData
>> ON tblAccessTable.Fld1 = tblExcelData.ExcelFld1
>> AND tblAccessTable.Fld2 = tblExcelData.ExcelFld2
>> SET tblAccessTable.MyField = [tblExcelData].[ExcelField];
>>
>>
>> You can also combine both statements:
>>
>> UPDATE tblAccessTable INNER JOIN tblExcelData
>> ON tblAccessTable.Fld1 = tblExcelData.ExcelFld1
>> AND tblAccessTable.Fld2 = tblExcelData.ExcelFld2
>> SET tblAccessTable.MyField = [tblExcelData].[ExcelField],
>> tblAccessTable.MyOtherField = [tblExcelData].[OtherExcelField];
>>
>> SQL is a very powerful language, it's so easy to do stuff like this once
>> you
>> know what you are doing, until then it's best to make regular backups of
>> your tables while you are experimenting with updates and deletes
>> (speaking
>> from experience).
>>
>>
>>
>>
>> > "Jellifish" wrote:
>> >
>> >> Use the following SQL as a guide:
>> >>
>> >> UPDATE tblAccessTable INNER JOIN tblExcelData
>> >> ON tblAccessTable.PK = tblExcelData.ExcelPK
>> >> SET tblAccessTable.MyField = [tblExcelData].[ExcelField];
>> >>
>> >>
>> >> "dhstein" wrote in message
>> >>
>> >> > I'm trying to experiment with creating the update query. I'm not
>> >> > sure
>> >> > how
>> >> > to
>> >> > specify the criteria - to get a record in linked table xyz with a
>> >> > primary
>> >> > key
>> >> > value of 1234 to update a field in a record in table ABC with the
>> >> > same
>> >> > key
>> >> > value. Any example of how this is done would be appreciated.
>> >> >
>> >> > "dhstein" wrote:
>> >> >
>> >> >> We have a situation where we will be entering data into an Excel
>> >> >> spreadsheet
>> >> >> each day which updates 1 field in records in an Access database.
>> >> >> The
>> >> >> excel
>> >> >> file is created from the Access table, the headings are identical.
>> >> >> The
>> >> >> excel
>> >> >> user will be updating one field in the records in the file and we
>> >> >> will
>> >> >> read
>> >> >> that file. I can write a program to read the file and locate the
>> >> >> appropriate record and update the 1 field that is changing. But
>> >> >> I'm
>> >> >> wondering if there is any easier way. Thanks for any suggestions
>> >> >> on
>> >> >> this.
>> >>
>> >>
>> >>
>>
>>
>>
 >> Stay informed about: Updating Reords from an Excel file