Access relationship issue

[boredpilot]

OK been pulling my hair out for 3 hours so now time to ask for help.

Im setting up a simple database for a company with many machines, each machine has a uniqe number so is the primary key. Im trying to create a second table so I can keep a record of machine repairs, etc since there can be many repairs per machine

Currently they look like below

Now when I try to link machine number to machine number it always gives it a one to one relationship where as I need a one to many.

Any ideas help much appreciated
Neil

 

[boredpilot]

Just realised the picture is very small would have edited first post but cannot find edit button
this should be bigger

As the detail isnt fab the machine name in table 2 is the last entry, exactly the same as in table one bar its not the primary key

Thanks again
Neil

 

[PHV]

So, [Company number] is the Primary Key of Machines table and should be a Foreign Key in Services table.
Did you create an index allowing duplicates for the FK ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[boredpilot]

Thanks for the reply, INDEX if thats the index you find in the design view of tables then both the machines table and the service table have both Company Number as INDEXED Yes (No duplicates)

 

[boredpilot]

PHV, Big thankyou ive twigged that in the second table it doesnt want to be set to no duplicates.

Thankyou for steering me the right way

 

[ceh4702]

maybe run a query looking for the key field and list them in order. Can you say select XXXX not unique.

If you had a duplicate it may not let you change the option to unique. I am not a big fan of keys like these. could just generate a numbered unique key that is not the serial number. What if someone made a mistake and tagged two items with the same number and entered that in by mistake or entered something in twice.

Could be like an autonumber field.

If you do not like my post feel free to point out your opinion or my errors.

 

[boredpilot]

I did originaly think about having a auto number but then I realised since each machine has its own unique code that would surfice.

Having it this way so a tech can only put in the machine code once stops any potential errors of the same machine code appearing, (This is mostly licence and financial reasoning) since if we dont pay a licence we could take a hefty penalty for not doing so, so the company needs to be right on this one.

Thanks for the input though