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

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?)

Thanks!
Kevin
Kevin SmithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
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.
0
Kevin SmithAuthor Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.