Update query help needed

[Support]

I have two databases with the following schema (main fields named only).

I need to merge them into one database & I am a little stuck on how best to
proceed.



PK= Primary Key/ FK= Foreign Key


DB1

DB2







tblCustomers

tblCustomers


g_user_id

g_user_id


source_customer_id
PK
order_group_id

PK
new_user_id

order_number


order_group_id




order_number









tblOrders

tblOrders


customer_guid
FK/PK
order_group_id


source_customer_id

order_number

FK
new_user_id




order_group_id




order_number



PK
new_order_id









tblOrderlines

tblOrderlines


order_number
FK
order_id


order_id



FK
new_order_id









DB1 is comprised of 2 differing databases from 2 different order systems
which is why there is some duplication

DB2 is the new ordering system and is the final design I am intending to aim
for.



On DB1, I have a 1:Many relationship between tblCustomers and tblOrders. I
have a 1:many relationship between tblOrders and tblOrderlines



On DB2, I have a 1:1 between tblCustomers and tblOrders and 1:many between
tblOrders and tblOrderlines.





I think I should aim to get the new_order_id of tblOrders (DB1) to also be
present in tblCustomers of DB1.



(NB: To create new_order_id in tblOrders I created a combination of
order_group_id and order_number. I cannot do this in tblCustomers as the
data is different)



So in order to do this I have done an update query where:



UPDATE tblCustomersDB1 INNER JOIN tblOrdersDB1 ON
tblCustomersDB1.New_User_ID = tblOrdersDB1.New_User_ID SET
tblCustomersDB1.NewOrder_ID = tblOrdersDB1!NewOrder_ID;



The problem I have is that there are 61459 records in tblCustomers (DB1) and
89649 records in tblOrders (DB1). I know that this is because a customer
has more than one order so I effectively also need to update the
tblCustomers.new_users_id with more entries (and take off the PK) and do the
update above.



How do I do this?

Thanks.

 

[Ken Snell [MVP]]

Your post appears to have lost its formatting, so your structures are not
easily discerned.

However, before we consider your request about how to merge the data, are
you absolutely sure that you want a 1:1 relationship betwen tblCustomers and
tblOrders? That means you need a record in tblCustomers for each record in
tblOrders. That seems to be a peculiar database design for such models.

A more usual structure is 1:many for tblCustomers to tblOrders, and a 1:many
for tblOrders to tblOrderLines.

 

[Support]

Hi Ken,
Here is the data structure again hopefully in a visible format:

DB1 DB2
* tblCustomers * tblCustomers
g_user_id g_user_id
source_customer_id PK order_group_id
PK new_user_id order_number
order_group_id
order_number

* tblOrders * tblOrders
customer_guid PK/fk order_group_id
source_customer_id order_number
fk new_user_id
order_group_id
PK new_order_id

* tblOrderlines * tblOrderlines
order_number fk order_id
order_id
fk new_order_id


DB1 is old data made up of 3 different order systems:
1) our old mailorder system
2) our old web ordering system
3) present mail/web ordering system

I will be receiving updated data from our web partners in the format of DB2
so it is easier for me to use that structure.
I agree with you that it makes more sense that one customer has many orders
consisting of many orderlines unfortunately the new system doesn't seem to
be designed like that. So, in the instance above for DB2, unfortunately it
does mean that I have duplicate customers appearing in the customers table.
I think it would be quite a lot of work to try and change this but I could
easily be wrong!

I hope this makes things clearer?
Rob

 

[Ken Snell [MVP]]

I'll take a look at this more closely a little later today, but I reiterate
that the proposed 1:1 structure is going to cause problems for you in
queries and data entry and data maintenance.

How will users choose/enter company data? How will you avoid slight errors
from one record to another in the tblCustomers table for the same customer?

How will you ensure that a change for one customer gets written to every
tblCustomers record for that company?

Queries will be problematic in many cases.

Better to change the structure now, before you put data into it. Just my
strong recommendation to you.
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

I will give you some general concepts to work from.

Essentially, what you will need to do is build queries that will enter the
data just as if it were brand new. In other words, based on a 1:1
relationship, I probably would read the tblOrders data from the incoming
database, and write that record to the new database's tblOrders. I then
would find the matching tblCustomers data for that order in the imcoming
database, and write a record to the new database's tblCustomers table.

And so on.