Results 1 to 8 of 8

Creating a query that search for duplicates but has an variable duplicate search amount

  1. #1
    Mrocks22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    5

    Creating a query that search for duplicates but has an variable duplicate search amount

    Hi,


    Sorry for the slightly confusing title. I've taken the following critera from duplicate search query:

    In (SELECT [Customer_id] FROM [Rental] As Tmp GROUP BY [Customer_id] HAVING Count(*)>1)

    From what I understand the >1 means that any duplicates more than one will be vaild for the critera of the query.
    How do I make the 1 a variable that can be inputted by a dialogue box?

    Also I would prefer simpler solutions as I don't know how to use Access that well.

    Thanks,
    Marcus

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,438
    try In (SELECT [Customer_id] FROM [Rental] As Tmp GROUP BY [Customer_id] HAVING Count(*)>[Enter Count])
    although I confess to never having tried to use a parameter prompt in what looks like a sub query.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  3. #3
    Mrocks22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    5
    I tried that but it doesn't work because the "In" fuction bit.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,438
    Then the only solution I can think of that should work involves vba, which you've indicated you don't want. If nothing else is offered and you change your mind, post back.
    Since you're only showing part of the sql I can't tell if you'd build a sql string and execute it in vba, or alter the query definition of your stored query, or what.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,362
    Dynamic parameter in the nested query works for me, even with the IN function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,438
    I don't think I have a dupes query with an IN clause so I can't test it, but it does work in the usual dupe query.

  7. #7
    Mrocks22 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    5
    Thanks everyone, Miron had the right answer, must have been my mistake when I was trying myself.
    Thanks, Micro.

    ...Now do I need to close this thread...or how do I indicate it's all good?

    Edit: Found the "Close thread under thread tools" Thanks

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,438
    Quote Originally Posted by Mrocks22 View Post
    Thanks everyone, Miron had the right answer, must have been my mistake when I was trying myself.
    Thanks, Micro.
    You're welcome, Mrocks2.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-30-2016, 03:41 AM
  2. Creating a multi variable Search Form
    By AccessUser12345 in forum Forms
    Replies: 3
    Last Post: 10-14-2016, 01:36 PM
  3. Replies: 8
    Last Post: 09-02-2015, 03:00 PM
  4. Replies: 2
    Last Post: 08-11-2014, 03:05 PM
  5. (How) Creating an Actual SEARCH Query?
    By ndb in forum Queries
    Replies: 3
    Last Post: 12-09-2013, 09:16 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums