Question Help a newbie?

[SophiaTryo]

Good afternoon.

I have a problem. Lol. Who doesn't?

Alright, I have 5 different databases that hold the same information on different computers in different offices. We are finally going to move to a network style of operation so I need to turn all 5 databases into a single database.

In each database there are tables that are all related -

1. Client Basic Information
2. Client Visits (tracked by client ID, multiple entries per client)
3. Client's Children (tracked by client ID, multiple entries per client)
4. Client's Lessons (tracked by client ID, multiple entries per client)
5. Client Purchases (tracked by client ID, multiple entries per client)

I have done basic import/export between databases before but my problem is each database has client ID auto numbers 1-500ish so I would end up with 5 clients with the ID number of 1,2,3 etc. How do I fix this in the first table and all the corresponding tables?

Each office's database has 500 clients at least and over 3,000 entries for each table 2-5. The nightmare of going through and changing client ID and the related tables by hand is terrifying. Can anyone suggest what I should do?

Thank you so much for your time!

- Tiffany

 

[bparkinson]

I would bring the client records into a new database. Allow the new client table to assign a new primary key (autonumber). Additionally, bring in the old primary key, and stick it in another ordinary (not autonumber) int column.

Do one database at a time, since you have duplicate ID's.

Next, bring in the subordinate records. Store the clientID_OLD FK in a temporary column. Have an empty clientID column Use the previous clientID to temporarily join them to the client records, then update the subordinate records with the new client.ID to be joined back on the new Client.ID

 

[SophiaTryo]

So I would enter the updated numbers one at a time in the empty field? (after doing the process you just explained?)

 

[bparkinson]

No need for that. I would make an updateable query for each subordinate table, and update it as a set, like "UPDATE qrySubordinate SET SubordinateRecord.ClientD = Client.ID"

So qrySubrdinate would be updateable and contain the client.ID in each row.

 

[SophiaTryo]

Thank you! You just saved me HOURS!!!!

 

[bparkinson]

Glad I could help. If you run into trouble, I'm subscribed to this thread, so just post another reply here and I'll see it.

 

[SophiaTryo]

BP-

I've been trying to figure this out and haven't quite got a handle on it.

I've got my tables transferred for the first database, my new column that's blank and my relationships established. But how do I get Access to recognize the old ID, match it with the new ID and put it in the empty column for the other tables?

I'm just not getting it.

I appreciate any help you can offer. If you could explain what I do when I'm looking at the update query design screen that would be most helpful. So far I have columns ID, Old Ct ID, New Client ID (for new tables).

I'm such a newbie...

 

[bparkinson]

BP-

I've been trying to figure this out and haven't quite got a handle on it.

I've got my tables transferred for the first database, my new column that's blank and my relationships established. But how do I get Access to recognize the old ID, match it with the new ID and put it in the empty column for the other tables?

I'm just not getting it.

I appreciate any help you can offer. If you could explain what I do when I'm looking at the update query design screen that would be most helpful. So far I have columns ID, Old Ct ID, New Client ID (for new tables).

I'm such a newbie...

I just use Access as a front end, so I don't write any Access queries, all my SQL is in T-SQL on SQL Server, so I'm not the best person to help with the specifics of writing queries in Access. I would just write the SQL by hand, making the appropriate joins.

If you upload your database, I can take a look and possibly propose some SQL that you could paste into the Access query editor.

 

[SophiaTryo]

BP-
Thank you for your help. I self taught myself on just the basics so SQL seems a little out of my reach yet. Attached you will find my zipped basic db. I removed all the confidential info so many columns are blank on purpose.
I can't tell you how much I appreciate your help.

Thanks!

- ST

 

[bparkinson]

OK, I've looked at your tables. Here's how to do what I think you want.

Create a query, called BaseQuery, with the following SQL:

SELECT [Client Information].ID, [Client Information].Previous_ID, [Child Information].Previous_ParentID, [Child Information].[New _ParentID]
FROM [Client Information] INNER JOIN [Child Information] ON [Client Information].Previous_ID = [Child Information].Previous_ParentID;

Save the query.

Create a blank form. Add a command buttom. In the command button's Click event, enter the following VBA code:

DoCmd.RunSQL "Update BaseQuery SET [New _ParentID] = ID"

You'll see a dialog asking you to confirm that you are about to update n number of rows. Tell it to do so.

Open the Child Information table. You'll see that the New _ParentID column is now populated. Note that you have a space before the underscore in that column name.

 

[SophiaTryo]

You are absolutely and completely amazing and you totally and utterly made my weekend great! Thank you so much for blessing me with your time! I appreciate you so much!

I will have no problem at all editing this to work for the other tables.

Thank you so much!

- Tiffany

 

[gemma-the-husky]

sophie

when you have done all this - you need to decide how to use it. how will the reomte offices connect to the single Head office datastore. you will probably find you will not get adequate performance over a WAN network, and you will have the added cost/issues of needing to set up a terminal server, or something similar.

 

[bparkinson]

Glad I could help, Sophia