473,883 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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<>'CO NTRACTS'".

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.A mount) AS [The Value]
SELECT mapTransfers.De partment, mapTransfers.Di vision,
dicPayCodes.Cat egory2<>'CONTRA CTS' AS CatContractsYN,
dicPayCodes.Cat egory2, Sum(ytdIA2004.A mount) 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.De partment, mapTransfers.Di vision,
dicPayCodes.Cat egory2<>'CONTRA CTS', dicPayCodes.Cat egory2
PIVOT Format([Date],'mmm') In
('Jul','Aug','S ep','Oct','Nov' ,'Dec','Jan','F eb','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<>'CON TRACTS'
(which evaluates to "-1") in the first group and one record with
Category2='CONT RACTS' (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='CONT RACTS') 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
Nov 13 '05 #1
3 2754
The query conatins a calculated field named CatContractsYN.

Try using that name in the Sorting'n'Group ing 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******@hotma il.com> wrote in message
news:a2******** *************** ***@posting.goo gle.com...

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<>'CO NTRACTS'".

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.A mount) AS [The Value]
SELECT mapTransfers.De partment, mapTransfers.Di vision,
dicPayCodes.Cat egory2<>'CONTRA CTS' AS CatContractsYN,
dicPayCodes.Cat egory2, Sum(ytdIA2004.A mount) 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.De partment, mapTransfers.Di vision,
dicPayCodes.Cat egory2<>'CONTRA CTS', dicPayCodes.Cat egory2
PIVOT Format([Date],'mmm') In
('Jul','Aug','S ep','Oct','Nov' ,'Dec','Jan','F eb','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<>'CON TRACTS'
(which evaluates to "-1") in the first group and one record with
Category2='CONT RACTS' (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='CONT RACTS') 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

Nov 13 '05 #2
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<>'CON TRACTS'). 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*********@Se eSig.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'Group ing 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******@hotma il.com> wrote in message
news:a2******** *************** ***@posting.goo gle.com...

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<>'CO NTRACTS'".

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.A mount) AS [The Value]
SELECT mapTransfers.De partment, mapTransfers.Di vision,
dicPayCodes.Cat egory2<>'CONTRA CTS' AS CatContractsYN,
dicPayCodes.Cat egory2, Sum(ytdIA2004.A mount) 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.De partment, mapTransfers.Di vision,
dicPayCodes.Cat egory2<>'CONTRA CTS', dicPayCodes.Cat egory2
PIVOT Format([Date],'mmm') In
('Jul','Aug','S ep','Oct','Nov' ,'Dec','Jan','F eb','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<>'CON TRACTS'
(which evaluates to "-1") in the first group and one record with
Category2='CONT RACTS' (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='CONT RACTS') 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

Nov 13 '05 #3
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.Cat egory2='Contrac ts'
and
"No" otherwise. My report(s) are now selecting this field and
grouping by
it instead of the expression "dicPayCodes.Ca tegory2<>'CONTR ACTS'.

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******@hotmai l.com (ah******@hotma il.com) wrote in message news:<a2******* *************** **@posting.goog le.com>...
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<>'CON TRACTS'). 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*********@Se eSig.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'Group ing 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******@hotma il.com> wrote in message
news:a2******** *************** ***@posting.goo gle.com...

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<>'CO NTRACTS'".

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.A mount) AS [The Value]
SELECT mapTransfers.De partment, mapTransfers.Di vision,
dicPayCodes.Cat egory2<>'CONTRA CTS' AS CatContractsYN,
dicPayCodes.Cat egory2, Sum(ytdIA2004.A mount) 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.De partment, mapTransfers.Di vision,
dicPayCodes.Cat egory2<>'CONTRA CTS', dicPayCodes.Cat egory2
PIVOT Format([Date],'mmm') In
('Jul','Aug','S ep','Oct','Nov' ,'Dec','Jan','F eb','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<>'CON TRACTS'
(which evaluates to "-1") in the first group and one record with
Category2='CONT RACTS' (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='CONT RACTS') 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

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1190
by: David W. Fenton | last post by:
I was creating a very simple report today, a deposit slip report that displays payments grouped by date of payment and then by payment type -- very straightforward, the kind of report I've created a gazillion times. I realized I wanted to have the payment date and payment type in the inner header (for payment type), so I moved the controls on the payment date header into the payment type header. When I ran the report, it took...
0
1105
by: Jim Allard | last post by:
Hi, I have an A97 database that has been in use for some 6 years now. There are two reports that each generate an xls document for import into an accounting package. There are two date fields in each report. I have been using the following as the on click code to generate each report: Private Sub NewChangeXLS_Click() On Error GoTo Err_NewChangeXLS_Click
3
1517
by: John Baker | last post by:
Hi: I have, I hope, a trivial formatting issue. I have a field that I wish to format as 12.30 (00.00). IN other words two digits decimal point and two digits. Using the format command (in a query), I run into a snag: When I set up the command as: Format(,"nn.nn")
6
1603
by: Dave | last post by:
I was curious in regards to Access 12; specifically more fluent report formatting. I'm an average user, but from my end, I find more and more need for cleaner reports....and do not always want to merge with Word. Although I believe my reports are very clean and well laid out, they always tend to look more like an office form, with underlined spaces that require to be filled in. As an Example, I just built a report for Wills, and a...
3
1929
by: lmawler | last post by:
Hi everyone, I'm making reports that are based on queries, and the data has several levels of grouping. I have set up a report with several nested group headers, but I'm currently stuck where either my data shows up multiple times (in the case that I have the data in the header section) or it has a big gap in spacing (if it's in the detail section). I've narrowed down the problem to the fact that I have parents (group headers) that...
5
3071
by: CarrieR | last post by:
I am working within a DB I have been devolping in Access 2003, and have encountered a report coding issue I was hoping someone could help me with: I have 18 reports, each running off some smaller queried version of 2 main queries (apx. 6000 records each). The purposes of the DB and the reports require the formatting of these reports to change monthly as Actual Revenue is reported. I can accomplish all of these monthly formatting changes...
4
4239
by: lupo666 | last post by:
Hi everybody, this time I have three problems driving me nuts :-((( (1) I have a report with 20 or so Yes/No "squares". Is there a way to either hide/show the "square" or change the yes/no relative textbox, depending on value? (2)
1
1397
by: cbnewman | last post by:
What is the best way to accomplish the following: I would like to generate a report that changes the text size and italicization based on a boolean variable (present in the row of the data source returned by a stored query). In other words, the report runs and if the boolean decrease_emphasis is true, then that row in the report is printed in a smaller, italicized font.
10
2161
by: sara | last post by:
Hi - I have a report that is 14 columnar sub-reports (Line up: Position- holders in each of our 14 locations - Manager, Assistant Manager, Receiving, Office, etc). I output directly to PDF (using ConvertReportToPDF from this site - Stephan Lebans) The formatting - Outlines on some fields - do not appear on subreports
0
11128
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10838
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9568
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7964
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7119
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5794
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5988
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4212
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.