Update Query with linked excel table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AJ - 04 May 2006 16:05 GMT

I have two tables:

log sheet
log_sheet - linked table from excel (emailed from contractor and saved to my
computer so I can update my database)

They have the same field names, ie: project number, contact person, company
etc.

I need to be able to update the log sheet table with the linked table using
an update query. How would I go about doing this? All of my reports run off
of log sheet and I don't want to have to update it by hand.

Thanks in advance for your time and efforts.

Reply to this Message

[MVP] S.Clark - 04 May 2006 20:41 GMT

Add both tables.
Link by id fields
Add field to be updated. Set what new value would be.(i.e. field name from
spreadsheet.)
Run query

Signature

Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

>I have two tables:
>
[quoted text clipped - 14 lines]
>
> Thanks in advance for your time and efforts.

Reply to this Message

AJ - 05 May 2006 14:23 GMT

Ok

I have added log_sheet (which is linked to excel) and log sheet.
I have joined fpi_log and fpi (join type - only include when equal)
I inserted into query:

field: fpi_log
table: log_sheet
update to: This is where I don't know what to put, I typed fpi which would
be from log sheet. That doesn't work. Do I put the name of the table I want
to update?

I did create an append query, which adds the fields that don't already
exist, but now I need to append those that exist with the new data.

> Add both tables.
> Link by id fields
[quoted text clipped - 20 lines]
> >
> > Thanks in advance for your time and efforts.

Reply to this Message