How to a update a field for several posts automatically?

[carolineJ]

Hi I have a silly problem that I can't solve. I think the problem is that I'm new to Access but an experienced Excel user so I think "too much Excel" when I try to solve my problem if you know what I mean?

I simply need to paste a column from an Excel file into the corresponding column in Access, i.e I want to update all posts in my table for a specific field, but not with the same data for all posts. Therefore I assume I can't use an update query? Hence I want to export data from Excel to an existing table in Access, but just ONE column.

How can I do this? I don't want to manually enter data for each post since it is several thousands of posts.

Any help very much appreciated. Please excuse my English. English is not my mother tounge.

 

[JoeMiller]

You can use an update query, you just need to define your criteria to do it. There are multiple ways of doing it. If you've got 2 possible values then you can use an iif statement. If it's an equation you can perform the equation. How about giving us an example of your data and the value that your interested in applying and how you make the decision what the value is? Then we could help more..
Joe Miller
joe.miller@flotech.net

 

[carolineJ]

Hi Mr Miller, thank you for your attemt to help me.

The thing is the values are not logical in anyway, i.e. they can't be calculated using a formula and if there are say 2000 posts, each post will have a unique value.

Anyway here is some background if You are still interested. It is quite complicated in a way, but on the other hand it is not!

I have a table in Access where the posts (rows) are my company's investment projects and the fields are facts about the projects (project manager, project name, project number etc). Some of the fields show the ACTUAL cost of the project for a specific month and some of them show ESTIMATED cost for a specific month.

For example for this year, I got 12 (jan-dec) columns for estimated cost and 12 columns for actual cost. I have also made a Query that calculates estimated project cost for the whole year by adding the actual/estimated cost for each month. I have told the Query to use actual cost for a month if it is available (i.e if it is not null) and to use estimated cost instead if actual cost for that month is not available yet.

For example the expected annual project cost for a specific project this month will be the sum of actual cost for jan-sep and estimated cost for oct-dec, since actual cost for the last three months of this year is not available yet.

By the end of the month I get actual cost for each project from our accounting system in an Excel file so when I get the data from October I want to fill in the column "actual cost October", so that my query uses actual cost for october instead of estimated cost when it calculates estimated cost for this year and so on.

Hope you get the point.

It seems silly that this problem can't be solved in an easy way. All I want to do is to get data from one column in Excel to a corresponding column in an Access table, without having to type it in manually.

 

[JoeMiller]

Well your data is set up improperly for one which is why you're having problems doing it in access. You shouldn't have a column for each month for each type (estimated vs actual). You SHOULD have 1 table (tblCosts?) that is related to a project table, and in that table you would have these fields:

ProjectID - linked to the correct project in your project table
CostDate - month(/year?) of this cost
CostType - Estimated or Actual
Cost - value of the cost

Then you'd be able to do calculations EASILY using access queries. I don't know if you have the time/desire/need to do this, but it's a suggestion.

That aside, it may be possible for you to copy/paste the column from excel into access if everything is sorted correctly and matches. Have you tried this?

HTH Joe Miller
joe.miller@flotech.net

 

[tempclerk]

In Access, under File, Get External Data, and Link your Excel spreadsheet. Then you can use it just like a table. In a new query, add your Excel table and the table you want to update, Join the tables on your Project ID or whatever your unique key is, drag the field you want updated to the query grid, change the query to an update query, and in the Update to box put the field name from your access table (You might want to use the builder to get the reference syntax right.)

 

[tempclerk]

Oops, you want to Update to the field name from your Excel table.

 

[carolineJ]

Mr Miller I don't really have time to set up data correctluy right now. I can't paste the column from the Excel table into the Access table, beacause when I try to do that, I only affects the first cell in the column.

tempclerk, your suggestion seems very interesting, but I'm not sure how to "join the tables on the project ID". Can you please explain how you do this?

 

[tempclerk]

Add both tables to the query in design view, and in the section above the grid, click on the ID field in one table and drag-and-drop it on the corresponding ID field in the other.

 

[carolineJ]

Excellent! It worked it worked! Thank you tempclerk!