individual last records

  • Thread starter Thread starter JB
  • Start date Start date
J

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
 
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.
 
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
 
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


John Spencer said:
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 said:
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.
Click to expand...
Click to expand...
 
Post the SQL of your query.
--
Build a little, test a little.


JB said:
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


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

KARL DEWEY said:
Post the SQL of your query.
--
Build a little, test a little.


JB said:
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


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


JB said:
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

KARL DEWEY said:
Post the SQL of your query.
--
Build a little, test a little.


JB said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
Thank you Karl & John, Perfect!
THANK YOU!!!
Jen

John Spencer said:
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

KARL said:
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;
Click to expand...
Click to expand...
 
Come back!!
The query worked but I can't seem to get a report out of it. Please
Jen

John Spencer said:
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

KARL said:
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;
Click to expand...
Click to expand...
 
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

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

John Spencer said:
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

John Spencer said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top