select

[Rli]

I have a table that is giving me big problems....
Its definition is simple:
table name: table1
field1 name: [table1-id] (autonumber)
field2 name: [bail-id] (reference to an other table)
field2 name: [amount] (money)
field3 name: [amountdate] (a date field)

so the problem is that there may be several records with the same [bail-id]
but a different [amount] and/or [amountdate].
for example:
[table1-id],[bail-id],[amount],[amountdate]
record1 1 3 10.10 10-09-2005
record2 2 3 12.00 11-09-2005
record3 3 2 9.00 12-09-2005
record4 4 2 10.00 09-09-2005

Now i want to find for each DISTINCT [bail-id] only those records that
contain the most recent date and the corresponding [amount]. Awnser: record2
and record3

Does anyone understands what i mean?......and give me a clou how to do it???

Thanks for any help

 

[Gary Walter]

I have a table that is giving me big problems....
Its definition is simple:
table name: table1
field1 name: [table1-id] (autonumber)
field2 name: [bail-id] (reference to an other table)
field2 name: [amount] (money)
field3 name: [amountdate] (a date field)

so the problem is that there may be several records with the same
[bail-id]
but a different [amount] and/or [amountdate].
for example:
[table1-id],[bail-id],[amount],[amountdate]
record1 1 3 10.10 10-09-2005
record2 2 3 12.00 11-09-2005
record3 3 2 9.00 12-09-2005
record4 4 2 10.00 09-09-2005

Now i want to find for each DISTINCT [bail-id] only those records that
contain the most recent date and the corresponding [amount]. Awnser:
record2
and record3

Hi Rli,

http://www.mvps.org/access/queries/qry0020.htm

So (untested), one way might be:

SELECT
t.[table1-id],
t.[bail-id],
t.amount,
t.amountdate
FROM
table1 As t
WHERE
t.amountdate =
(SELECT Max(q.amountdate)
FROM
table1 As q
WHERE
q.[bail-id] = t.[bail-id]);

If it is not too late, I believe you will be
better off removing all punctuation from your
fieldnames.

good luck,

gary