Solved

Finding Duplicate Records

Posted on 2004-08-19
13
237 Views
Last Modified: 2006-11-17
I have an MS Access form that I'm using for a search interface where users select fields from drop down boxes and type their criteria in a text box, which is pretty basic.  When they click the Search Button I read their input and build it into a filter string (i.e. Field1 like 'userinput' AND Field2 like 'userinput2' ... etc. ).  I then use the Docmd.Appyfilter to filter my records based on the filter string.

However, I am now trying to let the user input a only a number and a field.  This number correlates to the number of times data in that field must be repeated.

For example, lets say in my table I have a field called "Field1", and the user enters 10 as the number.  I need to build a filter string that will return only the records that have the data in Field1 repeated 10 times.  

I can do this by looping through the recordsets, however that is not what I'm trying to accomplish.  I'm trying to do it by building a filter string so that I can do it all in one command.

Any help would be greatly appreciated....
0
Comment
Question by:jg0069
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 11845054
What do you mean- records that have the data in Field1 repeated 10 times - each record can only have 1 Field1, right? Are looking for records where at least 10 fields have the same value as Field1 (or a search string)?

-ef
0
 
LVL 4

Expert Comment

by:MrXmas
ID: 11845075
jg0069,

Is it a specific item that you want to count?  For instance, are you letting the user specify that the field must equal "Jim" ten times, or are you letting them query for anything that is repeated ten times in that field?

You may want to consider switching from using your filter to building the SQL that feeds the form dynamically.  You'll get far more flexibility out of an SQL statement than you can ever get from building a filter to tack on in the form.

Jim Christmas
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 200 total points
ID: 11845150
For the OnClick event of your search button, try something like this:

Private Sub SearchButton_Click()

Me.RecordSource = "SELECT * FROM MyTable WHERE " & _
                              FieldComboBox.Text & " IN (SELECT " & FieldComboBox.Text & " FROM MyTable GROUP BY " & _
                              FieldComboBox.Text & " HAVING count(*) >= " & NumberTextBox.Text & ")"
Me.Requery

End Sub
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:jg0069
ID: 11845192
Mr Xmas --

That is sort of what I'm trying to do, however there is not a specific value that I'm looking for.  I want to look at all the records in the table and determine which ones have values in Field1 that are repeated X number of times.

I'm trying to it using a filter string or SQL string as suggested by routinet above.  




0
 
LVL 4

Expert Comment

by:MrXmas
ID: 11845411
jg0069,

routinet is your man.  He's got the exact SQL you need.  Well done.
Use his code and attach the remainder of your filter to the WHERE clause in the filter.

Jim Christmas
0
 

Author Comment

by:jg0069
ID: 11854452
Thanks the responses guys... Here's another interesting twist.... I'm trying to add it to my existing SQL statement which is shown below:

Me.RecordSource= "Select tblGSAP_Equip.EquipNum, tblGSAP_Equip.TechID, tblGSAP_Equip.ObjectType, tblGSAP_WorkOrders.WorkCenter, tblGSAP_WorkOrders.Priority, tblGSAP_WorkOrders.SystemStatus, tblGSAP_WorkOrders.WorkCenter, tblGSAP_WorkOrders.WorkOrderNum, tblGSAP_WorkOrders.WorkOrderType, tblGSAP_WorkOrders.FunctionalLocation, tblGSAP_WorkOrders.EquipmentID, tblGSAP_WorkOrders.CreatedDate, tblGSAP_WorkOrders.Module, tblGSAP_WorkOrders.Unit, tblGSAP_WorkOrders.Cost, tblGSAP_WorkOrders.Category, tblGSAP_WorkOrders.ShortText, tblGSAP_WorkOrders.SubCategory, tblGSAP_WorkOrders.AutoNumber FROM tblGSAP_WorkOrders LEFT JOIN tblGSAP_Equip ON tblGSAP_Equip.EquipNum = tblGSAP_WorkOrders.EquipmentID"

The SQL Statement show above is a little complicated and is the one that I am using to initially load the records.  Notice that I'm using a LEFT JOIN for two tables.  

Question one is instead of listing all those fields in the Select Field1, Field2, Field3, etc portion of the SQL statement how can I say I want to select all the fields from Table1 and all the fields from Table2 and then Left Join on a certain field.

Question two is how would I incorportate the new syntax suggested by routinet into my SQL statment.  I'm sure its not that complicated but it looks that way with all the different fields listed and the left join.

Again, thanks for the help guys...
0
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 11854763
Select Table1.*, Table2.* will select all fields from both files - you can still refer to individual fields in the Join and Where clauses.

-ef
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11855142
How do the user-supplied values in the combo and text boxes relate to your existing SQL?  For example, does the field selection correspond to a particular existing field in your query?  You'll need to explain a little more about your project for this part.

As far as grabbing all the fields, eflamm has the right of it.  Here's an edited version of the SQL you posted changed to include all fields from both tables.  The aliases were not necessary, but I put them for your reference.

Me.RecordSource= "Select a.*,b.* FROM tblGSAP_WorkOrders as a LEFT JOIN tblGSAP_Equip as b ON b.EquipNum = a.EquipmentID"
0
 

Author Comment

by:jg0069
ID: 11855358
Yes that is right... The user supplied values are a field and a number which I will pass into the SQL statment.  I am now trying to figure out how to incorporate the syntax that you posted earlier into my code.  

Here's what I've got so far:

me.recordsource = "Select tblGSAP_Equip.*, tblGSAP_WorkOrders.*, COUNT(EquipmentID) " & _
    "FROM tblGSAP_WorkOrders " & _
    "LEFT JOIN tblGSAP_Equip ON tblGSAP_Equip.EquipNum = tblGSAP_WorkOrders.EquipmentID " & _
    "GROUP BY [tblGSAP_WorkOrders].[EquipmentID] " & _
    "HAVING COUNT([tblGSAP_WorkOrders].[EquipmentID]) >=4"

I've just hard coded the field and the number into the SQL statement to make it all easier to understand, as its eay to pass a variable name in later.  I ran the code above and got an error saying "Cannot Group on Fields selected with a '*' (tblGSAP_Equip)".

So, I figured it had something to do with the new .* syntax that I was using for the two tables, so I went back to the old syntax of listing the field names seperatly:

Me.Recordsource = "Select tblGSAP_Equip.EquipNum, tblGSAP_Equip.TechID, " & _
    "tblGSAP_Equip.ObjectType , tblGSAP_WorkOrders.WorkCenter, " & _
    "tblGSAP_WorkOrders.Priority , tblGSAP_WorkOrders.SystemStatus, " & _
    "tblGSAP_WorkOrders.WorkCenter , tblGSAP_WorkOrders.WorkOrderNum, " & _
    "tblGSAP_WorkOrders.WorkOrderType , tblGSAP_WorkOrders.FunctionalLocation, " & _
    "tblGSAP_WorkOrders.EquipmentID , tblGSAP_WorkOrders.CreatedDate, " & _
    "tblGSAP_WorkOrders.Module , tblGSAP_WorkOrders.Unit, " & _
    "tblGSAP_WorkOrders.Cost , tblGSAP_WorkOrders.OrderClass, " & _
    "tblGSAP_WorkOrders.ShortText , tblGSAP_WorkOrders.DBStatus," & _
    "tblGSAP_WorkOrders.AutoNumber, COUNT(EquipmentID) " & _
    "FROM tblGSAP_WorkOrders " & _
    "LEFT JOIN tblGSAP_Equip ON tblGSAP_Equip.EquipNum = tblGSAP_WorkOrders.EquipmentID " & _
    "GROUP BY EquipmentID " & _
    "HAVING COUNT(EquipmentID) >=4"

I ran the code above and got an error saying "You tried to execute a query that does not include the specified expression 'EquipNum' as part of an aggregate function"....

So, I think we're on the right track... just think I'm missing something...




0
 

Author Comment

by:jg0069
ID: 11855519
Alright, the above comment is a bit much... After a little bit of frustration, I decided to go back to the basics of the HAVING COUNT and GROUP BY, and wrote the code below:

me.recordsource = "Select EquipmentID, COUNT(EquipmentID) " & _
    "FROM tblGSAP_WorkOrders " & _
    "GROUP BY EquipmentID " & _
    "HAVING COUNT(EquipmentID) >=4"

The code above works just fine, however when I try to select for data fields it doesn't seem to work.  The code is below:

strRecordSource = "Select Priority, EquipmentID, COUNT(EquipmentID) " & _
    "FROM tblGSAP_WorkOrders " & _
    "GROUP BY EquipmentID " & _
    "HAVING COUNT(EquipmentID) >=4"

The error that I get is "You tried to execute a query that does not include the specified expression 'Priority' as part of an aggregate function"....

So, how does one select multiple data fields and still use the HAVING COUNT and GROUP syntax??
0
 
LVL 8

Assisted Solution

by:Eric Flamm
Eric Flamm earned 50 total points
ID: 11855589
It's worse than you think - when you aggregate with Group By or Having  - EVERY field in the select must appear either in an aggregate function (SUM, COUNT, etc) or in the GROUP BY phrase. So this is probably NOT what you want to do. You really need to do a subquery to get the Equipment ID's  for records with Count>4, then select the details (without aggreagting) from the WorkOrders table for those Equipment ID's:

Select tblGSAP_Workorders.*, tblGSAP_Equip.* from
tblGSAP_Workorders left join tblGSAP_Equip on tblGSAP_Equip.EquipNum = tblGSAP_WorkOrders.EquipmentID
 where
(tblGSAP_Workorders.EquipmentID in (SELECT tblGSAP.EquipmentID from tblGSAP GROUP BY tblGSAP.EquipmentID where COUNT(tblGSAP.EquipmentID>=4)))

-ef
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11855699
yupyup!  That's roughly the idea.  This should be a working line of code for you, based on the original.  You will still need to substitute in the values from the textboxes.

me.recordsource = "SELECT a.*, b.* FROM tblGSAP_WorkOrders a LEFT JOIN tblGSAP_Equip b on a.EquipmentID = b.EquipNum " & _
                            "WHERE a.EquipmentID IN (SELECT EquipmentID FROM tblGSAP_WorkOrders GROUP BY EquipmentID HAVING count(*)>4)"

0
 

Author Comment

by:jg0069
ID: 11856349
Thanks for all your help... I finally got it working and here is the final code:

Me.RecordSource = "Select tblGSAP_Equip.*, tblGSAP_WorkOrders.* " & _
        "FROM tblGSAP_WorkOrders " & _
        "LEFT JOIN tblGSAP_Equip ON tblGSAP_Equip.EquipNum = tblGSAP_WorkOrders.EquipmentID " & _
        "WHERE (tblGSAP_WorkOrders.EquipmentID in " & _
            "(SELECT tblGSAP_WorkOrders.EquipmentID " & _
            "FROM tblGSAP_WorkOrders " & _
            "GROUP BY tblGSAP_WorkOrders.EquipmentID " & _
            "HAVING COUNT(tblGSAP_WorkOrders.EquipmentID) >=" & Me.txtBadActor & ")) " & _
            "AND (Len(Trim(tblGSAP_WorkOrders.EquipmentID))>0) " & _
            "AND ((IsNull(tblGSAP_WorkOrders.EquipmentID))=False) " & _
        "ORDER BY tblGSAP_WorkOrders.EquipmentID"

I threw in a few other WHERE Conditions to throw out the values that are blank...

Thanks again for all your help....
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question