Access gurus: Need help with Petrol Station database

[justinoh]

I have designed over 10 different databases and none work well. I'm exhausted with new ideas. I'll post the one which I think is the best here. Primary key will be bold.
Staff table: Staff_no, Staff_Name
Transaction table: Transaction_No, time, date, PumpID, Staff_no, Vol_Pumped
Pump table: PumpID, Petrol_Name, Price

Staff table to transaction table is 1 to many and transaction table to pump table is many to 1. I managed to generate a form for data entry purposes but then it ran into duplicate values in index, primary key or relationship error.

Guys, please help me out here. I desperately need help and am way behind schedule in completing this project. Without this database done, I can't proceed on with my documentation and report

Thanks in advance!

 

[EagleKeeper]

Nothing is wrong with the desgin of the table as you described.

Must be the form that you built.

 

[justinoh]

I can only add details for OC1 which is the first PumpID

In my form, PumpID is a combo box in the main form along with Petrol Name and Price. In the subform, I have Transaction_No, time, date, Staff No and Vol_Pumped.

I can't seem to add details for other PumpID's such as OC2,OC3 and so on. When I click on the subform area to fill in the details I run into duplicate values in index, primary key or relationship error.

Any ideas?

 

[EagleKeeper]

YGPM