A2K: Report formatting issue grouping on "Fld<>'Value'"

[ahaque38]

Hello.

Using A2K SP3, I am having the following problem with a report using
"Sorting and Grouping".

I have recently added a grouping in the reports for
"Category2<>'CONTRACTS'".

I have reports at the plan (overall totals), department and division
levels which have sorting and grouping implemented with this new
grouping. The first
two reports work as expected. However, for the division level report,
the Access report is not grouping as expected. See details below.

For the report which does not work, the source is an SQL statement as
follows...

TRANSFORM Sum(ytdIA2004.Amount) AS [The Value]
SELECT mapTransfers.Department, mapTransfers.Division,
dicPayCodes.Category2<>'CONTRACTS' AS CatContractsYN,
dicPayCodes.Category2, Sum(ytdIA2004.Amount) AS [Year To Date]
FROM (ytdIA2004 LEFT JOIN dicPayCodes ON ytdIA2004.[PayCode#] =
dicPayCodes.[PayCode#])
LEFT JOIN mapTransfers ON (ytdIA2004.[IDX Program] =
mapTransfers.[IDX Program]) AND
(ytdIA2004.[IDX Billing Area] = mapTransfers.[IDX Billing Area]) AND
(ytdIA2004.[IDX Division] = mapTransfers.[IDX Division])
WHERE (ytdIA2004.[Income Type] = 'Payments' OR ytdIA2004.[Income Type]
= 'Refunds') AND
ytdIA2004.[PayCode#] Is Not Null AND ytdIA2004.[Date] <= #05/01/2004#
GROUP BY mapTransfers.Department, mapTransfers.Division,
dicPayCodes.Category2<>'CONTRACTS', dicPayCodes.Category2
PIVOT Format([Date],'mmm') In
('Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb',' Mar','Apr','May','Jun');

The Access report has the following "sorting and grouping"
specifications:

Department Ascending (with no group header or footer)
Division Ascending (with no group header but a group footer)
CatContractsYN Ascending (with both a group header and group footer)
Category2 Ascending (with no group header or footer)

For all of the above, the other settings are as follows
Group On: Each Value
Interval: 1
Keep Together: No

I am expecting to see multiple records with Category2<>'CONTRACTS'
(which evaluates to "-1") in the first group and one record with
Category2='CONTRACTS' (which evalueates to '0') in the second group
for "CatContractsYN". This works for the plan and department level
reports.

However, for this division level report, I see only one record in the
first group (for the first department & division on the report) and
one record (which is not for Category2='CONTRACTS') in the second
group. In addition, if I go to page 3 of the report, I get multiple
"No current record" messages. Somehow,
the report's source data is not being matched up correctly to the
report definition.

If I take out the header AND footer for the CatContractsYN group, the
report groups correctly. However, putting in a header or footer for
this group results in the problem.

I have tried running the source query independently and it seems to
group and sort as expected. The issue seems to be with the report
design.

I would appreciate any ideas.

Thanks,
Ahsan

[Allen Browne]

The query conatins a calculated field named CatContractsYN.

Try using that name in the Sorting'n'Grouping dialog instead of the
expression.

BTW, you will get 3 groups of records. The field is on the outer side of the
join, so even if it is a required field it can have 3 possible values in the
query: Null, True, and False.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ah******@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...

Using A2K SP3, I am having the following problem with a report using
"Sorting and Grouping".

I have recently added a grouping in the reports for
"Category2<>'CONTRACTS'".

I have reports at the plan (overall totals), department and division
levels which have sorting and grouping implemented with this new
grouping. The first
two reports work as expected. However, for the division level report,
the Access report is not grouping as expected. See details below.

For the report which does not work, the source is an SQL statement as
follows...

TRANSFORM Sum(ytdIA2004.Amount) AS [The Value]
SELECT mapTransfers.Department, mapTransfers.Division,
dicPayCodes.Category2<>'CONTRACTS' AS CatContractsYN,
dicPayCodes.Category2, Sum(ytdIA2004.Amount) AS [Year To Date]
FROM (ytdIA2004 LEFT JOIN dicPayCodes ON ytdIA2004.[PayCode#] =
dicPayCodes.[PayCode#])
LEFT JOIN mapTransfers ON (ytdIA2004.[IDX Program] =
mapTransfers.[IDX Program]) AND
(ytdIA2004.[IDX Billing Area] = mapTransfers.[IDX Billing Area]) AND
(ytdIA2004.[IDX Division] = mapTransfers.[IDX Division])
WHERE (ytdIA2004.[Income Type] = 'Payments' OR ytdIA2004.[Income Type]
= 'Refunds') AND
ytdIA2004.[PayCode#] Is Not Null AND ytdIA2004.[Date] <= #05/01/2004#
GROUP BY mapTransfers.Department, mapTransfers.Division,
dicPayCodes.Category2<>'CONTRACTS', dicPayCodes.Category2
PIVOT Format([Date],'mmm') In
('Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb',' Mar','Apr','May','Jun');

The Access report has the following "sorting and grouping"
specifications:

Department Ascending (with no group header or footer)
Division Ascending (with no group header but a group footer)
CatContractsYN Ascending (with both a group header and group footer)
Category2 Ascending (with no group header or footer)

For all of the above, the other settings are as follows
Group On: Each Value
Interval: 1
Keep Together: No

I am expecting to see multiple records with Category2<>'CONTRACTS'
(which evaluates to "-1") in the first group and one record with
Category2='CONTRACTS' (which evalueates to '0') in the second group
for "CatContractsYN". This works for the plan and department level
reports.

However, for this division level report, I see only one record in the
first group (for the first department & division on the report) and
one record (which is not for Category2='CONTRACTS') in the second
group. In addition, if I go to page 3 of the report, I get multiple
"No current record" messages. Somehow,
the report's source data is not being matched up correctly to the
report definition.

If I take out the header AND footer for the CatContractsYN group, the
report groups correctly. However, putting in a header or footer for
this group results in the problem.

I have tried running the source query independently and it seems to
group and sort as expected. The issue seems to be with the report
design.

I would appreciate any ideas.

Thanks,
Ahsan

[ahaque38]

Hello.

The "Sorting and Grouping" dialog is using the calculated field
"CatContractsYN". When the query is run directly, it results in 1 or
2 groups for all the divisions (with 2 groups for the first division
output on the report). However, I understand your note about a
possible third Null outcome. I'll check into how to ensure 2 or less
outcomes...

The thing to note is that the same approach for the Plan and
Department level reports works.

The lines of data which show up in the 2 groups on the first page and
the 1 group on the second page of the report are all supposed to be in
the first group (first dept-division combination for
Category2<>'CONTRACTS'). Also, the totals for the first group, second
group and overall division total are all correct for the first
dept-division combination. It is somehow just not putting the details
into the correct groups (based on the calculated field
"CatContractsYN") when a header or footer (even if it's blank) is
specified for this group.

Thanks for any more input.

Ahsan

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@per-qv1-newsreader-01.iinet.net.au>...

The query conatins a calculated field named CatContractsYN.

Try using that name in the Sorting'n'Grouping dialog instead of the
expression.

BTW, you will get 3 groups of records. The field is on the outer side of the
join, so even if it is a required field it can have 3 possible values in the
query: Null, True, and False.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ah******@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...

Using A2K SP3, I am having the following problem with a report using
"Sorting and Grouping".

I have recently added a grouping in the reports for
"Category2<>'CONTRACTS'".

I have reports at the plan (overall totals), department and division
levels which have sorting and grouping implemented with this new
grouping. The first two reports work as expected. However, for the
division level report, the Access report is not grouping as expected.
See details below.

For the report which does not work, the source is an SQL statement as
follows...

TRANSFORM Sum(ytdIA2004.Amount) AS [The Value]
SELECT mapTransfers.Department, mapTransfers.Division,
dicPayCodes.Category2<>'CONTRACTS' AS CatContractsYN,
dicPayCodes.Category2, Sum(ytdIA2004.Amount) AS [Year To Date]
FROM (ytdIA2004 LEFT JOIN dicPayCodes ON ytdIA2004.[PayCode#] =
dicPayCodes.[PayCode#])
LEFT JOIN mapTransfers ON (ytdIA2004.[IDX Program] =
mapTransfers.[IDX Program]) AND
(ytdIA2004.[IDX Billing Area] = mapTransfers.[IDX Billing Area]) AND
(ytdIA2004.[IDX Division] = mapTransfers.[IDX Division])
WHERE (ytdIA2004.[Income Type] = 'Payments' OR ytdIA2004.[Income Type]
= 'Refunds') AND
ytdIA2004.[PayCode#] Is Not Null AND ytdIA2004.[Date] <= #05/01/2004#
GROUP BY mapTransfers.Department, mapTransfers.Division,
dicPayCodes.Category2<>'CONTRACTS', dicPayCodes.Category2
PIVOT Format([Date],'mmm') In
('Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb',' Mar','Apr','May','Jun');

The Access report has the following "sorting and grouping"
specifications:

Department Ascending (with no group header or footer)
Division Ascending (with no group header but a group footer)
CatContractsYN Ascending (with both a group header and group footer)
Category2 Ascending (with no group header or footer)

For all of the above, the other settings are as follows
Group On: Each Value
Interval: 1
Keep Together: No

I am expecting to see multiple records with Category2<>'CONTRACTS'
(which evaluates to "-1") in the first group and one record with
Category2='CONTRACTS' (which evalueates to '0') in the second group
for "CatContractsYN". This works for the plan and department level
reports.

However, for this division level report, I see only one record in the
first group (for the first department & division on the report) and
one record (which is not for Category2='CONTRACTS') in the second
group. In addition, if I go to page 3 of the report, I get multiple
"No current record" messages. Somehow, the report's source data is not
being matched up correctly to the report definition.

If I take out the header AND footer for the CatContractsYN group, the
report groups correctly. However, putting in a header or footer for
this group results in the problem.

I have tried running the source query independently and it seems to
group and sort as expected. The issue seems to be with the report
design.

I would appreciate any ideas.

Thanks,
Ahsan

[ahaque38]

Hello.

I wanted to let you know that I came up with a workaround for this
issue.

It seems that this scenario doesn't work because it involves grouping
(with a header or footer) on an expression which can result in True
(-1), False (0) or Null IF this is done on the third grouping level
(or later). I had the same error when I added a Yes/No field in the
dicPaycodes table which indicated if Category2 is 'Contracts' or not
and used this field to group on.

What I had to do was to add a text field (length 5) in dicPayCodes.
This field
(Cat2Contracts) has value "Yes" if dicPayCodes.Category2='Contracts'
and
"No" otherwise. My report(s) are now selecting this field and
grouping by
it instead of the expression "dicPayCodes.Category2<>'CONTRACTS'.

This will require us to update this field when the value for Category2
is
changed (or set for a new record). This will be done via the
AfterUpdate()
method for the Category2 control on the PayCodes form (where users add
or
edit records).

If you come up with a possible solution for the original problem,
please let
me know.

Thanks,
Ahsan

ah******@hotmail.com (ah******@hotmail.com) wrote in message news:<a2************************@posting.google.co m>...

Hello.

The "Sorting and Grouping" dialog is using the calculated field
"CatContractsYN". When the query is run directly, it results in 1 or
2 groups for all the divisions (with 2 groups for the first division
output on the report). However, I understand your note about a
possible third Null outcome. I'll check into how to ensure 2 or less
outcomes...

The thing to note is that the same approach for the Plan and
Department level reports works.

The lines of data which show up in the 2 groups on the first page and
the 1 group on the second page of the report are all supposed to be in
the first group (first dept-division combination for
Category2<>'CONTRACTS'). Also, the totals for the first group, second
group and overall division total are all correct for the first
dept-division combination. It is somehow just not putting the details
into the correct groups (based on the calculated field
"CatContractsYN") when a header or footer (even if it's blank) is
specified for this group.

Thanks for any more input.

Ahsan

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@per-qv1-newsreader-01.iinet.net.au>...

The query conatins a calculated field named CatContractsYN.

Try using that name in the Sorting'n'Grouping dialog instead of the
expression.

BTW, you will get 3 groups of records. The field is on the outer side of the
join, so even if it is a required field it can have 3 possible values in the
query: Null, True, and False.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ah******@hotmail.com> wrote in message
news:a2**************************@posting.google.c om...

Using A2K SP3, I am having the following problem with a report using
"Sorting and Grouping".

I have recently added a grouping in the reports for
"Category2<>'CONTRACTS'".

I have reports at the plan (overall totals), department and division
levels which have sorting and grouping implemented with this new
grouping. The first two reports work as expected. However, for the
division level report, the Access report is not grouping as expected.
See details below.

For the report which does not work, the source is an SQL statement as
follows...

TRANSFORM Sum(ytdIA2004.Amount) AS [The Value]
SELECT mapTransfers.Department, mapTransfers.Division,
dicPayCodes.Category2<>'CONTRACTS' AS CatContractsYN,
dicPayCodes.Category2, Sum(ytdIA2004.Amount) AS [Year To Date]
FROM (ytdIA2004 LEFT JOIN dicPayCodes ON ytdIA2004.[PayCode#] =
dicPayCodes.[PayCode#])
LEFT JOIN mapTransfers ON (ytdIA2004.[IDX Program] =
mapTransfers.[IDX Program]) AND
(ytdIA2004.[IDX Billing Area] = mapTransfers.[IDX Billing Area]) AND
(ytdIA2004.[IDX Division] = mapTransfers.[IDX Division])
WHERE (ytdIA2004.[Income Type] = 'Payments' OR ytdIA2004.[Income Type]
= 'Refunds') AND
ytdIA2004.[PayCode#] Is Not Null AND ytdIA2004.[Date] <= #05/01/2004#
GROUP BY mapTransfers.Department, mapTransfers.Division,
dicPayCodes.Category2<>'CONTRACTS', dicPayCodes.Category2
PIVOT Format([Date],'mmm') In
('Jul','Aug','Sep','Oct','Nov','Dec','Jan','Feb',' Mar','Apr','May','Jun');

The Access report has the following "sorting and grouping"
specifications:

Department Ascending (with no group header or footer)
Division Ascending (with no group header but a group footer)
CatContractsYN Ascending (with both a group header and group footer)
Category2 Ascending (with no group header or footer)

For all of the above, the other settings are as follows
Group On: Each Value
Interval: 1
Keep Together: No

I am expecting to see multiple records with Category2<>'CONTRACTS'
(which evaluates to "-1") in the first group and one record with
Category2='CONTRACTS' (which evalueates to '0') in the second group
for "CatContractsYN". This works for the plan and department level
reports.

However, for this division level report, I see only one record in the
first group (for the first department & division on the report) and
one record (which is not for Category2='CONTRACTS') in the second
group. In addition, if I go to page 3 of the report, I get multiple
"No current record" messages. Somehow, the report's source data is not
being matched up correctly to the report definition.

If I take out the header AND footer for the CatContractsYN group, the
report groups correctly. However, putting in a header or footer for
this group results in the problem.

I have tried running the source query independently and it seems to
group and sort as expected. The issue seems to be with the report
design.

I would appreciate any ideas.

Thanks,
Ahsan