Solved

Finding Duplicate Records

Posted on 2004-08-19
13
233 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
  • 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now