Query does not show last date.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
P Mon said:
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.
Click to expand...

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
 
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.
Click to expand...

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]
 
Thanks

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

Cheers again.

John Vinson said:
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.
Click to expand...

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]
Click to expand...
 
Back
Top