Query last record for multiple clients

[austin]

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

 

[Falty]

On the query design view grid select the greek symbol (back to front E) then
for clients leave the Total option as 'group by' and for the date field
change the Total option to 'last'

 

[austin]

The query still shows all the records for some reason.

 

[Ken Sheridan]

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

 

[austin]

I'm looking for the query to display a list of all of our clients, and the
last contact date with each.

I might not of phrased my original question well because whenever I change
the settings accordingly, I still see all of the calls for each person
instead of their latest.

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

 

[Ken Sheridan]

Either of the queries I gave you will do that. The first gives you just the
client and latest date columns per client; the second gives you all the other
columns from the table as well for those rows with the latest date per client.

Paste the SQL I posted into a blank query in SQL view and amend the SQL so
the table and column names match your own (but in the case of the second
query not the aliases CL1 and CL2 which should stay as is), remembering to
wrap any names with spaces in brackets [like this], and it should work.

Ken Sheridan
Stafford, England

I'm looking for the query to display a list of all of our clients, and the
last contact date with each.

I might not of phrased my original question well because whenever I change
the settings accordingly, I still see all of the calls for each person
instead of their latest.

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

 

[austin]

thank you very much for your help and time

you wouldnt happen to know how to add a field into a report while in design
view would you?

Either of the queries I gave you will do that. The first gives you just the
client and latest date columns per client; the second gives you all the other
columns from the table as well for those rows with the latest date per client.

Paste the SQL I posted into a blank query in SQL view and amend the SQL so
the table and column names match your own (but in the case of the second
query not the aliases CL1 and CL2 which should stay as is), remembering to
wrap any names with spaces in brackets [like this], and it should work.

Ken Sheridan
Stafford, England

I'm looking for the query to display a list of all of our clients, and the
last contact date with each.

I might not of phrased my original question well because whenever I change
the settings accordingly, I still see all of the calls for each person
instead of their latest.

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

 

[austin]

thank you very much for your help and time

you wouldn't happen to know how to insert another category into a report in
design view would you?

Either of the queries I gave you will do that. The first gives you just the
client and latest date columns per client; the second gives you all the other
columns from the table as well for those rows with the latest date per client.

Paste the SQL I posted into a blank query in SQL view and amend the SQL so
the table and column names match your own (but in the case of the second
query not the aliases CL1 and CL2 which should stay as is), remembering to
wrap any names with spaces in brackets [like this], and it should work.

Ken Sheridan
Stafford, England

I'm looking for the query to display a list of all of our clients, and the
last contact date with each.

I might not of phrased my original question well because whenever I change
the settings accordingly, I still see all of the calls for each person
instead of their latest.

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

 

[austin]

thank you very much for your time and help

Either of the queries I gave you will do that. The first gives you just the
client and latest date columns per client; the second gives you all the other
columns from the table as well for those rows with the latest date per client.

Paste the SQL I posted into a blank query in SQL view and amend the SQL so
the table and column names match your own (but in the case of the second
query not the aliases CL1 and CL2 which should stay as is), remembering to
wrap any names with spaces in brackets [like this], and it should work.

Ken Sheridan
Stafford, England

I'm looking for the query to display a list of all of our clients, and the
last contact date with each.

I might not of phrased my original question well because whenever I change
the settings accordingly, I still see all of the calls for each person
instead of their latest.

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

 

[austin]

Thank you very much for your time and help.

You wouldn't happen to know how to set a subform up so that it starts a new
record with the current day in it every time it is opened?

Either of the queries I gave you will do that. The first gives you just the
client and latest date columns per client; the second gives you all the other
columns from the table as well for those rows with the latest date per client.

Paste the SQL I posted into a blank query in SQL view and amend the SQL so
the table and column names match your own (but in the case of the second
query not the aliases CL1 and CL2 which should stay as is), remembering to
wrap any names with spaces in brackets [like this], and it should work.

Ken Sheridan
Stafford, England

I'm looking for the query to display a list of all of our clients, and the
last contact date with each.

I might not of phrased my original question well because whenever I change
the settings accordingly, I still see all of the calls for each person
instead of their latest.

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

 

[austin]

thank you very much for your time and help.

You wouldn't happen to know how to make a subform start a new record with
the current day every time it is opened would you?

Either of the queries I gave you will do that. The first gives you just the
client and latest date columns per client; the second gives you all the other
columns from the table as well for those rows with the latest date per client.

Paste the SQL I posted into a blank query in SQL view and amend the SQL so
the table and column names match your own (but in the case of the second
query not the aliases CL1 and CL2 which should stay as is), remembering to
wrap any names with spaces in brackets [like this], and it should work.

Ken Sheridan
Stafford, England

I'm looking for the query to display a list of all of our clients, and the
last contact date with each.

I might not of phrased my original question well because whenever I change
the settings accordingly, I still see all of the calls for each person
instead of their latest.

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

 

[austin]

thank you very much for your time and help

Either of the queries I gave you will do that. The first gives you just the
client and latest date columns per client; the second gives you all the other
columns from the table as well for those rows with the latest date per client.

Paste the SQL I posted into a blank query in SQL view and amend the SQL so
the table and column names match your own (but in the case of the second
query not the aliases CL1 and CL2 which should stay as is), remembering to
wrap any names with spaces in brackets [like this], and it should work.

Ken Sheridan
Stafford, England

I'm looking for the query to display a list of all of our clients, and the
last contact date with each.

I might not of phrased my original question well because whenever I change
the settings accordingly, I still see all of the calls for each person
instead of their latest.

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

 

[Ken Sheridan]

You can simply set the DefaultValue property of the control for the date in
the subform to:

Date()

That will insert the current date for any new record in the subform. If you
want the subform to go to the new record automatically then the best place
would I think be the Current event procedure of the main parent form. This
would mean that every time you move to another record in the main form the
subform would immediately go to a new record and the current date would be in
place. This would not actually start to create a new record in the subform;
that would only happen when you add more data into it, so if the user does
not add any more data in the new subform record no new record would be
created when they move on.

What you'd need to do is set focus to the subform and then move to the new
record. To do this you'd refer to the subform control, i.e., the control in
the main parent form which houses the subform, so the code would be along
these lines:

Me.[YourSubformControl].SetFocus
DoCmd.GoToRecord Record:=acNewRec

If you don't want the focus to stay on the subform's new record when you
move to a record in the main form add another line to the above code which
moves the focus back to an appropriate control on the main form, e.g.

Me.[SomeControl].SetFocus

This would mean that the record pointer in the subform would be at its new
record, but the insertion point would be back in whichever control on the
main form you've moved focus to in the above extra line.

Ken Sheridan
Stafford, England

Thank you very much for your time and help.

You wouldn't happen to know how to set a subform up so that it starts a new
record with the current day in it every time it is opened?

Either of the queries I gave you will do that. The first gives you just the
client and latest date columns per client; the second gives you all the other
columns from the table as well for those rows with the latest date per client.

Paste the SQL I posted into a blank query in SQL view and amend the SQL so
the table and column names match your own (but in the case of the second
query not the aliases CL1 and CL2 which should stay as is), remembering to
wrap any names with spaces in brackets [like this], and it should work.

Ken Sheridan
Stafford, England

I'm looking for the query to display a list of all of our clients, and the
last contact date with each.

I might not of phrased my original question well because whenever I change
the settings accordingly, I still see all of the calls for each person
instead of their latest.

:

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]

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

 

[Ken Sheridan]

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]

Ken, I have wrote:

SELECT *
FROM [Movimiento en bodega] AS CL1
WHERE (((CL1.ID)=(SELECT MAX (ID) FROM [Movimiento en bodega] AS CL2 WHERE
CL2.[ ID_tanque]=CL1.[ID_tanque])));

Because i want to know the real stock of all my tanks (ID_tanque means
Identity_tank and Movimiento en bodega means Store movements)

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

 

[Ken Sheridan]

Gonzalo:

Firstly you can change the aliases from CL1 and CL2 to something which
better reflects your table name; I've used MeB1 and MeB2 below.

I think the reason for the prompt is simply because you've included a space
in [ ID_tanque] so Access will be looking for a field name starting with a
space.

The following should work,

SELECT *
FROM [Movimiento en bodega] AS MeB1
WHERE (MeB1.ID)=
(SELECT MAX(ID)
FROM [Movimiento en bodega] AS MeB2
WHERE MeB2.[ID_tanque]=MeB1.[ID_tanque]);

but I assume that ID is the primary key of the table, probably an
autonumber, so the highest ID value per ID_tanque should be the last record
entered for each ID_tanque. Unfortunately you can't rely on an autonumber to
be sequential, so if this is an autonumber, while it will probably work its
not bullet-proof. Much better would be to have a date/time column as Austin
did and use the MAX value of this to identify the latest record. You can set
the date/time column's DefaultValue property to Now() so that the current
date/time is inserted automatically when a record is created.

Ken Sheridan
Stafford, England

Ken, I have wrote:

SELECT *
FROM [Movimiento en bodega] AS CL1
WHERE (((CL1.ID)=(SELECT MAX (ID) FROM [Movimiento en bodega] AS CL2 WHERE
CL2.[ ID_tanque]=CL1.[ID_tanque])));

Because i want to know the real stock of all my tanks (ID_tanque means
Identity_tank and Movimiento en bodega means Store movements)

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]

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]

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

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]

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