mdb to mdb

[bhu]

i have an mdb with lot of tables in it. But now since the old mdb is not so flexible the way user wanted, the requirement is creating a new mdb with different tables some of them matches the way the old table but some it is combination of fields from more than table in old mdb.


For example old.mdb table name: associate Fields : first name, last name. personid
table name: Network Fields : network id, person id

New mdb
table name associate: firstname, lastname, personid, networkid
How do i do it?

[HiTechCoach]

How to you do what? A specific question on how to do something with Access would really make it a lot easier for us to help you.

Can you explain in more detail what you are having trouble doing?

[Dököll]

i have an mdb with lot of tables in it. But now since the old mdb is not so flexible the way user wanted, the requirement is creating a new mdb with different tables some of them matches the way the old table but some it is combination of fields from more than table in old mdb.


For example old.mdb table name: associate Fields : first name, last name. personid
table name: Network Fields : network id, person id

New mdb
table name associate: firstname, lastname, personid, networkid
How do i do it?

Hello bhu!

Have you yet attempted an update query?

Fetch here or Google search if you do not know, may find some fancy, fairly easy tutorials through Microsoft's sites.

Are you comfortable setting up a query?

An cheap an easy alternative could be to load the data right into your new database table (with both database open)...Copy and paste. You'd need to make sure table columns are set up the same way in both, proper formats and so on.

Let us know.

In a bit!

Dököll

[sierra7]

If you really want to do what you say (I am presuming this is just an example) then there are two approaches you can use;-
1. Add the new field to the Associates table and then update it with a query from the networks table, as Mr Dokoll is suggesting (sorry I dont have umlauts on my kb)

2. Alternatively, create a 'Make Table' query (in Old.mdb) , run it to create a NewAssociates table, then open New.mdb and import the new table from Old.mdb, rename it and check indexes. Sound more lengthy but leaves initial data intact.

However, whichever you decide you must first you must confirm (analyse) you 'physical' situation. One presumption is that you have a list of 'associates' some of whom have a NetworkID. You must then confirm that an associate may NOT HAVE more than one NetworkID, otherwise your data must stay as it is. (Why has the original database designer written it like this??) You may also need to confirm that for each NetworkID record there is a valid PersonaID value, otherwise the NetworkID is of no value and can be lost. etc.

You should also run 'Find Duplicate' checks on the network table to ensure that there are no repeats of Person ID or NetworkID, because it sounds like a 'JOIN' table to me.

Taking the situation at face value and that you have a list of associates, some of whom have networkID's, start by creating a Select query joining the two tables on [PersonID]. You will need to edit the join type so that you are selecting ALL records from the Associates table. When you run this query and check you are seeing the correct data you can go back into design mode and change the query type to MakeTable. You will then be prompted for a new table name.When you run the query next time the data will be copied into the new table.

However, the more I think about your problem the more concerned I am that you may be about to 'de-normalize' the data which is bad for performance, bad for flexibility (i.e. restricts an associate to only one NetworkID or one telephone number etc, when it may be valid to have more than one) and your forms may stop working because they are based on queries

Best of luck