date and currency

M

miles

hi,

i kind of urgently need help, i doing a database and there is a
problem i encounter.....

i have three field a name, date and currency - when i use group by for
the name and last or max for both of the date & currency with the same
name it only take up some correct date ........

example:
name date
currency
John 03/03/06
1.00
John 00/09/05
1.20
Miles 00/03/05
0.50
Miles 03/01/06
1.50

when i run the query
where i (name group by)
(date last )
(currency last )
it will result in :
John 03/03/06 1.20 (wrong currency
collected)
Miles 03/01/06 1.50 (correct)

how to solve this problem ????? how to use the IIF method if i want to
use?? is there some other way to help me solve this problem????
 
S

strive4peace

Hi Miles,

on the query

field --> [name_field]

field --> [Date_field]
criteria -->
dMax("[Date_field]","[Tablename]","[name_field]='" &
[name_field] & "'")

field --> [currency_field]

ideally, however, you would use the record ID field and not
a name for comparisons

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
T

Tom Ellison

Dear Miles:

You seem to have an incorrect understanding of the MAX() function, or any
aggregate function, for that matter.

For John's two rows of data, there are two dates. 03/03/06 is the greater
of these two dates. There are two "currency" values 1.00 and 1.20. 1.20 is
the greater of these two.

It sounds like what you want is to find the row with the MAX() of the date
and show the currency value from that same row. This is a different thing
altogether.

The thing to do would be to first find the value of that date which is
greatest, then retrieve the entire row for that person with that date. This
is done using a "correlated subquery": technique:

SELECT [name], [date], [currency]
FROM YourTable T
WHERE [date] =
(SELECT MAX([date])
FROM YourTable T1
WHERE T1.[name] = T.[Name])

There is a possible problem with this. If you have two rows with the same
name and the same date, and that date is the maximum date for all rows with
that name, then you will see both of them. But then, it is possible these
two rows have different values for currency. Given what I believe you want,
both these could be the correct answer to the question, so it is appropriate
that both would appear in the query results.

You must replace YourTable above with the actual name of your table or
query.

Tom Ellison
 
W

Wolfgang Kais

Hello miles.

miles said:
i kind of urgently need help, i doing a database and there is a
problem i encounter.....

i have three field a name, date and currency - when i use group
by for the name and last or max for both of the date & currency
with the same name it only take up some correct date ...

example:
name date currency
John 03/03/06 1.00
John 00/09/05 1.20
Miles 00/03/05 0.50
Miles 03/01/06 1.50

when i run the query where i (name group by)
(date last) (currency last) it will result in :
John 03/03/06 1.20 (wrong currency collected)
Miles 03/01/06 1.50 (correct)
Click to expand...

When using max, there is no problem (date: yy/mm/dd?):
03/03/06 is the max of John's date and
1.20 is the max of John's currency.
When using last for both fields, this sould indeed not happen.
 
Top