Newbie Update Query Question?

[General Specific]

Access 2007

I have an equipment inventory in Access.

I have a separate lookup table in an Excel spreadsheet that looks up
barcode numbers and creates a daily list of equipment on a site.

Can I use an Update Query to change the "Location" field in the main
inventory list in the database?
If so, do the records from the spreadsheet have be identical field-for-
field as the main database inventory?


Example:

Database Inventory:

Barcode Number, Item Type, Make, Model, Description, Serial, Purchase
Price, Location


Excel List

Barcode Number, Description, Location

The Excel list might be 100 barcodes of the possible 2000 items. The
Location field would be different. Would an Update Query update the
Location field of those 100 of the 2000 in the database list that have
matching barcodes?

 

[Dirk Goldgar]

Access 2007

I have an equipment inventory in Access.

I have a separate lookup table in an Excel spreadsheet that looks up
barcode numbers and creates a daily list of equipment on a site.

Can I use an Update Query to change the "Location" field in the main
inventory list in the database?
If so, do the records from the spreadsheet have be identical field-for-
field as the main database inventory?


Example:

Database Inventory:

Barcode Number, Item Type, Make, Model, Description, Serial, Purchase
Price, Location


Excel List

Barcode Number, Description, Location

The Excel list might be 100 barcodes of the possible 2000 items. The
Location field would be different. Would an Update Query update the
Location field of those 100 of the 2000 in the database list that have
matching barcodes?

So you want to update those records in the Inventory table (in the Access
database) with the Location information from the Excel spreadsheet? Sure,
that should be a pretty simple update query. Link to the Excel spreadsheet,
creating a linked table, then create a query that joins the two tables on
the [Barcode Number] field, and turn it into an update query that sets the
Inventory table's Location field equal to the linked Excel table's Location
field. The SQL would be something like this

UPDATE
Inventory INNER JOIN ExcelList
ON Inventory.[Barcode Number] = ExcelList.[Barcode Number]
SET Inventory.Location = ExcelList.Location;

 

[General Specific]

Access 2007

I have an equipment inventory in Access.

I have a separate lookup table in an Excel spreadsheet that looks up
barcode numbers and creates a daily list of equipment on a site.

Can I use an Update Query to change the "Location" field in the main
inventory list in the database?
If so, do the records from the spreadsheet have be identical field-for-
field as the main database inventory?

Database Inventory:

Barcode Number, Item Type, Make, Model, Description, Serial, Purchase
Price, Location

Excel List

Barcode Number, Description, Location

The Excel list might be 100 barcodes of the possible 2000 items. The
Location field would be different. Would an Update Query update the
Location field of those 100 of the 2000 in the database list that have
matching barcodes?

So you want to update those records in the Inventory table (in the Access
database) with the Location information from the Excel spreadsheet? Sure,
that should be a pretty simple update query. Link to the Excel spreadsheet,
creating a linked table, then create a query that joins the two tables on
the [Barcode Number] field, and turn it into an update query that sets the
Inventory table's Location field equal to the linked Excel table's Location
field. The SQL would be something like this

UPDATE
Inventory INNER JOIN ExcelList
ON Inventory.[Barcode Number] = ExcelList.[Barcode Number]
SET Inventory.Location = ExcelList.Location;

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

I can do this without the SQL code, right?

I would be processing many location updates per day. I can create a
linked table, run the update query, then delete the table.

Thanks

 

[Dirk Goldgar]

I can do this without the SQL code, right?

You can use the visual query designer to create the query, if that's what
you mean. That's how I build most queries, but I post SQL for examples,
because it's a lot more efficient than posting pictures of the designer
window -- even if attachments were appropriate in these text-based
newsgroups. But it doesn't matter whether you build the query in design
view or in SQL view -- the resulting query is the same. What the query
designer presents is pretty much just a pretty face on the underlying SQL.

I would be processing many location updates per day. I can create a
linked table, run the update query, then delete the table.

Yep. The only question is whether you want to write VBA code to automate
the process, or are satisfied doing it manually.

 

[General Specific]

You can use the visual query designer to create the query, if that's what
you mean. That's how I build most queries, but I post SQL for examples,
because it's a lot more efficient than posting pictures of the designer
window -- even if attachments were appropriate in these text-based
newsgroups. But it doesn't matter whether you build the query in design
view or in SQL view -- the resulting query is the same. What the query
designer presents is pretty much just a pretty face on the underlying SQL.


Yep. The only question is whether you want to write VBA code to automate
the process, or are satisfied doing it manually.

I am learning, so it's manual first and then VBA.