duplicates

[lee123]

hi there i have a question, how can i find duplicates of items used by other customers...well to make this make sense i have a form i created to hold a lot of mats that goes out on a route. some of the same mats goes out with different drivers. well that being said, my question is if i wanted to find these mats and there different rt.drivers how would i make a query for this? on my form i have made a search to find mats im looking for and it pulls it up but in my search i want to make it if its a mat that different drivers use it will pull it up in a subform i will create. the code for the search is this.

Private Sub Text63_AfterUpdate()
 
     Dim mydata As String
 
     mydata = Text63  
     If mydata = "" Or IsNull(mydata) Then Exit Sub
     DoCmd.GoToControl "customername"  
     DoCmd.FindRecord mydata, acAnywhere, False, acSearchAll, , acCurrent, True
End Sub

can anyone modify this so when i search for these mats it will pull all the mats that are duplicates in the subform and the mats that are not in the database to pop up a msgbox that say's "there is no mats of that kind"


thanks,
lee123

[nico5038]

When you want to have insight of the mats for different drivers, you can create a GroupBy query to count the number of mats and add the number of drivers.
Those having > 1 driver are the ones you're interested in.
Guess just a subform with Mat and #drivers will solve the problem of your user.

What additional requirements do you have? It looks to me that only mats delivered on the same day are of interest, thus the GroupBy would also need to group on the deliverydate...

Nic;o)

[lee123]

thanks nico5038, for responding yea just the duplicates mats. question, in the criteria is where i put ( >1) and how do i adjust the code to only find those mats i seek for instant if i wanted to find a mat called (tammies) and three different drivers use this mat. how would i have the search find this mat and all of its id's
and then there is the msgbox to say if a mat don't exist or if they misspelled the mat.

lee123

[nico5038]

You need to open the graphical query editor and place the table with the tams and drivers.
Now press the "E" looking button and place Tam and Driver and the table unique ID (or another always filled field).
Under all fields you'll see "GroupBy" appear.
Change this under the ID into "Count".
Save this and see the effect.
Now remove the Driver field and test again.

These two tests will show the data needed. Under the ID you can place the >1 criteria.

Got it ?

Nic;o)