Relational issues

[Charlie]

Im trying to join two tables with a one to many relation,Im trying to join supplier code from the tblSupplier to supplier code on the tblinvoice.
Whenever or whatever I try Access will not allow me to join these, what is my problem?
Ive looked for hours but I cannot spot the error, apparently some data is different in one of the tables, it beats me which though.
http://homepage.ntlworld.com/charlie_chuck/Invoices02.mdb

Would someone put me out of my misery please.

Thanks
Charlie
This message was edited by Charlie on 2003-02-15 11:00

 

[Davepacey]

It worked Just fine when I downloaded the file and tried it.
The data types are the same, both are text fields, only difference I can see is that on tblsupplier, the required property is set to no, it is set to yes on tblinvoice, but it still works on my system. AccessXP running on windowsXP

 

[Charlie]

Try it with enforce referencial integrity checked, silly me forgot to mention that important detail.

Charlie

 

[Davepacey]

In the invoice table, record number 4 the supplier code is L312 it should be L321.
Change that and it will work

Regards,

Dave

 

[Charlie]

What a fool I am, do you know of any tools that can analyse and find such faults with relationships etc in Access?
One tool like that would come in very handy about now.

Thanks

Charlie

 

[Davepacey]

No, but if you set out the database before entering data, it would not let yopu enter anything that did not meet referential integrity. Also, set up correctly with subforms etc, the linked fields could be made to update automatically when you enter a new record. ie. if you made a data entry form based on your supplier table, then added a subform for creating an invoice, the subform would be linked on the supplier code field, this field in the new invoice record would be filled automatically.

Hope this helps.

Dave

 

[Charlie]

I would really like to know how to set up a database to not allow me to enter data not matching referential integerity, unfortunately I do not know how to do that. If anyone would point me to the settings needed for this I would be grateful.

Charlie

 

[Davepacey]

Expect an email shortly

Dave

 

[Charlie]

Appreciate it mate, that will save me a lot of time.

Charlie

 

[Davepacey]

No Problem

Dave