Updating tables

[JT]

I have approximately 100 Access databases (one for each cost center). Each
cost center has their own folder on a corporate drive. There are 3 other
files (Excel) besides the database in each folder. I have a macro that will
cycle through all of the folders and replace any of the 3 Excel files in each
folder.

I now need to update 2 fields ("Office", "CostCenter") in a single table.
This table only has the 2 fields and each field has a text string of "4.0".
I need to update each field with "5.0".

I'm trying to create a sql statement that will update /replace each field
with "5.0"

The database name is "MyDatabase". The table name is "Version". The field
names are "Office" and "CostCenter".

Any help getting started would be greatly appreciated. Thanks.......

 

[Jeff Boyce]

So, you're saying that every time your corporation gains or loses a cost
center, you create a new Access database? That sounds like ... a
spreadsheet!

You won't get the best out of Access' relationally-oriented
features/functions (e.g., SQL statements) if you feed it 'sheet data.

You might want to consider creating a single Access database and adding a
single field ([CostCenter]) to the table that I'm assuming is central to
your application. Plus, you'd need a tlkpCostCenter (a cost center lookup
table) -- that way, when you gain/lose a cost center, you make a single
change on this single lookup table...

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[JT]

Thanks.....however, is it possible to change 4.0 to 5.0 programmatically?
Thanks..
--
JT

So, you're saying that every time your corporation gains or loses a cost
center, you create a new Access database? That sounds like ... a
spreadsheet!

You won't get the best out of Access' relationally-oriented
features/functions (e.g., SQL statements) if you feed it 'sheet data.

You might want to consider creating a single Access database and adding a
single field ([CostCenter]) to the table that I'm assuming is central to
your application. Plus, you'd need a tlkpCostCenter (a cost center lookup
table) -- that way, when you gain/lose a cost center, you make a single
change on this single lookup table...

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have approximately 100 Access databases (one for each cost center). Each
cost center has their own folder on a corporate drive. There are 3 other
files (Excel) besides the database in each folder. I have a macro that will
cycle through all of the folders and replace any of the 3 Excel files in each
folder.

I now need to update 2 fields ("Office", "CostCenter") in a single table.
This table only has the 2 fields and each field has a text string of "4.0".
I need to update each field with "5.0".

I'm trying to create a sql statement that will update /replace each field
with "5.0"

The database name is "MyDatabase". The table name is "Version". The field
names are "Office" and "CostCenter".

Any help getting started would be greatly appreciated. Thanks.......

 

[Jeff Boyce]

Take a look at Access HELP for an Update query.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks.....however, is it possible to change 4.0 to 5.0 programmatically?
Thanks..
--
JT

So, you're saying that every time your corporation gains or loses a cost
center, you create a new Access database? That sounds like ... a
spreadsheet!

You won't get the best out of Access' relationally-oriented
features/functions (e.g., SQL statements) if you feed it 'sheet data.

You might want to consider creating a single Access database and adding a
single field ([CostCenter]) to the table that I'm assuming is central to
your application. Plus, you'd need a tlkpCostCenter (a cost center lookup
table) -- that way, when you gain/lose a cost center, you make a single
change on this single lookup table...

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I have approximately 100 Access databases (one for each cost center). Each
cost center has their own folder on a corporate drive. There are 3 other
files (Excel) besides the database in each folder. I have a macro

that
will

cycle through all of the folders and replace any of the 3 Excel files

in
each

folder.

I now need to update 2 fields ("Office", "CostCenter") in a single table.
This table only has the 2 fields and each field has a text string of "4.0".
I need to update each field with "5.0".

I'm trying to create a sql statement that will update /replace each field
with "5.0"

The database name is "MyDatabase". The table name is "Version". The field
names are "Office" and "CostCenter".

Any help getting started would be greatly appreciated. Thanks.......

 

[John W. Vinson]

UPDATE [Version] SET [Office] = Replace("[Office]", "4.0", "5.0"),
[CostCenter] = Replace([CostCenter], "4.0", "5.0"
WHERE [Office] LIKE "*4.0*"
OR [CostCenter] LIKE "*4.0*";

Back up your database first, and check the results carefully.

John W. Vinson [MVP]