How to check database to see if there's a matching record...?

Posted on 2014-08-25
Last Modified: 2014-09-11
I have an access front end with a sql backend. I have jobs that have unique ID's, but there might be two or more jobs that have the same job number. I want to display a list box that shows up only if there are matching jobs. What syntax do I use (I'm assuming on load) to check for duplicates?

(side question: will this slow it down much on a db with about 3k records?)

Question by:Kevin Smith
    LVL 25

    Expert Comment

    by:Lee Savidge
    Which part do you want the syntax for? The SQL Server code or VBScript?

    If it's SQL you need to group on a count.

    select count(myIDColumn) from myTable
    group by myIDColumn
    having count(myIDColumn) > 1
    -- You could use the above to give you the list of IDs. If you wanted more from the table you could wrap it in another select
    select * from myTable where myIDColumn in
    select count(myIDColumn) from myTable
    group by myIDColumn
    having count(myIDColumn) > 1
    order by myIDColumn

    Open in new window

    Again, this depends on where you want the code to look for duplicates.

    Author Comment

    by:Kevin Smith
    The vb...I need to be on a form and for the list box to show the duplicate jobs in a list box based on the job number. It needs to list them 'cause they'll need to click in the list box to open the jobs if needed.
    LVL 84

    Accepted Solution

    The code for this would simply be to set the listbox's RowSource to the query that shows the duplicates, for example:

    Me.MyListbox.RowSource = "SELECT Field1, Field2, Field3 FROM YourTable WHERE JobNumber='" & Me.JobNumber & "'"

    You'd do this in the Current event of the form - that fires every time you move to a different/new record.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    732 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

    22 Experts available now in Live!

    Get 1:1 Help Now