How to sort Duplicate Data in Field

[shieriel]

Please help me..

I have a current database and i want to sort duplicate records in a query. Example: I want to show all records having the same job order.

Is this possible? And is it also possible to restrict a specific field for a specific data ( no duplicates ).

 

[vbaInet]

You can sort it by the ID or whatever field you wish to use.

For no duplicates, see this:
http://office.microsoft.com/en-us/access/HP010322051033.aspx

 

[shieriel]

Thanks for reply. But i want to elaborate my inquiry:
Sample:
ID | Job order | Time | Name
(autonumber)| 123456 | 11:00 | Peter

The example above shows field for specific data. How can i make it that the job order cannot entered twice. If i already entered that data and my co-worker mistakenly entered the same data, an error will occur saying that the data is already been entered.

 

[MStef]

In the table on the field "Job order" put an index with Unique = Yes.

 

[vbaInet]

If you're getting an error then that means it's not getting saved correct? So what are you actually trying to prevent? Here are links on error handling:

http://baldyweb.com/ErrorTrap.htm
http://www.cpearson.com/excel/ErrorHandling.htm

 

[shieriel]

If you're getting an error then that means it's not getting saved correct? So what are you actually trying to prevent? Here are links on error handling:

http://baldyweb.com/ErrorTrap.htm
http://www.cpearson.com/excel/ErrorHandling.htm

I am preventing my co-workers to input data twice, based on job order. Any way i will try your suggestions when i get back to work tomorrow as my db is at my office' computer. Mayber i will follow up questions tomorrow.....thanks

 

[vbaInet]

It is unlikelt that you get a message but it still gets saved. Have a look at what MStef says and do it on the table.

You can also handle it with code by using the DLookup() function to find in your table whether that ID already exists. You would implement that code in the BEFORE INSERT event of your form.

Let us know which works for you.

 

[shieriel]

In the table on the field "Job order" put an index with Unique = Yes.

Hello. I tried your suggestion but i got an error saying,
"The changes you requested to the table were not succesful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicated data, or redefine the index to permit duplicate entries and try again."

I already have data on my table maybe this is why i cannot change the index for job order/work order.

 

[shieriel]

It is unlikelt that you get a message but it still gets saved. Have a look at what MStef says and do it on the table.

You can also handle it with code by using the DLookup() function to find in your table whether that ID already exists. You would implement that code in the BEFORE INSERT event of your form.

Let us know which works for you.

Hi, sorry but honestly i am a newbie in access and i just learn it by myself. Can you please give me example on how to use dlookup function for this?
Thank in advance..

 

[vbaInet]

Maybe you can show what you've done?

 

[shieriel]

Maybe you can show what you've done?

Hi, please see attached image when i tried to change the "index" value of "work_Order" from "No" to "yes(no duplicate)".

 

[vbaInet]

Have a look at this:
http://baldyweb.com/BeforeUpdate.htm

 

[MStef]

Hello shireirel!
Yes, this error means that you already has the records with th same "Job order" in your table.
You can do next:
1) Find a duplicate (Job order) records and delete them.
2) Or, make a new table (empty, the same as your tbl), put an index on
"Yob order" unique = Yes, and make an Append query which will append the records from your table to a New tbl. Run this query.

 

[shieriel]

Hello shireirel!
Yes, this error means that you already has the records with th same "Job order" in your table.
You can do next:
1) Find a duplicate (Job order) records and delete them.
2) Or, make a new table (empty, the same as your tbl), put an index on
"Yob order" unique = Yes, and make an Append query which will append the records from your table to a New tbl. Run this query.

Hi mstef. That is another problem i am facing right now. I am a self-learner in access and i dont know how to find duplicate records. I also dont know how to use the append query. Can you please send me some example for this? many thanks in advance...

 

[MStef]

Hello shireiel!
Look at "DemoAppendqryA2000.mdb" (attachment, zip).
Look at TableA, (Index1, unique = Yes), TableB, Query1.
The Index1 in TableA means "not permit the two same records".
When you run a Query1 it'll add only 3 records from TableB in TableA.
Make the something same in your mdb.

 

[shieriel]

hi mstef! Allow me to study the demo. Thanks!

 

[MStef]

Repetitio est mater studiorum.

 

[shieriel]

Hello shireiel!
Look at "DemoAppendqryA2000.mdb" (attachment, zip).
Look at TableA, (Index1, unique = Yes), TableB, Query1.
The Index1 in TableA means "not permit the two same records".
When you run a Query1 it'll add only 3 records from TableB in TableA.
Make the something same in your mdb.

Hi MStef. I already made an append query for my database. And out of 837 records on my database only 830 were appended in the new table i created which means that there are possible 7 duplicated entries. My question is how can i extract those duplicate entries so i can double check again the data? Is it possible that only those duplicate entries be shown?

 

[shieriel]

~up...any inputs here?

 

[MStef]

You can not to have a duplicate records in the new table if you put an index, (unique = Yes).