Access Date Query

[Juliette]

Hi I have a table with numerous stock codes which are duplicated because the date raised against them is different. I have been asked to provide the earliest date that a record was created for each stock code.
Any idea how to do this.

Thanks
Juliette

 

[almahmood]

Hi,

Try this:
Select StockCode, Min([Date]) from YourTable GROUP By StockCode

 

[Ranman256]

If you only need the code and date,it's 1 query.
If you need all fields in the record,Make 2 queries,

Q1 gets the Code and earliest date:
Select Code,Min([Date]) from table

To get all fields, Q2 uses Q1:
Select table.* from table,Q1 where Q1.code=table.Code and Q1.date=table.date

 

[Juliette]

How do I do this via Design View.
Juliette

 

[Juliette]

Below is a snippet of 20,000 records. As you can see the stock code appears numerous time with different dates. I just want to show the earliest date for each stock code.
Job StockCode JobDescription Warehouse
000000000100752 10374 SM ASSY:M100X LAMPHEAD SUB 12/05/2015
000000000101382 10374 SM ASSY:M100X LAMPHEAD SUB 02/06/2015
000000000102301 10374 SM ASSY:M100X LAMPHEAD SUB 29/06/2015
000000000102394 10374 SM ASSY:M100X LAMPHEAD SUB 30/06/2015
000000000102867 10374 SM ASSY:M100X LAMPHEAD SUB 13/07/2015
000000000103304 10374 SM ASSY:M100X LAMPHEAD SUB 28/07/2015
000000000103305 10374 SM ASSY:M100X LAMPHEAD SUB 28/07/2015
000000000104023 10374 SM ASSY:M100X LAMPHEAD SUB 20/08/2015
000000000104113 10374 SM ASSY:M100X LAMPHEAD SUB 25/08/2015
000000000105139 10374 SM ASSY:M100X LAMPHEAD SUB 29/09/2015
000000000106007 10374 SM ASSY:M100X LAMPHEAD SUB 02/11/2015
000000000107266 10374 SM ASSY:M100X LAMPHEAD SUB 08/01/2016
000000000107321 10374 SM ASSY:M100X LAMPHEAD SUB 08/01/2016
000000000107322 10374 SM ASSY:M100X LAMPHEAD SUB 08/01/2016
000000000109069 10374 SM ASSY:M100X LAMPHEAD SUB 09/03/2016
000000000109254 10374 SM ASSY:M100X LAMPHEAD SUB 11/03/2016
000000000110830 10374 SM ASSY:M100X LAMPHEAD SUB 04/05/2016
000000000111625 10374 SM ASSY:M100X LAMPHEAD SUB 31/05/2016
000000000112572 10374 SM ASSY:M100X LAMPHEAD SUB 23/06/2016
000000000113970 10374 SM ASSY:M100X LAMPHEAD SUB 01/08/2016
000000000114158 10374 SM ASSY:M100X LAMPHEAD SUB 02/08/2016
000000000114810 10374 SM ASSY:M100X LAMPHEAD SUB 23/08/2016
000000000114993 10374 SM ASSY:M100X LAMPHEAD SUB 26/08/2016
000000000115399 10374 SM ASSY:M100X LAMPHEAD SUB 12/09/2016
000000000116513 10374 SM ASSY:M100X LAMPHEAD SUB 05/10/2016
000000000117995 10374 SM ASSY:M100X LAMPHEAD SUB 17/11/2016
000000000118734 10374 SM ASSY:M100X LAMPHEAD SUB 09/12/2016
000000000120994 10374 SM ASSY:M100X LAMPHEAD SUB 15/02/2017
000000000121751 10374 SM ASSY:M100X LAMPHEAD SUB 08/03/2017
000000000122273 10374 SM ASSY:M100X LAMPHEAD SUB 27/03/2017
000000000120132 10475 SM ASY:XY VEX REC/SUS/SURF LED SUB 13/01/2017
000000000120375 10475 SM ASY:XY VEX REC/SUS/SURF LED SUB 20/01/2017
000000000122702 10475 SM ASY:XY VEX REC/SUS/SURF LED SUB 06/04/2017
000000000115279 10475 SM ASY:XY VEX REC/SUS/SURF LED SUB 07/09/2016
000000000114939 10475 SM ASY:XY VEX REC/SUS/SURF LED SUB 25/08/2016
000000000112910 10475 SM ASY:XY VEX REC/SUS/SURF LED SUB 04/07/2016
000000000110492 10475 SM ASY:XY VEX REC/SUS/SURF LED SUB 21/04/2016
000000000110055 10475 SM ASY:XY VEX REC/SUS/SURF LED SUB 13/04/2016

 

[almahmood]

Hi Juliette,

Check this out

http://prntscr.com/eyqpk5

 

[Juliette]

Thanks all sorted now.
Cheers
Juliette