SQL Querry

[Charles A. Lackman]

Hello,

I trying to produce a querry that will accomplish the following and am not
sure how to write the statement.
I am currently using Jet 4.0 but will be using SQL Server soon.

Example Records
ID CustID Data Date Balance

1 1234 bla8 092705 42.00
2 2341 bla7 091405 14.00
3 3412 bla6 090205 32.00
4 4123 bla5 081805 65.00
5 3412 bla4 093005 34.00
6 2341 bla3 082705 19.00

What I want to accomplish is:

Return Data, Date and Balance Where Max(ID) For CustID 3412

SELECT Data, Date, Balance Where Max(ID) AND CustID = '3412'

The Querry should Return

bla4, 093005, 34.00

Any Assistance is greatly appreciated.

Chuck

 

[Paul Clement]

¤ Hello,
¤
¤ I trying to produce a querry that will accomplish the following and am not
¤ sure how to write the statement.
¤ I am currently using Jet 4.0 but will be using SQL Server soon.
¤
¤ Example Records
¤ ID CustID Data Date Balance
¤
¤ 1 1234 bla8 092705 42.00
¤ 2 2341 bla7 091405 14.00
¤ 3 3412 bla6 090205 32.00
¤ 4 4123 bla5 081805 65.00
¤ 5 3412 bla4 093005 34.00
¤ 6 2341 bla3 082705 19.00
¤
¤ What I want to accomplish is:
¤
¤ Return Data, Date and Balance Where Max(ID) For CustID 3412
¤
¤ SELECT Data, Date, Balance Where Max(ID) AND CustID = '3412'
¤
¤ The Querry should Return
¤
¤ bla4, 093005, 34.00
¤
¤ Any Assistance is greatly appreciated.

"SELECT Data, [Date], Balance " & _
"FROM MyTable " & _
"WHERE ID = " & _
"(SELECT MAX(ID) " & _
"FROM MyTable " & _
"WHERE CustID = '3412')"

BTW, Date is a reserved word in Jet so you may want to consider changing the name. Otherwise, it
must be enclosed by brackets.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Charles A. Lackman]

Thank You Very Much.

Chuck


On Thu, 29 Dec 2005 11:11:56 -0800, "Charles A. Lackman"

¤ Hello,
¤
¤ I trying to produce a querry that will accomplish the following and am not
¤ sure how to write the statement.
¤ I am currently using Jet 4.0 but will be using SQL Server soon.
¤
¤ Example Records
¤ ID CustID Data Date Balance
¤
¤ 1 1234 bla8 092705 42.00
¤ 2 2341 bla7 091405 14.00
¤ 3 3412 bla6 090205 32.00
¤ 4 4123 bla5 081805 65.00
¤ 5 3412 bla4 093005 34.00
¤ 6 2341 bla3 082705 19.00
¤
¤ What I want to accomplish is:
¤
¤ Return Data, Date and Balance Where Max(ID) For CustID 3412
¤
¤ SELECT Data, Date, Balance Where Max(ID) AND CustID = '3412'
¤
¤ The Querry should Return
¤
¤ bla4, 093005, 34.00
¤
¤ Any Assistance is greatly appreciated.

"SELECT Data, [Date], Balance " & _
"FROM MyTable " & _
"WHERE ID = " & _
"(SELECT MAX(ID) " & _
"FROM MyTable " & _
"WHERE CustID = '3412')"

BTW, Date is a reserved word in Jet so you may want to consider changing the
name. Otherwise, it
must be enclosed by brackets.


Paul
~~~~
Microsoft MVP (Visual Basic)