update table.field from excel

[gint32]

Hi everyone,

Other than manually updating each, is there a way of updating a specific column in one specific table. I have a list off usernames which is generally last name with first initial ge: smith andy = SmithA, but of course when there are two or more Smith A's, such as Smith Arron and Smith Amanda then the usernames(login names) have to be different, so I have an excel sheet with all of these "Double Ups", but I am trying to figure out a way of running through this list (in excel) and updating the Table in


Access where it finds a match in the table. So does anyone know of a way to loop through the list, or do I have to do this manually. The excel file has a unique the user id that matches the table.

Thanks for reading and any input that you may have

[pbaldy]

Presuming you can link to the Excel spreadsheet, an update query would be the quickest way to update all the records in the table.

[isladogs]

Create a linked table for your Excel file in Access. Run an update query linking the Excel table to your Access users table

[Micron]

Link to the spreadsheet (like you would a table) in Access and create an Update query where you join the Access table to the spreadsheet with an equal join on the user id.
BTW, Access tables don't have columns, they have fields. A common question here ought to be why you use Excel in the first place when a table is the only thing you need.

[gint32]

Link to the spreadsheet (like you would a table) in Access and create an Update query where you join the Access table to the spreadsheet with an equal join on the user id.
BTW, Access tables don't have columns, they have fields. A common question here ought to be why you use Excel in the first place when a table is the only thing you need.

thanks..re excel this is the way the new data was presented to me!

[gint32]

Link to the spreadsheet (like you would a table) in Access and create an Update query where you join the Access table to the spreadsheet with an equal join on the user id.
BTW, Access tables don't have columns, they have fields. A common question here ought to be why you use Excel in the first place when a table is the only thing you need.

Sorry Everyone, but I am having trouble with this one, could someone be so kind as to provide me with a downloadable example, so as I can adjust to suit my needs.
I've done the link to excel a
and the equal join on the IDs, b

But that's as far as my small mind can get too!

Thanks

[Micron]

could someone be so kind as to provide me with a downloadable example

Not me - I'm too lazy to create a db and a workbook, interpret your data, populate my workbook and table then join them and create a query for you that's probably all wrong. But you can post a db and a small workbook with data in both files and I/we will see what comes out of it.

[gint32]

Sounds like a big ask .My apologies .I really just need the part I am stuck at. Which is what goes in the update to field. As when I click in this field there is no data to select from...so do i manually type something like..
Excel.workbook.column.F3.values

[pbaldy]

Since the Excel file is linked, it's the way you would refer to a table field:

[FieldName]

The two tables would need to be joined in the query.

[gint32]

Since the Excel file is linked, it's the way you would refer to a table field:

[FieldName]

The two tables would need to be joined in the query.

As I said in post 6, the join are done (with the two table) thanks though

[isladogs]

Here's a very basic update query for you to adapt

UPDATE tblAccess INNER JOIN tblExcel ON tblAccess.Id = tblExcel.id SET tblAccess.LastName = [tblExcel].[LastName];

Change the table and field names accordingly.
However if you use the query designer, intellisense will guide you anyway.

[gint32]

Thanks..as always you always come through. When all else fail..👍

[isladogs]

You're welcome. Not sure what your problem was however.
Until you feel confident writing SQL, use the query designer to construct what you require.
If you want to then convert to SQL that's easy to do ... or you could use my SQL to VBA and back again utility to help you ....

[gint32]

[QUOTE=isladogs;If this has helped, please click the star button and leave a comment....[/QUOTE] wheres the star button

[isladogs]

wheres the star button

Next to the triangle!
OK, its in the green bar below this at the bottom of each post