updating a table field based on key field

[Guest]

I have tblInvoices with:
[InvID] (text key field)
[Customer]
[Rate]
[Units]
[TaxAmount]

Every month I export all the fields except [TaxAmount] into an Excel
spreadsheet and send it to my payroll company. They process the payroll and
send a spreadsheet back with the same information with the addition of the
[TaxAmount] field. I need to import that field into tblInvoice using my key
field [InvID] but have no idea what sort of query I can use to update the
table in this way. Any pointers would be appreciated.

Thanks,
spence

 

[Guest]

If, and it's a big IF, no records are added, deleted, or updated while the
spreadsheet is away, you could delete all records in the table then import
the spreadsheet back into it. However if there are any changes while the
spreadsheet is gone, those changes will be lost. Also if you happen to
referiential integrity enforces between this table and another, Access
probably won't let you do it either.

You could link to the spreadsheet then run a query from it to update your
table.

UPDATE InvoicesXLS
INNER JOIN tblInvoices ON InvoicesXLS.InvID = tblInvoices.InvID
SET tblInvoices.TaxAmount = [InvoicesXLS]![TaxAmount];

 

[Guest]

Jerry,

Thanks for the response. I am the only one with Access ot this database and
the turn around for the spreadsheet is pretty quick, so I could easliy go
without any records in the table being changed. However, I am enforcing
referential integrity and that sounds like it might be a problem. So I'll try
going with your other suggestion. Am wondering though, if I there is any
disadvantage to pasting the spreadsheet in as a new table and doing my query
there rather than doing it from Exel. And if so, what that looks like.

Thanks again,
spence

If, and it's a big IF, no records are added, deleted, or updated while the
spreadsheet is away, you could delete all records in the table then import
the spreadsheet back into it. However if there are any changes while the
spreadsheet is gone, those changes will be lost. Also if you happen to
referiential integrity enforces between this table and another, Access
probably won't let you do it either.

You could link to the spreadsheet then run a query from it to update your
table.

UPDATE InvoicesXLS
INNER JOIN tblInvoices ON InvoicesXLS.InvID = tblInvoices.InvID
SET tblInvoices.TaxAmount = [InvoicesXLS]![TaxAmount];
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have tblInvoices with:
[InvID] (text key field)
[Customer]
[Rate]
[Units]
[TaxAmount]

Every month I export all the fields except [TaxAmount] into an Excel
spreadsheet and send it to my payroll company. They process the payroll and
send a spreadsheet back with the same information with the addition of the
[TaxAmount] field. I need to import that field into tblInvoice using my key
field [InvID] but have no idea what sort of query I can use to update the
table in this way. Any pointers would be appreciated.

Thanks,
spence