Using the Update Query

[Saad]

Hi,

I have a query that runs of a table that contains rates
for various origin and destinations.
E.g.

Newyork Houston $1000 02/22/2003
Newyork Houston $1200 10/22/2003

Rightnow the query returns both the records, but I only
want the record with the latest timestamp i.e. 10/22/2003
in the example above. Can you help? Tks a lot - Saad

 

[John Spencer (MVP)]

One method is to use a query with a subquery.

SELECT A.CityA, A.CityB, A.Cost, A.DateField
FROM Table as A
WHERE A.DateField =
(SELECT Max(tmp.DateField)
FROM Table as tmp
WHERE tmp.CityA = A.CityA
AND tmp.CityB = A.CityB)

 

[Saad]

Hi John,

Tks for the advice. Can you guide a little more as to how
to make a subquery where I could fit in this code? Tks -
Saad

 

[John Spencer (MVP)]

That was an example with a query and a subquery. The subquery is the part in
the Where clause after the equals sign. You will have to put in your field and
table names