Query last record for multiple clients

[Ken Sheridan]

Hola Gonzalo

To format the columns change the first line of the query from:

SELECT *

to:

SELECT [Field1], [Field2], [Field3]

and so on, substituting the real names of your fields. Put a comma between
each field name, but not after the last one. You can then switch to design
view and set the Format property of each column in the usual way.

As regards the situation where you have two or more rows for the same tank
in one day then Access cannot know from the date which is the later record.
In situations like this its best to include the time of day in the date field
as then one will be later than the other, but if you have the date only then
you are reliant on another field such as an autonumber field to distinguish
between them. As I said before this will usually work, but cannot be totally
relied on. To do it that way, however, the query would be like this:

SELECT *
FROM [Movimiento en bodega] AS MeB1
WHERE MeB1.Fecha =
(SELECT MAX(Fecha)
FROM [Movimiento en bodega] AS MeB2
WHERE MeB2.[ID_tanque]=MeB1.[ID_tanque]
AND MeB1.YourAutonumberField =
(SELECT MAX(YourAutonumberField)
FROM [Movimiento en bodega] AS MeB3
WHERE MeB3.[ID_tanque]=MeB1.[ID_tanque]
AND MeB3.Fecha = MeB1.Fecha);

This will return the rows for each tank where the Fecha is the latest
(último) for that tank and the autonumber field's value is the greatest (mas
grande) for the tank on that date. Remember that an autonumber field cannot
be completely guaranteed to be sequential, and even when it this would be the
latest row entered in the table for that date, which is not necessarily the
same as the latest record of the tank on that date as the rows might not have
been entered in the correct real-time order. Including the times of day in
the Fecha column would be the only safe solution, in which case the original
query will be fine.

BTW I wish my Spanish were as good as your English. I always try to speak
Spanish as much as possible when I visit Spain, but then I forget it all
again when I come home!

Ken Sheridan
Stafford, England

Finally, it works!!
I suppose it was only a typping problem.

You are my new access-hero. Thanks for all.

By the way, now I want to give different formats to all columns showed in
the query. For instance, two decimals in number fields, or changing the name
in the column without changing names in the original table. (I do all those
things into design view but they don't appear now, because I used sql view).

Thanks a lot

Another question:
In the same query, if I have two records for the same tank in the same day,
¿how colud I get only the latest?

Ken,

first at all, thanks for your help.
The second thing, I apologize for my terrible english (you know, had wrote
instead of have written, etc.).
After that, I have tried to do all things that you told me yesterday, but it
doesn't work, yet.

I have written :
SELECT *
FROM [Movimiento en bodega] AS MeB1
WHERE (MeB1.Fecha)=
(SELECT MAX(Fecha)
FROM [Movimiento en bodega] AS MeB2
WHERE MeB2.[ID_tanque]=MeB1.[ID_tanque]);

But the query ask for two ID_tanque and it have given only one record. In
this case, the record is the latest dated one of the table (I use date in
every movement that I make in my tanks).

Any other possibility?

Thanks

Gonzalo:

If you are being prompted for the Client that suggests Client is not the
name of a column in your table. What's the table name, and what are the
names of the column equivalent to Austin's Client column and the name of the
column equivalent to his ContactDate column?

Ken Sheridan
Stafford, England

:

Dear All,

I made a query very similar to Austin' query, but i had only one record
which is the latest one of my table. And, in my case, when i play the query
it ask for the name of two clients (I supouse because i wrote the order:
CL2.Client = CL1.Client).

Could anyone help me?

GONZALO (Spain)

:

If you simply want to return the latest contact date per client then group by
client and return the Max contact date:

SELECT Client, MAX(ContactDate) AS LatestContact
FROM CallLog
GROUP BY Client;

If you want to return other columns than that (or those) by which the query
is grouped and the date, e.g. the subject of the call, then use a subquery to
restrict the outer query's return set to the rows with the Max contact date
per client, e.g.

SELECT *
FROM CallLog AS CL1
WHERE ContactDate =
(SELECT MAX(ContactDate)
FROM CallLog AS CL2
WHERE CL2.Client = CL1.Client);

Remember that any table or column names which contain spaces or other
special charcters must be wrapped in brackets [like this].

BTW avoid the FIRST and LAST aggregation operators, which are pretty
pointless. Use operators like MIN, MAX etc which operate on values as the
basis for aggregation.

Ken Sheridan
Stafford, England

:

I have a call log table that has multiple records for each of our clients.

Can you run a query so that it displays the last time (Contact Date) we
spoke to each and every one of our clients?

thanks

 

[Gonzalo]

Thanks for all.
You were a good master and had been a great help

Hola Gonzalo

To format the columns change the first line of the query from:

SELECT *

to:

SELECT [Field1], [Field2], [Field3]

and so on, substituting the real names of your fields. Put a comma between
each field name, but not after the last one. You can then switch to design
view and set the Format property of each column in the usual way.

As regards the situation where you have two or more rows for the same tank
in one day then Access cannot know from the date which is the later record.
In situations like this its best to include the time of day in the date field
as then one will be later than the other, but if you have the date only then
you are reliant on another field such as an autonumber field to distinguish
between them. As I said before this will usually work, but cannot be totally
relied on. To do it that way, however, the query would be like this:

SELECT *
FROM [Movimiento en bodega] AS MeB1
WHERE MeB1.Fecha =
(SELECT MAX(Fecha)
FROM [Movimiento en bodega] AS MeB2
WHERE MeB2.[ID_tanque]=MeB1.[ID_tanque]
AND MeB1.YourAutonumberField =
(SELECT MAX(YourAutonumberField)
FROM [Movimiento en bodega] AS MeB3
WHERE MeB3.[ID_tanque]=MeB1.[ID_tanque]
AND MeB3.Fecha = MeB1.Fecha);

This will return the rows for each tank where the Fecha is the latest
(último) for that tank and the autonumber field's value is the greatest (mas
grande) for the tank on that date. Remember that an autonumber field cannot
be completely guaranteed to be sequential, and even when it this would be the
latest row entered in the table for that date, which is not necessarily the
same as the latest record of the tank on that date as the rows might not have
been entered in the correct real-time order. Including the times of day in
the Fecha column would be the only safe solution, in which case the original
query will be fine.

BTW I wish my Spanish were as good as your English. I always try to speak
Spanish as much as possible when I visit Spain, but then I forget it all
again when I come home!

Ken Sheridan
Stafford, England

Finally, it works!!
I suppose it was only a typping problem.

You are my new access-hero. Thanks for all.

By the way, now I want to give different formats to all columns showed in
the query. For instance, two decimals in number fields, or changing the name
in the column without changing names in the original table. (I do all those
things into design view but they don't appear now, because I used sql view).

Thanks a lot

Another question:
In the same query, if I have two records for the same tank in the same day,
¿how colud I get only the latest?

Ken,

first at all, thanks for your help.
The second thing, I apologize for my terrible english (you know, had wrote
instead of have written, etc.).
After that, I have tried to do all things that you told me yesterday, but it
doesn't work, yet.

I have written :
SELECT *
FROM [Movimiento en bodega] AS MeB1
WHERE (MeB1.Fecha)=
(SELECT MAX(Fecha)
FROM [Movimiento en bodega] AS MeB2
WHERE MeB2.[ID_tanque]=MeB1.[ID_tanque]);

But the query ask for two ID_tanque and it have given only one record. In
this case, the record is the latest dated one of the table (I use date in
every movement that I make in my tanks).

Any other possibility?

Thanks

:

Gonzalo:

If you are being prompted for the Client that suggests Client is not the
name of a column in your table. What's the table name, and what are the
names of the column equivalent to Austin's Client column and the name of the
column equivalent to his ContactDate column?

Ken Sheridan
Stafford, England

:

Dear All,

I made a query very similar to Austin' query, but i had only one record
which is the latest one of my table. And, in my case, when i play the query
it ask for the name of two clients (I supouse because i wrote the order:
CL2.Client = CL1.Client).

Could anyone help me?

GONZALO (Spain)

:

If you simply want to return the latest contact date per client then group by
client and return the Max contact date:

SELECT Client, MAX(ContactDate) AS LatestContact
FROM CallLog
GROUP BY Client;

If you want to return other columns than that (or those) by which the query
is grouped and the date, e.g. the subject of the call, then use a subquery to
restrict the outer query's return set to the rows with the Max contact date
per client, e.g.

SELECT *
FROM CallLog AS CL1
WHERE ContactDate =
(SELECT MAX(ContactDate)
FROM CallLog AS CL2
WHERE CL2.Client = CL1.Client);

Remember that any table or column names which contain spaces or other
special charcters must be wrapped in brackets [like this].

BTW avoid the FIRST and LAST aggregation operators, which are pretty
pointless. Use operators like MIN, MAX etc which operate on values as the
basis for aggregation.

Ken Sheridan
Stafford, England

:

I have a call log table that has multiple records for each of our clients.

Can you run a query so that it displays the last time (Contact Date) we
spoke to each and every one of our clients?

thanks