Determining earliest date

[Guest]

Hi,

I have a table with multiple invoice numbers I am trying to make a table
containing only the rows with the earliest record completed date.

ex. Current Table
Invoice# Date (Many Other Fields)
Invoice 1 12/9/2000 MOF1
Invoice 1 1/1/1999 MOF2
Invoice 1 3/28/2007 MOF3
Invoice 2 1/4/2007 MOF4
Invoice 2 2/14/2007 MOF2
Invoice 2 2/15/2007 MOF2
Invoice 3 12/9/2000 MOF1
Invoice 3 3/28/2007 MOF3
Invoice 3 4/18/2007 MOF2

I would like my results to be

New Table
Invoice 1 3/28/2007 MOF3
Invoice 2 2/15/2007 MOF2
Invoice 3 4/18/2007 MOF2

Would someone assist me please.

 

[Allen Browne]

1. Create a query.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. In the Total row under Invoice#, choose Group By

4. In the Total row under [Date], choose Min.

5. In the Total row under other fields, choose First.

For alternative approaches, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

 

[Guest]

Allen,

Thank was a great help.

Thank You,

Melody

1. Create a query.

2. Depress the Total button on the toolbar.
Access adds a Total row to the design grid.

3. In the Total row under Invoice#, choose Group By

4. In the Total row under [Date], choose Min.

5. In the Total row under other fields, choose First.

For alternative approaches, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a table with multiple invoice numbers I am trying to make a table
containing only the rows with the earliest record completed date.

ex. Current Table
Invoice# Date (Many Other Fields)
Invoice 1 12/9/2000 MOF1
Invoice 1 1/1/1999 MOF2
Invoice 1 3/28/2007 MOF3
Invoice 2 1/4/2007 MOF4
Invoice 2 2/14/2007 MOF2
Invoice 2 2/15/2007 MOF2
Invoice 3 12/9/2000 MOF1
Invoice 3 3/28/2007 MOF3
Invoice 3 4/18/2007 MOF2

I would like my results to be

New Table
Invoice 1 3/28/2007 MOF3
Invoice 2 2/15/2007 MOF2
Invoice 3 4/18/2007 MOF2

Would someone assist me please.