Prevent duplicate data entry

[Newbie_me]

Hi guys,

Need help! I am just making a simple form on entering components with program numbers on different machines. In adding a new record, how can it prompt me if the new machine number i am entering on either machine1, machine2 or machine 3 is NOT a DUPLICATE program number(compared to other machines)?

Component	Machine1 Program Number	Machine2 Program Number	Machine3 Program Number
PD45	1245​	8547​	3125​
OP78	8446​	2156​	9998​
KL777	7889​	7598​	4561​

Thanks in advance!

 

[arnelgp]

the table is not Normalized.

you need table like:

program (table)
programID (autonumber) PK
programName (short text)

component (table)
companentID (autonumber) PK
componentCode (short text)
description (short text)

progCompo (junction table)
ProgramID (long, FK to table program)
componentID (long, FK to table component)

 

[Cronk]

@Newbie_me Does that mean that a particular component can never be used in more than one machine? I'd expect normally a component could be used in multiple machines. What is your system modelling?

 

[Newbie_me]

@Newbie_me Does that mean that a particular component can never be used in more than one machine? I'd expect normally a component could be used in multiple machines. What is your system modelling?

a component can be made in different machines, however a program code/number should always be unique. Hope i make sense

 

[Newbie_me]

the table is not Normalized.

you need table like:

program (table)
programID (autonumber) PK
programName (short text)

component (table)
companentID (autonumber) PK
componentCode (short text)
description (short text)

progCompo (junction table)
ProgramID (long, FK to table program)
componentID (long, FK to table component)

thanks for the reply. my problem is the table and the data are already existing. it's too complicated to me to reformat everything

 

[Cronk]

@newbie_me wrote a component can be made in different machines, however a program code/number should always be unique. Hope i make sense

So a particular machine(s) is used to produce the component.

I would normalize your database so you have a table of components and a separate table of machines used for each component
CompMachID Autonumber
ComponentID Long (FK to tblComponents)
MachineID Long (FK to tblMachines)

It's then a simple matter of adding a unique index of ComponentID | MachineID so that there can never be the duolicate component/machine combinations.

 

[arnelgp]

this is a sample of your validation.

 

[Galaxiom]

thanks for the reply. my problem is the table and the data are already existing. it's too complicated to me to reformat everything

Ultimately you will end up wasting more time trying to work around the denormalized structure than it would take to fix it now.

Your current structure has no future.