Case sensitive link

[Aerowolf]

I have the following tables: Customers, ShipTo, Orders

In the shipto table, I have the following shipto ids for one customer: 66S & 66s (note the capital and lower-case S)

When I have my order screen up and I bring up an order for shipto id 66s, I get 2 copies of the same order, one for each shipto id.

How can I make sure the link between orders and shipto finds the right shipto id?

Edwin

 

[danvlas]

Change the primary key, THE foreign key and the relationship.

While code can be made case-sensitive, SQL can't (AFAIK). So both ships will be interpreted in the same way...




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Aerowolf]

The primary key in my ShipTo table is and autonumber field because I have many customer ids with many shipto ids. How would I change my table around to make the ShipTo ID the primary key when I have duplicate IDs?

Edwin

 

[danvlas]

I'm not sure I understand...
66S should be treated as 66s or not?

What are the relationships? Which are the primary keys, which are the foreign keys?

And...what joins do you have in your query?




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Aerowolf]

My ShipTo table has autonumber as the primary key, and it has the fields CustomerID and ShipToID. The Orders table also has CustomerID and ShipToID. One particular customer has ShipToIDs 66S and 66s. The link between my Orders table and the ShipTo table is through the CustomerID and the ShipToID.

When I want to look at the order for ShipToID 66s, or I print a report of orders, I get two identical orders, one for ShipToID 66s AND one for ShipToID 66S.

Edwin