Prevent duplicate data entry (1 Viewer)

[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)

 

[Uncle Gizmo]

If you have only just started with your data entry, then you should be able to create and fill the new tables easily.

However, if you have already entered a significant amount of data then you might be interested in my transpose tool.

If that's the case, then I would need to see a copy of your database with just a few sample records in it. I would then be able to tell you if the transpose tool would work on your data.

The tool, and a description of how to use it is here:-

Normalization Tool - Nifty Access

Normalization Tool Introducing the "Transpose Tool" or "Database Normalization Tool," your go-to solution for seamlessly converting spreadsheet-structured data into MS Access-structured data. This transformation not only equips you to construct exceptionally powerful MS Access databases, but is...

www.niftyaccess.com

AWF members get a free copy.

 

[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

 

[Uncle Gizmo]

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

Well it might be possible to to write some code to check through each text box but then which record are you checking? Is it single record? Are there multiple records? In other words I have no idea until I can see what you've actually got. You're going to have to post a copy of your Form to the forum. Only provide a few sample records, and remove any confidential information. Post the bare minimum to demonstrate the problem.

 

[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.

 

[Uncle Gizmo]

Your current structure has no future.

Well, more like a future of pain, frustration and constant visits to forums, hoping that someone will write some convoluted solution to get your badly constructed database to work how you want. We all know, we've all been down the road you're going down!

 

[Pat Hartman]

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

Really? We are unanimous in believing that you need to start with fixing the normalization problem. Once the table is properly normalized, the solution is to create a compound index. This has to be done using the indexes dialog. Creating unique indexes in the column properties creates a unique index on a single field and cannot create a unique index on multiple fields. The GUI does allow you to create a primary key comprised of multiple columns but it is far better to leave the autonumber as the PK and use that for all relationships than to work with multi-field relationships .

The way to create multi-field indexes when using the dialog is by giving the first row an index name and then leaving the index name blank on subsequent lines. Access supports up to 10 fields in a PK or index. Other RDBMS' support up to 32.

To do this with your current structure, you need to create THREE separate unique indexes. Component + machine1, Component + machine2, Component + machine3.

But DO NOT DO THIS. Fix the problem. ALWAYS take the time to fix foundational problems. You wouldn't want to live in a highrise that had foundation issues would you? I don't care how much of the app is built. Fix the problem.