Query does not show last date.

[Guest]

I need a Query to show me the last date for receipts on one table from
companies listed on another table. They are linked by a relationship.

Access 2000. Using Thai calendar.

The dates are reported out of order.

 

[Marshall Barton]

I need a Query to show me the last date for receipts on one table from
companies listed on another table. They are linked by a relationship.

Access 2000. Using Thai calendar.

The dates are reported out of order.

More details are needed to be sure, but I think something
like this might be close:

SELECT C.companyname, Max(receiptdate) As LastDate
FROM companiestable As C INNER JOIN receoptstable As R
ON R.companyID = C.ID
GROUP BY C.companyname

 

[John Vinson]

On Tue, 16 Jan 2007 20:38:01 -0800, P Mon <P

I need a Query to show me the last date for receipts on one table from
companies listed on another table. They are linked by a relationship.

Access 2000. Using Thai calendar.

The dates are reported out of order.

You don't describe your tables, or the Thai calendar; are the dates
stored in an Access Date/Time field, or some other field type? How are
Thai dates expressed - what's the sort order that you're getting?

If you are using date/time type fields, you can use a Top Values query
or a Subquery with a criterion

=(SELECT Max([datefield]) FROM tablename AS X WHERE X.companyid =
Tablename.companyid)

on the date field - but if the dates are stored in text fields they
will sort alphabetically, not chronologically.

John W. Vinson[MVP]

 

[Guest]

Thank you.

Max did the trick. I kept using last which messed me about.

Cheers

 

[Guest]

Thanks

MAX worked. I was using LAST, hence it kept messing up.

Cheers again.

On Tue, 16 Jan 2007 20:38:01 -0800, P Mon <P

I need a Query to show me the last date for receipts on one table from
companies listed on another table. They are linked by a relationship.

Access 2000. Using Thai calendar.

The dates are reported out of order.

You don't describe your tables, or the Thai calendar; are the dates
stored in an Access Date/Time field, or some other field type? How are
Thai dates expressed - what's the sort order that you're getting?

If you are using date/time type fields, you can use a Top Values query
or a Subquery with a criterion

=(SELECT Max([datefield]) FROM tablename AS X WHERE X.companyid =
Tablename.companyid)

on the date field - but if the dates are stored in text fields they
will sort alphabetically, not chronologically.

John W. Vinson[MVP]