individual last records

[JB]

A month ago I posted a question about creating a report to get the last
dated record by each consultant in a table for each of their clients.
I followed a couple suggestions but finally what worked was a crosstab
query.

Consultant (group by: Column heading)
Client (group by: Row heading)
ActivityDate (Last: Value)

This is perfect and the report shows all the clients Alphabetically in rows
and there is a column for each consultant showing the last date they
contacted a client. Happy.

BUT I'd now like to have a report for any given Consultant. In the query
design, in criteria under Consultant, I put [Enter Consultant's Name] but it
didn't like that. I was able to put a consultant's name , but do I have to
create a query and a report for each Consultant?? How can I get one report
to give me a dialogue box to enter the Consultant I want to view?
Hope that makes sense.
Can anyone please advise.
Jenny

 

[KARL DEWEY]

Use a form with unbound text box to enter the name of the consultant.
Then in the query reference the text box as criteria like this --
[Forms]![YourFormName]![TextBoxName]

The form must remain open with the data in the text box when you run the
query or report.

 

[John Spencer]

By the way, you probably want to use Max(ActivityDate) and Not
LAST(ActivityDate). Last tends to return the last record in the group in
storage order.

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
in Karl's example that would be
[Forms]![YourFormName]![TextBoxName]
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JB]

Thanks John.
I changed the Last to Max. Thanks for that tip.

I did the Parameter suggestion and although it didn't throw out an error,
the query showed all data.
And I'm getting more and more confused. But not giving up!!
J

By the way, you probably want to use Max(ActivityDate) and Not
LAST(ActivityDate). Last tends to return the last record in the group in
storage order.

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
in Karl's example that would be
[Forms]![YourFormName]![TextBoxName]
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Use a form with unbound text box to enter the name of the consultant.
Then in the query reference the text box as criteria like this --
[Forms]![YourFormName]![TextBoxName]

The form must remain open with the data in the text box when you run the
query or report.

 

[KARL DEWEY]

Post the SQL of your query.
--
Build a little, test a little.

Thanks John.
I changed the Last to Max. Thanks for that tip.

I did the Parameter suggestion and although it didn't throw out an error,
the query showed all data.
And I'm getting more and more confused. But not giving up!!
J

By the way, you probably want to use Max(ActivityDate) and Not
LAST(ActivityDate). Last tends to return the last record in the group in
storage order.

First thing, with a crosstab query you MUST declare your parameters and if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
in Karl's example that would be
[Forms]![YourFormName]![TextBoxName]
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Use a form with unbound text box to enter the name of the consultant.
Then in the query reference the text box as criteria like this --
[Forms]![YourFormName]![TextBoxName]

The form must remain open with the data in the text box when you run the
query or report.

 

[JB]

This is the original query that returns the most recent activity of each
consultant with their clients

TRANSFORM Last(qryClientActivity.ActivityDate) AS LastOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

BTW in the underlying table a client can only have one consultant so the
client will only show once in this query
Ta
Jen

Post the SQL of your query.
--
Build a little, test a little.

Thanks John.
I changed the Last to Max. Thanks for that tip.

I did the Parameter suggestion and although it didn't throw out an error,
the query showed all data.
And I'm getting more and more confused. But not giving up!!
J

By the way, you probably want to use Max(ActivityDate) and Not
LAST(ActivityDate). Last tends to return the last record in the group
in
storage order.

First thing, with a crosstab query you MUST declare your parameters and
if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
in Karl's example that would be
[Forms]![YourFormName]![TextBoxName]
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Use a form with unbound text box to enter the name of the consultant.
Then in the query reference the text box as criteria like this --
[Forms]![YourFormName]![TextBoxName]

The form must remain open with the data in the text box when you run
the
query or report.

 

[KARL DEWEY]

Try this --
PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.ClientName = [Enter Consultant's Name]
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

--
Build a little, test a little.

This is the original query that returns the most recent activity of each
consultant with their clients

TRANSFORM Last(qryClientActivity.ActivityDate) AS LastOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

BTW in the underlying table a client can only have one consultant so the
client will only show once in this query
Ta
Jen

Post the SQL of your query.
--
Build a little, test a little.

Thanks John.
I changed the Last to Max. Thanks for that tip.

I did the Parameter suggestion and although it didn't throw out an error,
the query showed all data.
And I'm getting more and more confused. But not giving up!!
J


By the way, you probably want to use Max(ActivityDate) and Not
LAST(ActivityDate). Last tends to return the last record in the group
in
storage order.

First thing, with a crosstab query you MUST declare your parameters and
if
any
other queries are used in the crosstab their parameters must also be
declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
in Karl's example that would be
[Forms]![YourFormName]![TextBoxName]
Select the data type of the parameter in column 2

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

KARL DEWEY wrote:
Use a form with unbound text box to enter the name of the consultant.
Then in the query reference the text box as criteria like this --
[Forms]![YourFormName]![TextBoxName]

The form must remain open with the data in the text box when you run
the
query or report.

 

[John Spencer]

Slight ERROR in the where clause. I think you want to restrict the query
against a consultant, not against a client.

Try this --
PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.Consultant= [Enter Consultant's Name]
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JB]

Thank you Karl & John, Perfect!
THANK YOU!!!
Jen

Slight ERROR in the where clause. I think you want to restrict the query
against a consultant, not against a client.

Try this --
PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.Consultant= [Enter Consultant's Name]
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Try this --
PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.ClientName = [Enter Consultant's Name] GROUP BY
qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

 

[JB]

Come back!!
The query worked but I can't seem to get a report out of it. Please
Jen

Slight ERROR in the where clause. I think you want to restrict the query
against a consultant, not against a client.

Try this --
PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.Consultant= [Enter Consultant's Name]
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Try this --
PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.ClientName = [Enter Consultant's Name] GROUP BY
qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

 

[John Spencer]

I don't know that this will work, but you can try the following

PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT [Enter Consultant's Name] as Consult,
qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.Consultant= [Enter Consultant's Name]
GROUP BY [Enter Consultant's Name], qryClientActivity.ClientName
PIVOT "LastActivity";

That will give you a consistent query structure so you can build a report
based on it.

It will return
Consultant's Name as entered, theClientName, and the MaxOfActivityDate.

If you need to do this for all consultants at one time then you would need to
know all the consultants and have the name listed in the PIVOT clause in order
to return a consistent structure.

PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant in
("Roberts","Smith","Jones","Spencer","Arkady")

That would return a column for each name listed whether or not there was any
data for the consultant.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Come back!!
The query worked but I can't seem to get a report out of it. Please
Jen

Slight ERROR in the where clause. I think you want to restrict the
query against a consultant, not against a client.

Try this --
PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.Consultant= [Enter Consultant's Name]
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JB]

Thank you John
Yes it worked. Nice Report. Brownie points for me from my boss (husband). I
should ask for a bonus!
Thank you very much
Jen

I don't know that this will work, but you can try the following

PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT [Enter Consultant's Name] as Consult,
qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.Consultant= [Enter Consultant's Name]
GROUP BY [Enter Consultant's Name], qryClientActivity.ClientName
PIVOT "LastActivity";

That will give you a consistent query structure so you can build a report
based on it.

It will return
Consultant's Name as entered, theClientName, and the MaxOfActivityDate.

If you need to do this for all consultants at one time then you would need
to know all the consultants and have the name listed in the PIVOT clause
in order to return a consistent structure.

PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant in
("Roberts","Smith","Jones","Spencer","Arkady")

That would return a column for each name listed whether or not there was
any data for the consultant.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Come back!!
The query worked but I can't seem to get a report out of it. Please
Jen

Slight ERROR in the where clause. I think you want to restrict the
query against a consultant, not against a client.

Try this --
PARAMETERS ( [Enter Consultant's Name] ) Text ( 255 );
TRANSFORM Max(qryClientActivity.ActivityDate) AS MaxOfActivityDate
SELECT qryClientActivity.ClientName
FROM qryClientActivity
WHERE qryClientActivity.Consultant= [Enter Consultant's Name]
GROUP BY qryClientActivity.ClientName
PIVOT qryClientActivity.Consultant;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County