access query

  • Thread starter Thread starter Jassi
  • Start date Start date
J

Jassi

hi

i have access database table with accountid and datetime fields. There
are more than 1 records for each account id. how should i write a query to
retreive the latest record according to date/time field for each account id.

thanks in advance.
 
hi

i have access database table with accountid and datetime fields. There
are more than 1 records for each account id. how should i write a query to
retreive the latest record according to date/time field for each account id.

thanks in advance.
Click to expand...

SELECT YourTable.AccountID, Max(YourTable.DateField) AS MaxOfDateField
FROM YourTable
GROUP BY YourTable.AccountID;
 
Dear Jessy:

First, you will need a definition of "latest record." Probably this
is the MAX() of the datetime column.

Next, what is meant by "retreive (sic) the latest record." Possibly
this means there are columns from the table other than accountid and
the datetime you want to see for the rows that are "latest."

Naturally, if there can be two rows for the same accountid with the
same datetime value, then there is no way to say one is "later" than
the other. This is an ambiguous situation, and the query will resolve
that by returning both. If you do not want this to be possible, I'd
recommend a unique constraint on an index of the table by accountid
and datetime.

The query may look something like this:

SELECT accountid, datetime, AnotherColumn
FROM YourTable T
WHERE datetime = (SELECT MAX(datetime) FROM YourTable T1
WHERE T1.accountid = T.accountid)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
-----Original Message-----
hi

i have access database table with accountid and datetime fields. There
are more than 1 records for each account id. how should i write a query to
retreive the latest record according to date/time field for each account id.

thanks in advance.



.
Have you tried a group by accountid and max(datetime)
Click to expand...
field?
 
hi

i wrote this query

SELECT a.acctid, Max(a.transactdatetime) AS MaxOfDateField,b.username FROM
transact a,users b where a.acctid=b.acctid GROUP BY a.acctid,b.username

above query gives me the only the records with maximum of date time
field....but i also want another column transactamount from transact table
and wrote a query like this


SELECT a.acctid, Max(a.transactdatetime) AS MaxOfDateField,a.transactamount,
b.username FROM transact a,users b where a.acctid=b.acctid GROUP BY
a.acctid,b.username,a.transactamount

above query returned all the records for each acctid. I want the record with
only the maximum of datetime.
"fredg" <[email protected] in message
 
Dear Jassi:

I think you'll find this is exactly that with which my response dealt.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


hi

i wrote this query

SELECT a.acctid, Max(a.transactdatetime) AS MaxOfDateField,b.username FROM
transact a,users b where a.acctid=b.acctid GROUP BY a.acctid,b.username

above query gives me the only the records with maximum of date time
field....but i also want another column transactamount from transact table
and wrote a query like this


SELECT a.acctid, Max(a.transactdatetime) AS MaxOfDateField,a.transactamount,
b.username FROM transact a,users b where a.acctid=b.acctid GROUP BY
a.acctid,b.username,a.transactamount

above query returned all the records for each acctid. I want the record with
only the maximum of datetime.
"fredg" <[email protected] in message
Click to expand...
 
Back
Top