Solved

Best way to Modify all (or batch) items within a table(s) inside local Access database...Excel?

Posted on 2014-10-19
10
185 Views
Last Modified: 2014-10-21
With a local Access database, what is the best way to make > 100 modifications to one or more tables?

Example: 30 minutes needs to be added to each entry within a particular table.
Example: A new column needs to added and values placed in each field based up the value in another field.
Example: Comparing field values in one table, and verifying the value (or name) exists within another table.

Each table within the database has less than 15,000 records.
Is using Excel the best option?  If so how would that be done?

Thanks,
Cook09
0
Comment
Question by:Cook09
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 39

Assisted Solution

by:als315
als315 earned 200 total points
ID: 40390599
Best way is update queries
you can add 30 minutes with this query:
UPDATE YourTable SET YourTable.YourField = DateAdd("n",30,[YourField]);
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 40391784
Also, you'll need to explain a bit more about the second example:

A new column needs to added and values placed in each field based up the value in another field.
Adding columns can be done with DAO or SQL, but the concept of one field being dependent on another can cause troubles ...

Comparing field values in one table, and verifying the value (or name) exists within another table.
Generally this is done with queries or straight SQL, but you'd have to flesh this out a bit more. What happens if you do NOT verify that the values exists in another table, for example? It's fairly simple to determine whether the value exists; the challenge is generally what to do with that information ...

Is using Excel the best option?
IMO, it would be rare in the extreme for Excel to be the best choice for working with Access-based data, especially if you're in need of schema changes. Access is far better suited for things of this nature than is Excel.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 175 total points
ID: 40392092
Example: A new column needs to added and values placed in each field based up the value in another field.

Open in new window

If one column can be derived from another it does not belong in the table as a column.  It should be calculated as it is needed in a query.  Moving from Excel to a relational database such as Access means you need to think about data a little differently.  Do some reading on at least the first three normal forms.

Example: Comparing field values in one table, and verifying the value (or name) exists within another table.
In a relational database this would normally be done by creating relationships and enforcing referential integrity.  That way you don't have to verify anything, the database engine will prevent bad data from being stored.
0
 

Author Comment

by:Cook09
ID: 40392327
The comparing values in one table is based upon the database not being "cleaned-up" in a while. The local version of Access just serves to pass data to a SQL database, when it gets updated in the evening.

For this request, an example would be:

Entries made in Table A, using variable "X," sets in motion certain actions within a third party application.   However, if variable "X" is not also located in Table Z, then the actions from Table A, which is using "X" will not occur.  So, I would like to verify that the variables used in Table A are also in Table Z.

Now, I know that Table A should not accept variable "X," or the values that accompany "X", if it is not in Table Z. This was put together long ago, and is how the database is currently being used.  I've seen values in Table A, and then checked to see if they are also in Table Z, and it wasn't.  So, all the data that is associated with variable "X" is now just taking up space in Table A.

Hope this makes sense.

As far as adding a new column and placing items based upon the value of another field.  I would think this should be straightforward, but don't know.  The column, most likely, will already be set up, so the code would not do that portion.  

But, for example, if the new column name is called "Region, and the reference column is named "State,"  then for each row,  if the field under "State" has the name of "Texas," then the intersecting field in the column under "Region" would be inserted with a value of 123AB.   If it is another "State," it would be assigned with a different value.  Since, there are over 5,000 rows, this could get tedious doing it manually.

I may not be explaining this correctly, so I'll further clarify in areas that are unclear.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 175 total points
ID: 40392374
You have a two part problem.
1. Clean up
2. Future prevention

Clean up is done with queries.  If you are not familiar with SQL, use the QBE to help you build the necessary update queries.  For the first example, add both tables to the grid.  Draw the join line that connects the two tables on PK to FK.  In the Where line for the field you want to compare add something like:

Is Null or <> tbl2.fieldname

This query will return rows were the values for matching rows do not have the same value in the column you want to verify.

The second will be an update query.  Add the table to the grid.  Select the column you want to update (it must already exist).  In the Update To line add the name of the column you want to copy or an expression that calculates some value

tbl1.fldname
OR
tbl1.fldname * tbl1.fldname2

Once the data is cleaned up. Create the proper relationships and enforce RI to prevent future errors.  In the form where you add data to the table, add code to populate the duplicate column in the Form's BeforeUpdate event.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Cook09
ID: 40393163
In attempting the initial query, there is one database called tblroutedetail, with one table named tabledetail.

The query written is:
SELECT tbldetail.routedeltime
FROM tblroutedetail
UPDATE tabledetail SET tbldetail.routedeltime = DateAdd("n",30,[routedeltime]);

Open in new window

The Field Name is routedeltime and it is set for Date/Time.
The error received is within the FROM clause.

What else needs to added to this query?
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 200 total points
ID: 40393606
Try to use Query Editor, most errors will be corrected.
If this query will be started inside tblroutedetail DB, you should correct query to:
SELECT tbldetail.routedeltime
FROM tbldetail
UPDATE tabledetail SET tbldetail.routedeltime = DateAdd("n",30,[routedeltime]);

Open in new window

0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 40394423
You don't really need the SELECT part of that query. Just use this:

UPDATE tabledetail SET tbldetail.routedeltime = DateAdd("n",30,[routedeltime]);

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40394776
As I mentioned earlier, rather than guessing about query syntax, use the QBE.  if you want to look at the generated SQL or copy it to paste it into a VBA procedure, you can switch to SQL view.
0
 

Author Closing Comment

by:Cook09
ID: 40395067
Well for some reason the initial comments were deleted as it went back to my Home page. The last comment by Scott, which was the first comment by Als315, did what I needed from that one process.
To summurize, thank you for the help and guidance.  I did purcase an Access 2010 book, as it looks like there is a lot more to learn, and will no doubt be back with additional questions.

Cook09
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now