Relationship problem

[Charlie2]

I am trying to create one to many relationships in this database file I am currently experimenting with and cannot get the thing to link the way i want. Could someone give me some advice on it please.
I was trying to maintain referential Integerity, but have found it impossible.


Thanks
Charlie

 

[Mile-O]

Can you post a '97 version or even a screen shot of your relationships?

 

[neileg]

I presume you want to link the Staff Member field in your Purchases table with the Staff# in your Staff table. Since you are going to have many purchase by each staff member this is going to be a many (Staff) to one (Purchases).

However, there's no obvious field to link Suppliers to Purchases. Presumably, you need to create a Supplier field in your Purchases table.

I have to say that your design is going to ned a lot of work to make it functional. For instance, you will have problems holding payments in your Suppliers table. I suggest you have a good look at Northwind Traders before you go any further with this.

Good luck!

 

[Charlie2]

Ok heres a screenshot of the relationships I have tried.

Charlie

 

[Mile-O]

What data types are Staff# in the table Staff and Staff Member in the table Purchases?

I'm presuming that Staff# is an autonumber.

 

[Charlie2]

Nope

They are both Text.

Any help to you ?

Charlie

 

[Mile-O]

Now, it would be better to have a StaffID (autonumber) as your primary key and in the other table also have a field called StaffID which is a numerical field and linking them in a one to many relationship as your current structure won't allow for multiple staff members with the same name.

It would also be better to break down your staff name into StaffForename and StaffSurname, a query - when necessary - can concatenate the names for you.

 

[Charlie2]

Thanks

Im going to try that right now, fingers crossed

Charlie

 

[Mile-O]

Also, have a look at the sample database I put on this thread.

 

[Charlie2]

Success

Ive taken a look at the way you structured yours in the thread, I must say mine is now much better and works, thank you so much.
I must remember to follow the basic structure you have used there in future projects.

Charlie