How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 37692
Experience:  Over 15 year experience resolving Microsoft Office Issues
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

I need to create a query that will extract all of the records

This answer was rated:

I need to create a query that will extract all of the records in a table with a unique value from a field with duplicate values.
Hi there and welcome

So to understand you want the query to make a unique id if it finds two of the same records in the tablet?
Customer: replied 4 years ago.

The "Incident number" field has many duplicate incident numbers, which represent different pieces of equipment responded to an emergency call. I want to create a table that just pulls each unique incident number with it's other corresponding information to create a new table.

Thanks Chuck

Than use this

Select * from tablet where incidentnumber in(select min(incidentnumber) from table)

Let me know if you have any problems please
Customer: replied 4 years ago.

Is this what you are talking about:


(select min (MODIFIED INCIDENT NO) from EMSOnlyCalYear2012)


I had to pull the "MODIFIED INCIDENT NO" down into a second query field as the * field doesn't allow criteria


yes that is it

Customer: replied 4 years ago.

I get a "syntax error" when I try to run it.

Syntax error in query expression '(((EMSOnlyCaiYear2012.[MODIFIED INCIDENT NO]}=(select min (MODIFIED NCIDENT NO) from EMSOnlyCaiYear2012)))'.

is this Microsoft Access? which version?
Customer: replied 4 years ago.

Yes, Access 2010

Can you upload the database here

And give me the download link

there are soo many factors that can cause this, it is easier if I do the query in access for you

Customer: replied 4 years ago.
ok, I see the problem

You want to just select one incident number right? But which one is the correct one?
Customer: replied 4 years ago.

It doesn't matter as long as it just selects one incident number and excludes the duplicates

ok, I need to figure it out by going though the data

I am going offline now, can I resume this tomorrow?

Or would you like an other expert to take over?
ignore that, worked it out

uploading now
Customer: replied 4 years ago.

OK I'll await the download link.

here you go

Let me know if you have problems please

Richard and 4 other Microsoft Office Specialists are ready to help you