Adding field to table and updating data

Acropolis

Registered User.
Local time
Today, 23:42
Joined
Feb 18, 2013
Messages
182
Hi I am sure there is a simple way to do this, but I cant for the life of me think what it is.

I have a table in my DB called, devices. This contains all the information about various devices we have deployed in the field.

These devices are also contained in 2 other separate MySQL DB's.

What I need to do is add 2 additional field to my access table for the DeviceRecno and DeviceID of the same device from the MySQL DB's.

Adding the field is easy, but I cant think of a way to enter the recno and ID from the other DB's without typing them in manually for each one.

The common between them all is the serial number of the device, and I can get a list of serial numbers, recno's and ID in an excel sheet.

Could someone suggest an easy, say like a vlookup in excel to easily populate all the existing records with the recno and id's from the other db's?

I am fully aware that the set up is a little strange to say the least, it is something that has happened through a multitude of reasons and is going to be looked at shortly and re-done to be in a much better way.

When I created the access system there was no intention to link it to the other DB's for any reason, but that has now changed due to a lot of reasons.

Any suggestions would be most helpful.

Thanks
 
A few questions regarding your set up:

You have info about devices in 2 separate databases, and are about to put it in a third database. Why?
What is the purpose of each database? Who are the users of each?
If combining databases was not planned, isthere a plan now for all 3 databases?

If these databases are important (to scheduling, invoicing, sales or whatever) which one is the authoritative database? How can you trust the numbes/data in any or all of these databases?
Is there some sort of synchronizing scheme somewhere? Should there be?

Analogy:
Suppose your bank had its accounts spread across a number of non-synchronized databases. And suppose the account in question is yours or your company's. If a deposit is made, where would it be recorded? If a withdrawal is made, which database is used? How do you know your balance at any time?
 
There are no users as such with the 2 existing DB's. They are there to allow the devices, which are data loggers to transmit and receive their data.

My access system was set up as a stand alone stock and job management system.

Due to many reasons, too many to go into now, it needs to be linked to the other DB's to make some changes when devices are swapped out. As such it needs the unique ID's for the devices from the other DB's against the entries in my access DB, this is what I would like some help with. I can add the fields, but don't really feel like sitting there and typing thousands numbers in.

I know the way this is set up is far from the best, this is something that has been inherited and has ended up this way through necessity unfortunately. Plans are been put in place to rectify the problems and change the structure to something far more suitable, but for now this is the way it is and what I have to deal with.
 
Do you plan to link the databases and do the "swap devices" processes via Access?

It seems, at least from 30,000 feet, that you will need a SWAP table along the lines:

tblSwap to identify swap,DeviceIDOut,DeviceIDIn,SwapDate....

It also seems you would have a list of Devices that are available for Swapping In. When you Swap a Device OUT (of active service) for repair or whatever, you would SwapIN an available "swappable device".

Don't know if that helps, but that's what your description suggests to me.
Good luck with your project.
 
What I am planning on doing is adding some extra functionality to the Access DB in as much as when stock is issued through the system, as well as updating the Access tbl with where the stock has gone, new fields are going to be added to the other DB's that I will be getting Access to update as well at the same time through a SQL command that will run.

All I need to do is update the field new fields I will add in the Access device tbl with the ID and RecNo from the other DB's. Can anyone suggest an easy way of doing this without having to type them all in. I could do it through another table, but I would rather not.
 

Users who are viewing this thread

Back
Top Bottom