#  Other Applications & Softwares  > Access Tables & Databases >  >  Updating one table from another

## jik_ff

Ok, this is the first time I'm trying something like this, so I don't have a frame of reference to start from.  I hope I am explaining this properly:

I have a source of data for equipment.  This source cannot be changed by me (it's automated).  I have made a query to pull needed equipment information from it to generate reports/labels.

One of these sets of labels need added information added to it (for servicing and supplies), but this information cannot be added to the source.  I was hoping to be able to pull the needed data from the source (which I have) into a new table.  Then to this table add a few more fields (service and supply info), which again I have done.

The tricky part is that the source data does get updated (as new/old equipment is changed), and I would need these changes reflected in my new table.  Is it possible to update the table from the source without having to make a new table.  I would hope that the fields would stay.  I would like the new equipment to be appended to the end of the table, and deleted items (probably the tricky part) removed.

----------


## alansidman

I have a few questions. 
1. How are you bringing in the data from the outside source?  Are you currently importing the data or are you linking a table from the outside source?

2.  What type of data are you adding or deleting from the source in Access?  Is this being done through an expression in a query.  Joins to other tables?

3.  Is it necessary to save the amended/appended information in 2. above in a table?  If so, why?  Why wouldn't you just use the data in a query and run the query for whatever report/information is needed.

Could you be more specific about the information you are trying to work on and what the end result will be.  Perhaps if you could post your db with only sample non confidential data included it might be helpful.

Alan

----------


## jik_ff

Thanks for the quick reply.  The data orginates from an ODBC source.

As a first step, I have tried using the Make Table query to pull out the specific data that I need (model, serial number, asset number, etc.).  This data is created through a diffrent system that is updated as equipment is purchased/decomissioned.

So I have my new table.  To this new table, I need to add servicing info (such as service vendor, supplies needed, etc.).  It looks like I will have to manual enter this information for each item.

Now, when items are added/removed from the source, I would like these changes reflected in the table I have made/adjusted without losing the udated info added to the remaining data.

I hope this makes it more clear.

----------


## alansidman

Instead of doing a make table query, why don't you link your outside table to Access and then run your queries against the linked table.  I am still slightly confused.  Why do you have to manually adjust the table?  Why can't you join the additional data in a query.

If you link your outside table, then it will always be up to date as your source data is linked and not imported.  If the outside source data changes, then the information within Access is changed automatically.

Alan

----------


## jik_ff

The reason for creating the new table is because I need to add information specific to our region (meaning corperate wants this info tracked but does not want to play with the source tables, so this stuff is done manually in excel at this time).

To give an example, information I am importing will look like this:

 | Asset# | Serial # | Model | HBU | Install Date | EOL|

when printing the labels and reports, I need more info that this.  to this current field list, I would need to add:

 | Service Agreement | Supplies | Contact Info |

(there are more fields, but I hope you get the idea)

This latter info is what is being created manually, as the above data is pulled from the source into excel.  Each time we pull the data, we have to put back in the Service data manually, which is getting to be a pain.  We are about to make a large turn over of equipment, and I want to make the process less painful.

----------


## alansidman

Do you have a table that has all the data for each asset # that you can import into Access.  This is the type of information that is constant and unchanging such as the service agreement and contact information.  It seems to me that if that information is available, it should be placed in an Access table.  Then join that table to your ODBC table on a unique identifier either the asset number or serial number.  Run your query and distribute your information as necessary.  Am I missing something here.  Seeing data would certainly help.

Also, what is the end result.  Steven Covey has said in his book on Seven habits of highly effective people "Begin with the end in mind."  I am not sure what your end result is and how it is used.  This may help as you talking about your issue and not a resolution.

----------


## jik_ff

I guess I was thinking too complex...  You are right.  I could make a table that has the asset # that could be linked back to the source in a query, then output that to a report.

This would still mean maunally adding the asset #'s in the table.  I guess I can't get away from that.  My issue is knowing what has been added or removed.  I was hoping that I could have access do that for me.

My end result (one of the key ones) is a label that would go on our printers so that isolated site offices would know what to do/who to contact when problems (supplies or service) arise with the equipment.  As it is now, there are various avenues when it comes to service depending on where the equipment is, when it was bought and the model.

----------


## alansidman

> This would still mean maunally adding the asset #'s in the table. I guess I can't get away from that. My issue is knowing what has been added or removed. I was hoping that I could have access do that for me.



If you build the table once, then the it would be almost automatic from then on.  

If you really need to know what has been added or deleted, then do a make table query after joining the two tables.  Save the table with a unique name.  The next time you update, save the table with another unique name.  Now using the Query Wizard, do an unmatch query.  This will tell you everything that does not match on the two tables.

Alan

----------


## jik_ff

It seems a lot simpler that it started out to be.  Thanks for clearing that up.  Guess I should start with thinking small, then expand on it.  I might have been too focused on the final product...

Will try and set that up today, and let you know how it goes.  Thanks again.

----------


## alansidman

Good Luck and post back with any questions that you may have.
Alan

----------

