Getting a single column to only show one record.

Hello,

I have a table that shows:
Serial Number Date Returned
2333005Y 8/15/2006
2333005Y 7/18/2003
2333005Y 1/1/2004
2333005Y 7/5/2006
2333005Y 11/24/2005
2333005Y 9/8/2005
2333005Y 9/8/2005
2333005Y 11/24/2005
2333014Y 7/18/2007
2333014Y 9/6/2005
2333014Y 7/23/2003
2333014Y 10/1/2001
2333014Y 1/25/2004
2333014Y 8/31/2007
2333014Y 9/6/2005
2333014Y 1/5/2006
2333014Y 1/25/2004
2333022Y 11/19/2006
2333022Y 8/22/2002
2333022Y 6/26/2006
2333022Y 1/1/2004
2333022Y 9/2/2003
2333022Y 1/1/2004
2333022Y 9/2/2003
2333022Y 8/22/2002
2333040Y 8/26/2002
2333040Y 11/20/2002
2333040Y 7/20/2003
2333040Y 4/26/2004
2333040Y 1/1/2004
2333040Y 5/29/2006
2333040Y 11/10/2003
2333040Y 1/1/2004
2333040Y 4/26/2004
2333040Y 8/26/2002

and so on. I want it to make a query that shows only one entry for the
serial number and match it with the most recent date there.So the
resulting query looks like this.

Serial Number Date Returned
2333005Y 8/15/2006
2333014Y 8/31/2007
2333022Y 11/19/2006
2333040Y 5/29/2006

and so on. I cannot figure out how to do this. Distinct is for each
row, and the Max function for the Date didn't seem to work, although I
might be doing it wrong.

Any help you could provide would be greatly appreciated.

Thank you,

Re: Getting a single column to only show one record.

Try

SELECT [Serial Number], Max([Date Returned])
FROM YourTable
GROUP BY [Serial Number]

You may have problems with the date as 9/8/2005 could be 9th August or 8th
September depending on how your system proccesses dates

Phil

"colemanj4" wrote in message
news:471be2f3-24df-4560-a314-c0a95bae4862@e23g2000prf.google groups.com...
> Hello,
>
> I have a table that shows:
> Serial Number Date Returned
> 2333005Y 8/15/2006
> 2333005Y 7/18/2003
> 2333005Y 1/1/2004
> 2333005Y 7/5/2006
> 2333005Y 11/24/2005
> 2333005Y 9/8/2005
> 2333005Y 9/8/2005
> 2333005Y 11/24/2005
> 2333014Y 7/18/2007
> 2333014Y 9/6/2005
> 2333014Y 7/23/2003
> 2333014Y 10/1/2001
> 2333014Y 1/25/2004
> 2333014Y 8/31/2007
> 2333014Y 9/6/2005
> 2333014Y 1/5/2006
> 2333014Y 1/25/2004
> 2333022Y 11/19/2006
> 2333022Y 8/22/2002
> 2333022Y 6/26/2006
> 2333022Y 1/1/2004
> 2333022Y 9/2/2003
> 2333022Y 1/1/2004
> 2333022Y 9/2/2003
> 2333022Y 8/22/2002
> 2333040Y 8/26/2002
> 2333040Y 11/20/2002
> 2333040Y 7/20/2003
> 2333040Y 4/26/2004
> 2333040Y 1/1/2004
> 2333040Y 5/29/2006
> 2333040Y 11/10/2003
> 2333040Y 1/1/2004
> 2333040Y 4/26/2004
> 2333040Y 8/26/2002
>
> and so on. I want it to make a query that shows only one entry for the
> serial number and match it with the most recent date there.So the
> resulting query looks like this.
>
> Serial Number Date Returned
> 2333005Y 8/15/2006
> 2333014Y 8/31/2007
> 2333022Y 11/19/2006
> 2333040Y 5/29/2006
>
> and so on. I cannot figure out how to do this. Distinct is for each
> row, and the Max function for the Date didn't seem to work, although I
> might be doing it wrong.
>
> Any help you could provide would be greatly appreciated.
>
> Thank you,

Re: Getting a single column to only show one record.

Hey,

That works... kind of. If I just take those 2 fields then it works,
and shows me the proper information. But if I add any other Fields to
be shown, it doesn't do it properly anymore. Is that because the other
fields can be different? I didin't think it would matter when I asked
so I never mentioned them.

The Fields are:

Order Number (each unique serial number can have more then one Order
number)
Customer (each unique serial number can have duplicate Customers)
Distributor (each unique serial number can have duplicate
Distributors)

I am using Access 2002 if that matters as well.

Thanks for your help.


On Dec 21, 2:01 pm, "Phil Stanton" wrote:
> Try
>
> SELECT [Serial Number], Max([Date Returned])
> FROM YourTable
> GROUP BY [Serial Number]
>
> You may have problems with the date as 9/8/2005 could be 9th August or 8th
> September depending on how your system proccesses dates
>
> Phil
>
> "colemanj4" wrote in message
>
> news:471be2f3-24df-4560-a314-c0a95bae4862@e23g2000prf.google groups.com...
>
> > Hello,
>
> > I have a table that shows:
> > Serial Number Date Returned
> > 2333005Y 8/15/2006
> > 2333005Y 7/18/2003
> > 2333005Y 1/1/2004
> > 2333005Y 7/5/2006
> > 2333005Y 11/24/2005
> > 2333005Y 9/8/2005
> > 2333005Y 9/8/2005
> > 2333005Y 11/24/2005
> > 2333014Y 7/18/2007
> > 2333014Y 9/6/2005
> > 2333014Y 7/23/2003
>
> 2333014Y 10/1/2001
> > 2333014Y 1/25/2004
> > 2333014Y 8/31/2007
> > 2333014Y 9/6/2005
> > 2333014Y 1/5/2006
> > 2333014Y 1/25/2004
> > 2333022Y 11/19/2006
> > 2333022Y 8/22/2002
> > 2333022Y 6/26/2006
> > 2333022Y 1/1/2004
> > 2333022Y 9/2/2003
> > 2333022Y 1/1/2004
> > 2333022Y 9/2/2003
> > 2333022Y 8/22/2002
> > 2333040Y 8/26/2002
> > 2333040Y 11/20/2002
> > 2333040Y 7/20/2003
> > 2333040Y 4/26/2004
> > 2333040Y 1/1/2004
> > 2333040Y 5/29/2006
> > 2333040Y 11/10/2003
> > 2333040Y 1/1/2004
> > 2333040Y 4/26/2004
> > 2333040Y 8/26/2002
>
> > and so on. I want it to make a query that shows only one entry for the
> > serial number and match it with the most recent date there.So the
> > resulting query looks like this.
>
> > Serial Number Date Returned
> > 2333005Y 8/15/2006
> > 2333014Y 8/31/2007
> > 2333022Y 11/19/2006
> > 2333040Y 5/29/2006
>
> > and so on. I cannot figure out how to do this. Distinct is for each
> > row, and the Max function for the Date didn't seem to work, although I
> > might be doing it wrong.
>
> > Any help you could provide would be greatly appreciated.
>
> > Thank you,

Re: Getting a single column to only show one record.

Problem is if you add say an order no and group on that, you get a new line
of information for each order no. If you are trying to get the details
corresponding to that derial no and date, create a second query based on
your orders and also the query that I suggested. Join the 2 Date Returned
fiels and the 2 Serial Number Fields

No guarantees, but it should work

Phil

"colemanj4" wrote in message
news:fc383331-d06d-421a-a0b5-1533c66deba4@i29g2000prf.google groups.com...
> Hey,
>
> That works... kind of. If I just take those 2 fields then it works,
> and shows me the proper information. But if I add any other Fields to
> be shown, it doesn't do it properly anymore. Is that because the other
> fields can be different? I didin't think it would matter when I asked
> so I never mentioned them.
>
> The Fields are:
>
> Order Number (each unique serial number can have more then one Order
> number)
> Customer (each unique serial number can have duplicate Customers)
> Distributor (each unique serial number can have duplicate
> Distributors)
>
> I am using Access 2002 if that matters as well.
>
> Thanks for your help.
>
>
> On Dec 21, 2:01 pm, "Phil Stanton" wrote:
>> Try
>>
>> SELECT [Serial Number], Max([Date Returned])
>> FROM YourTable
>> GROUP BY [Serial Number]
>>
>> You may have problems with the date as 9/8/2005 could be 9th August or
>> 8th
>> September depending on how your system proccesses dates
>>
>> Phil
>>
>> "colemanj4" wrote in message
>>
>> news:471be2f3-24df-4560-a314-c0a95bae4862@e23g2000prf.google groups.com...
>>
>> > Hello,
>>
>> > I have a table that shows:
>> > Serial Number Date Returned
>> > 2333005Y 8/15/2006
>> > 2333005Y 7/18/2003
>> > 2333005Y 1/1/2004
>> > 2333005Y 7/5/2006
>> > 2333005Y 11/24/2005
>> > 2333005Y 9/8/2005
>> > 2333005Y 9/8/2005
>> > 2333005Y 11/24/2005
>> > 2333014Y 7/18/2007
>> > 2333014Y 9/6/2005
>> > 2333014Y 7/23/2003
>>
>> 2333014Y 10/1/2001
>> > 2333014Y 1/25/2004
>> > 2333014Y 8/31/2007
>> > 2333014Y 9/6/2005
>> > 2333014Y 1/5/2006
>> > 2333014Y 1/25/2004
>> > 2333022Y 11/19/2006
>> > 2333022Y 8/22/2002
>> > 2333022Y 6/26/2006
>> > 2333022Y 1/1/2004
>> > 2333022Y 9/2/2003
>> > 2333022Y 1/1/2004
>> > 2333022Y 9/2/2003
>> > 2333022Y 8/22/2002
>> > 2333040Y 8/26/2002
>> > 2333040Y 11/20/2002
>> > 2333040Y 7/20/2003
>> > 2333040Y 4/26/2004
>> > 2333040Y 1/1/2004
>> > 2333040Y 5/29/2006
>> > 2333040Y 11/10/2003
>> > 2333040Y 1/1/2004
>> > 2333040Y 4/26/2004
>> > 2333040Y 8/26/2002
>>
>> > and so on. I want it to make a query that shows only one entry for the
>> > serial number and match it with the most recent date there.So the
>> > resulting query looks like this.
>>
>> > Serial Number Date Returned
>> > 2333005Y 8/15/2006
>> > 2333014Y 8/31/2007
>> > 2333022Y 11/19/2006
>> > 2333040Y 5/29/2006
>>
>> > and so on. I cannot figure out how to do this. Distinct is for each
>> > row, and the Max function for the Date didn't seem to work, although I
>> > might be doing it wrong.
>>
>> > Any help you could provide would be greatly appreciated.
>>
>> > Thank you,
>

Re: Getting a single column to only show one record.

Wonderful, thanks a tonne. I just couldn't seem to wrap my head around
why it wouldn't work for me.


On Dec 21, 3:21 pm, "Phil Stanton" wrote:
> Problem is if you add say an order no and group on that, you get a new line
> of information for each order no. If you are trying to get the details
> corresponding to that derial no and date, create a second query based on
> your orders and also the query that I suggested. Join the 2 Date Returned
> fiels and the 2 Serial Number Fields
>
> No guarantees, but it should work
>
> Phil
>
> "colemanj4" wrote in message
>
> news:fc383331-d06d-421a-a0b5-1533c66deba4@i29g2000prf.google groups.com...
>
> > Hey,
>
> > That works... kind of. If I just take those 2 fields then it works,
> > and shows me the proper information. But if I add any other Fields to
> > be shown, it doesn't do it properly anymore. Is that because the other
> > fields can be different? I didin't think it would matter when I asked
> > so I never mentioned them.
>
> > The Fields are:
>
> > Order Number (each unique serial number can have more then one Order
> > number)
> > Customer (each unique serial number can have duplicate Customers)
> > Distributor (each unique serial number can have duplicate
> > Distributors)
>
> > I am using Access 2002 if that matters as well.
>
> > Thanks for your help.
>
> > On Dec 21, 2:01 pm, "Phil Stanton" wrote:
> >> Try
>
> >> SELECT [Serial Number], Max([Date Returned])
> >> FROM YourTable
> >> GROUP BY [Serial Number]
>
> >> You may have problems with the date as 9/8/2005 could be 9th August or
> >> 8th
> >> September depending on how your system proccesses dates
>
> >> Phil
>
> >> "colemanj4" wrote in message
>
> >>news:471be2f3-24df-4560-a314-c0a95bae4862@e23g2000prf.goog legroups.com...
>
> >> > Hello,
>
> >> > I have a table that shows:
> >> > Serial Number Date Returned
> >> > 2333005Y 8/15/2006
> >> > 2333005Y 7/18/2003
> >> > 2333005Y 1/1/2004
> >> > 2333005Y 7/5/2006
> >> > 2333005Y 11/24/2005
> >> > 2333005Y 9/8/2005
> >> > 2333005Y 9/8/2005
> >> > 2333005Y 11/24/2005
> >> > 2333014Y 7/18/2007
> >> > 2333014Y 9/6/2005
> >> > 2333014Y 7/23/2003
>
> >> 2333014Y 10/1/2001
> >> > 2333014Y 1/25/2004
> >> > 2333014Y 8/31/2007
> >> > 2333014Y 9/6/2005
> >> > 2333014Y 1/5/2006
> >> > 2333014Y 1/25/2004
> >> > 2333022Y 11/19/2006
> >> > 2333022Y 8/22/2002
> >> > 2333022Y 6/26/2006
> >> > 2333022Y 1/1/2004
> >> > 2333022Y 9/2/2003
> >> > 2333022Y 1/1/2004
> >> > 2333022Y 9/2/2003
> >> > 2333022Y 8/22/2002
> >> > 2333040Y 8/26/2002
> >> > 2333040Y 11/20/2002
> >> > 2333040Y 7/20/2003
> >> > 2333040Y 4/26/2004
> >> > 2333040Y 1/1/2004
> >> > 2333040Y 5/29/2006
> >> > 2333040Y 11/10/2003
> >> > 2333040Y 1/1/2004
> >> > 2333040Y 4/26/2004
> >> > 2333040Y 8/26/2002
>
> >> > and so on. I want it to make a query that shows only one entry for the
> >> > serial number and match it with the most recent date there.So the
> >> > resulting query looks like this.
>
> >> > Serial Number Date Returned
> >> > 2333005Y 8/15/2006
> >> > 2333014Y 8/31/2007
> >> > 2333022Y 11/19/2006
> >> > 2333040Y 5/29/2006
>
> >> > and so on. I cannot figure out how to do this. Distinct is for each
> >> > row, and the Max function for the Date didn't seem to work, although I
> >> > might be doing it wrong.
>
> >> > Any help you could provide would be greatly appreciated.
>
> >> > Thank you,