S
Support
I have a database containing 2 tables:
tblOrders
tblOrderlines
Within each of these tables I have two fields:
Order Number eg 000624-1022
Ordergroup id eg FFFB2264-3FF8-4D77-8D34-18A1332BFAD01
These tables are the result if two merged databases which I do not have data
from.
I believe that:
In one old database Order Number was the pk in tblOrders and fk in
tblOrderlines
In the other database Ordergroup ID was the pk in tblOrders and fk in
tblOrderlines
I have created an update query to merge the two fields to a new field
(NewOrder_ID) in each table:
UPDATE Orders SET Orders.NewOrder_ID = [Ordergroup id] & "-" & [Order
Number];
This has populated field NewOrder_id correctly and there are no NULL values
in this new field.
tblOrders.NewOrder_id is the pk and tblOrderlines.NewOrder_ID is the fk
I have created a relationship between the two tables however have 2 problems
1) the keys do not seem to be linking.
For example, I have a value in tblOrders.NewOrder_ID of
000436FB-A41D-420C-9D5A-08E7C0E8F1C3-5040421
however this shows no data linked in tblOrderlines. Yet, when I look in
tblOrderlines.NewOrder_ID i can see that value
000436FB-A41D-420C-9D5A-08E7C0E8F1C3-5040421 is present and there is order
data present.
2) cannot enforce referential integrity which I beleive is because of the
above link problem.
I've done a mismatched query and the results don't add up as I can see the
relevant data in both tables.
Why would this be happening?
Thanks
tblOrders
tblOrderlines
Within each of these tables I have two fields:
Order Number eg 000624-1022
Ordergroup id eg FFFB2264-3FF8-4D77-8D34-18A1332BFAD01
These tables are the result if two merged databases which I do not have data
from.
I believe that:
In one old database Order Number was the pk in tblOrders and fk in
tblOrderlines
In the other database Ordergroup ID was the pk in tblOrders and fk in
tblOrderlines
I have created an update query to merge the two fields to a new field
(NewOrder_ID) in each table:
UPDATE Orders SET Orders.NewOrder_ID = [Ordergroup id] & "-" & [Order
Number];
This has populated field NewOrder_id correctly and there are no NULL values
in this new field.
tblOrders.NewOrder_id is the pk and tblOrderlines.NewOrder_ID is the fk
I have created a relationship between the two tables however have 2 problems
1) the keys do not seem to be linking.
For example, I have a value in tblOrders.NewOrder_ID of
000436FB-A41D-420C-9D5A-08E7C0E8F1C3-5040421
however this shows no data linked in tblOrderlines. Yet, when I look in
tblOrderlines.NewOrder_ID i can see that value
000436FB-A41D-420C-9D5A-08E7C0E8F1C3-5040421 is present and there is order
data present.
2) cannot enforce referential integrity which I beleive is because of the
above link problem.
I've done a mismatched query and the results don't add up as I can see the
relevant data in both tables.
Why would this be happening?
Thanks