Using IIF in expression builder in reports

[alvarjo9 via AccessMonster.com]

Hi,
Please help, in the detail report section , I have four IIF expression
builder.

Category A Category B
Category C Category D
=IIf([Calibre]="A",[Date],Null) =IIf([Calibre]="B",[Date],Null) =IIf(
[Calibre]="C",[Date],Null) =IIf([Calibre]="D",[Date],Null)


My problem is when the report runs, it produces unwantes spaces if it doesn't
satisfy the four condition. E.g. If it satisfies calbire A. Then it will
have space in the Category B C and D column.

I am thinking of putting them in one text box and concatenating them but I am
not sure of the syntax.
Please kindly assist.

I have tried to use the IIF statement in the query design and the result is
still the same (post previously before with subject Switch Statement)

thanks

 

[John Spencer]

Perhaps what you want is

IIF(Calibre in ("A","B","C","D"), [Date], NULL)

which will return the value in the date field if calibre is A, B, C, OR
D and null (blank) in all other cases.


It seems as if you want only one value returned for one column. If that
is not the case, post back with a sample of what you want to happen.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[alvarjo9 via AccessMonster.com]

Hi John,
Thanks for the help but I want to return 4 values based on the 4 categories
based on Calibre A, B , C D.
Example is the report layout.
The IIF(Calibre in ("A","B","C","D"), [Date], NULL) will return the value in
just one row. I require the Dates based on the Calibre condition A, B C D to
be place in Each of the category. This is the reason why I Created =IIf(
[Calibre]="A",[Date],Null) up to D and place them under each column of the
Category. But my problem is it produces spaces if it doesn't satisfy the
Calibre condition.

I am also thinking of creating Four Queries and calling them in the report
using the Name as the field for selection using combo box. But I am not sure
how to call them using one combo box but need to search in the four queries
and display them in the report.

Please kindly help.
thanks

Category A Category B Category C
Category D
Dates Dates Dates
Dates

Perhaps what you want is

IIF(Calibre in ("A","B","C","D"), [Date], NULL)

which will return the value in the date field if calibre is A, B, C, OR
D and null (blank) in all other cases.

It seems as if you want only one value returned for one column. If that
is not the case, post back with a sample of what you want to happen.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
Please help, in the detail report section , I have four IIF expression

[quoted text clipped - 17 lines]

thanks

 

[John Spencer]

Obviously, I don't understand your data structure and what you want to get
as a result.

Do you have multiple records in one table that are related to a record in
another table? And you want to combine the multiple related records into
one row in a query?

So you want to end up with a row that has (for example) five fields - four
of which are the dates for Calibre A, B, C, and D.

What you may want in that case is a Cross-tab query, but without a more
complete description of your problem and your table and field description I
doubt that anyone will be able to help.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,
Thanks for the help but I want to return 4 values based on the 4
categories
based on Calibre A, B , C D.
Example is the report layout.
The IIF(Calibre in ("A","B","C","D"), [Date], NULL) will return the value
in
just one row. I require the Dates based on the Calibre condition A, B C D
to
be place in Each of the category. This is the reason why I Created =IIf(
[Calibre]="A",[Date],Null) up to D and place them under each column of the
Category. But my problem is it produces spaces if it doesn't satisfy the
Calibre condition.

I am also thinking of creating Four Queries and calling them in the report
using the Name as the field for selection using combo box. But I am not
sure
how to call them using one combo box but need to search in the four
queries
and display them in the report.

Please kindly help.
thanks

Category A Category B Category C
Category D
Dates Dates Dates
Dates

Perhaps what you want is

IIF(Calibre in ("A","B","C","D"), [Date], NULL)

which will return the value in the date field if calibre is A, B, C, OR
D and null (blank) in all other cases.

It seems as if you want only one value returned for one column. If that
is not the case, post back with a sample of what you want to happen.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
Please help, in the detail report section , I have four IIF expression

[quoted text clipped - 17 lines]

thanks

 

[alvarjo9 via AccessMonster.com]

Hi John,
Sorry for the confusion. Basically I have a query that extracts the top 6
records based on each calibre: A, B, C , D . I will then display the Scores.
Date based on each category in the report form.
Below is the query I use:
SELECT Scores.Name, Scores.Calibre, Scores.Date
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName
AND (((Scores.Date) In (SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name =
Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

The problem is if I use the =IIf([Calibre] In ("A","B","C","D"),[Date],Null)
it will just return one row.
What I want to happen is for each category it will show the top six dates
under A , B , C or D.

I try to separate them using =IIf([Calibre]="D",[Date],Null), =IIf([Calibre]
="D",[Date],Null), =IIf([Calibre]="D",[Date],Null) and =IIf([Calibre]="D",
[Date],Null) but what happen is it display spaces if no condition is
satisfied for the category. Using the Calibre type as condition to extract
the Date, I want the report output to be:

I want the report to output to be:
Category A Category B Category C Cateoory
05-May-08 05-June-2005 12-Dec-06 05-Jul-05

It produces wrong output when generated the report is generated when I use
four IIF condition for each calibre:
Category A Category B Category C Cateoory
05-May-08 space space space
space space 12-Dec-06 space
space space space 05-Jul-05
space 05-June-2005 space space

PLease kindly advise if there is a solution to my problem. I hope I have
explain everything on the above issue.

thanks

Obviously, I don't understand your data structure and what you want to get
as a result.

Do you have multiple records in one table that are related to a record in
another table? And you want to combine the multiple related records into
one row in a query?

So you want to end up with a row that has (for example) five fields - four
of which are the dates for Calibre A, B, C, and D.

What you may want in that case is a Cross-tab query, but without a more
complete description of your problem and your table and field description I
doubt that anyone will be able to help.

Hi John,
Thanks for the help but I want to return 4 values based on the 4

[quoted text clipped - 47 lines]

 

[alvarjo9 via AccessMonster.com]

Hi,
Have already given the complete information regarding my query.
Please kindly assist on my problem
thanks

Hi John,
Sorry for the confusion. Basically I have a query that extracts the top 6
records based on each calibre: A, B, C , D . I will then display the Scores.
Date based on each category in the report form.
Below is the query I use:
SELECT Scores.Name, Scores.Calibre, Scores.Date
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName
AND (((Scores.Date) In (SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name =
Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

The problem is if I use the =IIf([Calibre] In ("A","B","C","D"),[Date],Null)
it will just return one row.
What I want to happen is for each category it will show the top six dates
under A , B , C or D.

I try to separate them using =IIf([Calibre]="D",[Date],Null), =IIf([Calibre]
="D",[Date],Null), =IIf([Calibre]="D",[Date],Null) and =IIf([Calibre]="D",
[Date],Null) but what happen is it display spaces if no condition is
satisfied for the category. Using the Calibre type as condition to extract
the Date, I want the report output to be:

I want the report to output to be:
Category A Category B Category C Cateoory
05-May-08 05-June-2005 12-Dec-06 05-Jul-05

It produces wrong output when generated the report is generated when I use
four IIF condition for each calibre:
Category A Category B Category C Cateoory
05-May-08 space space space
space space 12-Dec-06 space
space space space 05-Jul-05
space 05-June-2005 space space

PLease kindly advise if there is a solution to my problem. I hope I have
explain everything on the above issue.

thanks

Obviously, I don't understand your data structure and what you want to get
as a result.

[quoted text clipped - 15 lines]

 

[John Spencer]

I still don't understand.

I think you want to end up with a grid of 4 columns and 6 rows that contain
dates. What I can't determine is how you want the data determined for each
row. (A1, B1 etc representing dates) I think you want

Row 1 A1 B1 C1 D1
Row 1 A1 B1 C1 D1
Row 1 A1 B1 C1 D1
Row 1 A1 B1 C1 D1
Row 1 A1 B1 C1 D1

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,
Have already given the complete information regarding my query.
Please kindly assist on my problem
thanks

Hi John,
Sorry for the confusion. Basically I have a query that extracts the top 6
records based on each calibre: A, B, C , D . I will then display the
Scores.
Date based on each category in the report form.
Below is the query I use:
SELECT Scores.Name, Scores.Calibre, Scores.Date
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName
AND (((Scores.Date) In (SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name
=
Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

The problem is if I use the =IIf([Calibre] In
("A","B","C","D"),[Date],Null)
it will just return one row.
What I want to happen is for each category it will show the top six dates
under A , B , C or D.

I try to separate them using =IIf([Calibre]="D",[Date],Null),
=IIf([Calibre]
="D",[Date],Null), =IIf([Calibre]="D",[Date],Null) and =IIf([Calibre]="D",
[Date],Null) but what happen is it display spaces if no condition is
satisfied for the category. Using the Calibre type as condition to
extract
the Date, I want the report output to be:

I want the report to output to be:
Category A Category B Category C Cateoory
05-May-08 05-June-2005 12-Dec-06 05-Jul-05

It produces wrong output when generated the report is generated when I use
four IIF condition for each calibre:
Category A Category B Category C Cateoory
05-May-08 space space space
space space 12-Dec-06 space
space space space 05-Jul-05
space 05-June-2005 space space

PLease kindly advise if there is a solution to my problem. I hope I have
explain everything on the above issue.

thanks

Obviously, I don't understand your data structure and what you want to
get
as a result.

[quoted text clipped - 15 lines]

thanks

 

[John Spencer]

DANG! I hate when I hit the wrong keys.

I still don't understand.

I think you want to end up with a grid of 4 columns and 6 rows that contain
dates. What I can't determine is how you want the data determined for each
row. (A1, B1 etc representing dates for each Calibre) I think you want.

Row 1 A1 B1 C1 D1
Row 2 A2 B2 C2 D2
....
Row 6 A6 B6 C6 D6

How do you determine each row's content? That is how do you associate A1
with B1 and not with B2?
Your current query seems to return something like the following.

Name, Calibre, Date
xxx, A, DateA1
xxx, A, DateA2
xxx, A, DateA3
....
xxx, D, DateA5
xxx, D, DateA6

A crosstab query could be used to return the one row containing the maximum
or minimum dates for each calibre, but I am stuck on how to get this to
return all 6 rows of data.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi,
Have already given the complete information regarding my query.
Please kindly assist on my problem
thanks

Hi John,
Sorry for the confusion. Basically I have a query that extracts the top 6
records based on each calibre: A, B, C , D . I will then display the
Scores.
Date based on each category in the report form.
Below is the query I use:
SELECT Scores.Name, Scores.Calibre, Scores.Date
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName
AND (((Scores.Date) In (SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name
=
Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

The problem is if I use the =IIf([Calibre] In
("A","B","C","D"),[Date],Null)
it will just return one row.
What I want to happen is for each category it will show the top six dates
under A , B , C or D.

I try to separate them using =IIf([Calibre]="D",[Date],Null),
=IIf([Calibre]
="D",[Date],Null), =IIf([Calibre]="D",[Date],Null) and =IIf([Calibre]="D",
[Date],Null) but what happen is it display spaces if no condition is
satisfied for the category. Using the Calibre type as condition to
extract
the Date, I want the report output to be:

I want the report to output to be:
Category A Category B Category C Cateoory
05-May-08 05-June-2005 12-Dec-06 05-Jul-05

It produces wrong output when generated the report is generated when I use
four IIF condition for each calibre:
Category A Category B Category C Cateoory
05-May-08 space space space
space space 12-Dec-06 space
space space space 05-Jul-05
space 05-June-2005 space space

PLease kindly advise if there is a solution to my problem. I hope I have
explain everything on the above issue.

thanks

Obviously, I don't understand your data structure and what you want to
get
as a result.

[quoted text clipped - 15 lines]

thanks

 

[alvarjo9 via AccessMonster.com]

Hi
Sorry for the late reply. My query is returing the top 6 record for each
calibre.
Below is a sample result of the query:

Basically, I just want the report output to be:
John Spencer
A B C D
7/06/2005 31/07/2005 12/06/2005 6/07/2005
2/08/2005 6/11/2005 10/07/2005 14/08/2005
16/08/2005 26/03/2006 26/03/2006 9/10/2005
23/08/2005 2/04/2006 23/04/2006 11/12/2005
25/10/2005 9/04/2006 30/04/2006 15/01/2006
11/04/2006 30/04/2006

I can't find out how to do the above approach in the report. Please advise
if you have other option that you can give.

Name Calibre Date
John Spencer A 7/06/2005
John Spencer A 2/08/2005
John Spencer A 16/08/2005
John Spencer A 23/08/2005
John Spencer A 25/10/2005
John Spencer A 11/04/2006
John Spencer B 31/07/2005
John Spencer B 6/11/2005
John Spencer B 26/03/2006
John Spencer B 2/04/2006
John Spencer B 9/04/2006
John Spencer C 12/06/2005
John Spencer C 10/07/2005
John Spencer C 26/03/2006
John Spencer C 23/04/2006
John Spencer C 30/04/2006
John Spencer D 6/07/2005
John Spencer D 14/08/2005
John Spencer D 9/10/2005
John Spencer D 11/12/2005
John Spencer D 15/01/2006
John Spencer D 30/04/2006

The problem is using the IIf Expression defined in the detail section of the
report per each Calibre:
=IIf([Calibre]="A",[Date],Null) =IIf([Calibre]="B",[Date],Null) =IIf([Calibre]
="C",[Date],Null) and =IIf([Calibre]="D",[Date],Null) in the detail section
of the report

It will display the value in the detail section but the layout is incorrect
the result will be below with unwanted spaces. You may find that there are
unwanted spaces if it does not match the Condition

John Spencer

Calibre A Calibre B Calibre C Calibre D
07/06/2005
12/06/2005
6/07/2005
10/07/2005
31/07/2005
02/08/2005

14/08/2005
16/08/2005
23/08/2005

9/10/2005
25/10/2005
6/11/2005

11/12/2005
15/01/2006
26/03/2006
26/03/2006
2/04/2006
9/04/2006
11/04/2006
23/04/2006
30/04/2006
30/04/2006
7/06/2005

DANG! I hate when I hit the wrong keys.

I still don't understand.

I think you want to end up with a grid of 4 columns and 6 rows that contain
dates. What I can't determine is how you want the data determined for each
row. (A1, B1 etc representing dates for each Calibre) I think you want.

Row 1 A1 B1 C1 D1
Row 2 A2 B2 C2 D2
...
Row 6 A6 B6 C6 D6

How do you determine each row's content? That is how do you associate A1
with B1 and not with B2?
Your current query seems to return something like the following.

Name, Calibre, Date
xxx, A, DateA1
xxx, A, DateA2
xxx, A, DateA3
...
xxx, D, DateA5
xxx, D, DateA6

A crosstab query could be used to return the one row containing the maximum
or minimum dates for each calibre, but I am stuck on how to get this to
return all 6 rows of data.

Hi,
Have already given the complete information regarding my query.

[quoted text clipped - 52 lines]

 

[John Spencer]

Your query gives all the data you need.

Let's name that one as qBase - note the addition of the parameter line at
the beginning of the query - This is required when you are going to use a
crosstab query.

Parameters [Forms]![LicenseRenewalForm]![cboName] Text(255);
SELECT Scores.Name, Scores.Calibre, Scores.Date
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName
AND (((Scores.Date) In (SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name =
Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

Now using that query we need to rank the dates within each group. Save this
query as qBase2

SELECT qBase.Name
, qBase.Calibre
, qBase.Date
, DCount("*","Qbase","Calibre =""" & qBase.Calibre & """ AND Date < #" &
Format(qBase.Date,"yyyy-mm-dd") & "#") as Rank
FROM qBase

Now using qBase2 we can build a crosstab query

TRANSFORM First(Qbase2.Date) as TheDate
SELECT qBase2.Name, qBase2.Rank
FROM qBase2
GROUP BY qBase2.Name, qBase2.Rank
PIVOT qBase2.Calibre

Since I haven't tested any of these queries you may get syntax errors.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi
Sorry for the late reply. My query is returing the top 6 record for each
calibre.
Below is a sample result of the query:

Basically, I just want the report output to be:
John Spencer
A B C D
7/06/2005 31/07/2005 12/06/2005 6/07/2005
2/08/2005 6/11/2005 10/07/2005 14/08/2005
16/08/2005 26/03/2006 26/03/2006 9/10/2005
23/08/2005 2/04/2006 23/04/2006 11/12/2005
25/10/2005 9/04/2006 30/04/2006 15/01/2006
11/04/2006 30/04/2006

I can't find out how to do the above approach in the report. Please
advise
if you have other option that you can give.

Name Calibre Date
John Spencer A 7/06/2005
John Spencer A 2/08/2005
John Spencer A 16/08/2005
John Spencer A 23/08/2005
John Spencer A 25/10/2005
John Spencer A 11/04/2006
John Spencer B 31/07/2005
John Spencer B 6/11/2005
John Spencer B 26/03/2006
John Spencer B 2/04/2006
John Spencer B 9/04/2006
John Spencer C 12/06/2005
John Spencer C 10/07/2005
John Spencer C 26/03/2006
John Spencer C 23/04/2006
John Spencer C 30/04/2006
John Spencer D 6/07/2005
John Spencer D 14/08/2005
John Spencer D 9/10/2005
John Spencer D 11/12/2005
John Spencer D 15/01/2006
John Spencer D 30/04/2006

The problem is using the IIf Expression defined in the detail section of
the
report per each Calibre:
=IIf([Calibre]="A",[Date],Null) =IIf([Calibre]="B",[Date],Null)
=IIf([Calibre]
="C",[Date],Null) and =IIf([Calibre]="D",[Date],Null) in the detail
section
of the report

It will display the value in the detail section but the layout is
incorrect
the result will be below with unwanted spaces. You may find that there
are
unwanted spaces if it does not match the Condition

John Spencer

Calibre A Calibre B Calibre C Calibre D
07/06/2005
12/06/2005
6/07/2005
10/07/2005
31/07/2005
02/08/2005

14/08/2005
16/08/2005
23/08/2005

9/10/2005
25/10/2005
6/11/2005

11/12/2005
15/01/2006
26/03/2006
26/03/2006
2/04/2006
9/04/2006
11/04/2006
23/04/2006
30/04/2006
30/04/2006
7/06/2005

DANG! I hate when I hit the wrong keys.

I still don't understand.

I think you want to end up with a grid of 4 columns and 6 rows that
contain
dates. What I can't determine is how you want the data determined for
each
row. (A1, B1 etc representing dates for each Calibre) I think you want.

Row 1 A1 B1 C1 D1
Row 2 A2 B2 C2 D2
...
Row 6 A6 B6 C6 D6

How do you determine each row's content? That is how do you associate A1
with B1 and not with B2?
Your current query seems to return something like the following.

Name, Calibre, Date
xxx, A, DateA1
xxx, A, DateA2
xxx, A, DateA3
...
xxx, D, DateA5
xxx, D, DateA6

A crosstab query could be used to return the one row containing the
maximum
or minimum dates for each calibre, but I am stuck on how to get this to
return all 6 rows of data.

Hi,
Have already given the complete information regarding my query.

[quoted text clipped - 52 lines]

thanks

 

[alvarjo9 via AccessMonster.com]

Hi,
Thanks
I ran the query and name it as qbase2
SELECT qBase.Name, qBase.Calibre, qBase.Date, DCount("*","Qbase","Calibre
=""" & qBase.Calibre & """ AND Date < #" & Format(qBase.Date,"yyyy-mm-dd") &
"#") AS Rank
FROM qBase;

But I got an "#Error" result in the Rank field.

Also for the cross tabt query,

TRANSFORM First(Qbase2.Date) as TheDate
SELECT qBase2.Name, qBase2.Rank
FROM qBase2
GROUP BY qBase2.Name, qBase2.Rank
PIVOT qBase2.Calibre

Please advise were will this be created, will it also be from query or in the
report?
I ran this query and got a result of "The Microsoft Jet Datbase engine does
not recognize Scores.Name as a valid field name or expression".

Many thanks for your assistance on this.

Your query gives all the data you need.

Let's name that one as qBase - note the addition of the parameter line at
the beginning of the query - This is required when you are going to use a
crosstab query.

Parameters [Forms]![LicenseRenewalForm]![cboName] Text(255);
SELECT Scores.Name, Scores.Calibre, Scores.Date
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName
AND (((Scores.Date) In (SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name =
Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

Now using that query we need to rank the dates within each group. Save this
query as qBase2

SELECT qBase.Name
, qBase.Calibre
, qBase.Date
, DCount("*","Qbase","Calibre =""" & qBase.Calibre & """ AND Date < #" &
Format(qBase.Date,"yyyy-mm-dd") & "#") as Rank
FROM qBase

Now using qBase2 we can build a crosstab query

TRANSFORM First(Qbase2.Date) as TheDate
SELECT qBase2.Name, qBase2.Rank
FROM qBase2
GROUP BY qBase2.Name, qBase2.Rank
PIVOT qBase2.Calibre

Since I haven't tested any of these queries you may get syntax errors.

Hi
Sorry for the late reply. My query is returing the top 6 record for each

[quoted text clipped - 121 lines]

 

[John Spencer]

Lets make a few changes in the base query to handle the reserved words
you are using as field names

Parameters [Forms]![LicenseRenewalForm]![cboName] Text(255);
SELECT Scores.Name as SName, Scores.Calibre, Scores.Date as SDate
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName
AND (((Scores.Date) In (SELECT TOP 6 X.Date FROM Scores As X
WHERE X.Name = Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date;

Run that query and if it works save it as QBase. If it doesn't then
stop and we will work on getting thie query correct.


Now using that query we need to rank the dates within each group. Save
this query as qBase2

SELECT qBase.SName
, qBase.Calibre
, qBase.SDate
, DCount("*","Qbase","Calibre =""" & qBase.Calibre & """ AND SDate< #" &
Format(qBase.Date,"yyyy-mm-dd) & "#") as Rank
FROM qBase

If this one fails then we need to work on it and get it to work.
Perhaps you will need to use semi-colons as the delimiter in the DCount
statement instead of the US standard comma. If using semi-colons
doesn't work then well need to break down the query and the Dcount into
sections.
-- Drop the DCOUNT and test.
-- Add a simplified Dcount which will give wrong results for rank
DCount("*";"QBase";"Calibre="""& qBase.Calibre)
-- Add the Date criteria into the DCount

Once that is built and working, we can then attempt the Crosstab query.
Each query in the chain must work and yield valid results before you
can use the next one

TRANSFORM First(Qbase2.SDate) as TheDate
SELECT qBase2.SName, qBase2.Rank
FROM qBase2
GROUP BY qBase2.SName, qBase2.Rank
PIVOT qBase2.Calibre



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
Thanks
I ran the query and name it as qbase2
SELECT qBase.Name, qBase.Calibre, qBase.Date, DCount("*","Qbase","Calibre
=""" & qBase.Calibre & """ AND Date < #" & Format(qBase.Date,"yyyy-mm-dd") &
"#") AS Rank
FROM qBase;

But I got an "#Error" result in the Rank field.

Also for the cross tabt query,

TRANSFORM First(Qbase2.Date) as TheDate
SELECT qBase2.Name, qBase2.Rank
FROM qBase2
GROUP BY qBase2.Name, qBase2.Rank
PIVOT qBase2.Calibre

Please advise were will this be created, will it also be from query or in the
report?
I ran this query and got a result of "The Microsoft Jet Datbase engine does
not recognize Scores.Name as a valid field name or expression".

Many thanks for your assistance on this.

Your query gives all the data you need.

Let's name that one as qBase - note the addition of the parameter line at
the beginning of the query - This is required when you are going to use a
crosstab query.

Parameters [Forms]![LicenseRenewalForm]![cboName] Text(255);
SELECT Scores.Name, Scores.Calibre, Scores.Date
FROM Scores
WHERE Scores.Name=Forms!LicenseRenewalForm!cboName
AND (((Scores.Date) In (SELECT TOP 6 X.Date FROM Scores As X WHERE X.Name =
Scores.Name And X.Calibre = Scores.Calibre)))
GROUP BY Scores.Name, Scores.Calibre, Scores.Date, Scores.Date;

Now using that query we need to rank the dates within each group. Save this
query as qBase2

SELECT qBase.Name
, qBase.Calibre
, qBase.Date
, DCount("*","Qbase","Calibre =""" & qBase.Calibre & """ AND Date < #" &
Format(qBase.Date,"yyyy-mm-dd") & "#") as Rank
FROM qBase
Now using qBase2 we can build a crosstab query

TRANSFORM First(Qbase2.Date) as TheDate
SELECT qBase2.Name, qBase2.Rank
FROM qBase2
GROUP BY qBase2.Name, qBase2.Rank
PIVOT qBase2.Calibre

Since I haven't tested any of these queries you may get syntax errors.

Hi
Sorry for the late reply. My query is returing the top 6 record for each

[quoted text clipped - 121 lines]

thanks