Copy a table with only distinct values of a column

Access 2013 desktop database Windows 7
I have two tables which are identical in structure (just field1 and field2, both simple numerics)
I need to copy the rows from table 1 which has a unique value of field1 ( just use the first row of duplicate values of field1)
I should be able to do this in one command something like :
Insert * into Table 2 from (Select DISTINCT * from Table1) ;  
Can't get it quite to work.  How do I specify I only want the distinct part to be against field1.
I've gotten this to work using VBA and scanning a recordset doing selected insert commands but it's very slow for 15,000 records
Example
Table 1
1000 10
1000 11
1000 12
1001 40
1002 50
Result
Table2
1000 10
1001 40
1002 50
Once again, your help is most appreciated.
LVL 4
Keyboard CowboyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Rey Obrero (Capricorn1)Commented:
try this query against table1

select field1, min([field2])
from table1
group by [field1]
Keyboard CowboyAuthor Commented:
Ray - I don't understand - what would be full statement.  Whoever min(field2) would probably work if ...from Table1 ORDER by field1.  Do I need brackets if the field names don't have any spaces?
Rey Obrero (Capricorn1)Commented:
<Do I need brackets if the field names don't have any spaces? > 
NO, but
you may have to use brackets if the name of field have special characters.


select field1, min([field2])
from table1
group by [field1]
order by [field1]
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This should work:

INSERT INTO Table2.FieldName SELECT DISTINCT Table1.FieldName

If you have multiple fields:

INSERT INTO Table2Field1, Table2.Field2 SELECT DISTINCT Table1.Field1, Table1.Field2
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry, my last won't work, since it would get every record in Table2 (since all of them are distinct with both fields).
Rey Obrero (Capricorn1)Commented:
the query i posted above

select field1, min([field2])
from table1
group by [field1]
order by [field1]

is just a select query, to create a new table, in the design view of the query, just select the Make Table in the Query Type

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.