468,249 Members | 1,496 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,249 developers. It's quick & easy.

Report - All Records on one Page/Table Format

How do I get all fields on one page of a report?
I have a report that has a column for each day of the week and 6 records for
each day. I need each weekday's records returned on only one detail page.
Instead I am getting a new table layout for each day of the week. I have tried
grouping on every record and combination I can, manipulating the Group
Properties but can't get it right. I have the entire report in the Detail
Section, with the Week Starting date (Monday), VehicleID, and BattID values
listed in the report once at the top. The queries WHERE clause is based on the
Monday - Sunday date range, the VehicleID and the BattID.
The STDATE from the query is obviously causing each day to be set on another
detail page. How do I get it all on one page?
I know it has to do with the Grouping of records and Group Properties but
everything I try doesn't work.

My entire report should look like this:

STDATE = 10/27/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 5 10 3 8
# of Charges 2 3 1 7
MinSOC 12.3 31.1 65.5 34.4
MaxTemp 80 79 74 45
FullCharge YES NO YES NO
EQCharge NO NO NO YES

But I get something like this:

FIRST PAGE:
STDATE = 10/27/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 5
# of Charges 2
MinSOC 12.3
MaxTemp 80
FullCharge YES NO
EQCharge NO NO
STDATE = 10/28/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 10
# of Charges 2 3
MinSOC 31.1
MaxTemp 80 79
FullCharge NO NO NO
EQCharge NO NO NO

SECOND PAGE:
STDATE = 10/29/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 3
# of Charges 2 2 1
MinSOC 65.5
MaxTemp 80 79 74
FullCharge NO NO YES
EQCharge NO NO NO
The records returned are based on expressions from a query.
For example:
Monday Tues.
Charge Rtn: =IIf([WkDay]=2,[ChgRtn]) =IIf([WkDay]=3,[ChgRtn])
# of Chgs: =Sum(IIf([WkDay]=2,[SumVid])) =Sum(IIf([WkDay]=3,[SumVid]))
MinSOC: =Min(IIf([WkDay]=2,[MinSTSOC])) =Min(IIf([WkDay]=3,[MinSTSOC]))

Nov 12 '05 #1
3 5841
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You want a summary report - don't put the data in the Detail Section,
put it in a footer section - Report Footer or some other group footer.
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6gWs4echKqOuFEgEQI9GACglbaHYZcpP6sAouXQzPf1m5 xgcFEAnij0
aXYef+EHj1m1XjIEG8vS3RZE
=BHsX
-----END PGP SIGNATURE-----

mark wrote:
How do I get all fields on one page of a report?
I have a report that has a column for each day of the week and 6 records
for each day. I need each weekday's records returned on only one detail
page. Instead I am getting a new table layout for each day of the week.
I have tried grouping on every record and combination I can,
manipulating the Group Properties but can't get it right. I have the
entire report in the Detail Section, with the Week Starting date
(Monday), VehicleID, and BattID values listed in the report once at the
top. The queries WHERE clause is based on the Monday - Sunday date
range, the VehicleID and the BattID.
The STDATE from the query is obviously causing each day to be set on
another detail page. How do I get it all on one page?
I know it has to do with the Grouping of records and Group Properties
but everything I try doesn't work.

My entire report should look like this:

STDATE = 10/27/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 5 10 3 8
# of Charges 2 3 1 7
MinSOC 12.3 31.1 65.5 34.4
MaxTemp 80 79 74 45
FullCharge YES NO YES NO
EQCharge NO NO NO YES

But I get something like this:

FIRST PAGE:
STDATE = 10/27/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 5
# of Charges 2
MinSOC 12.3
MaxTemp 80
FullCharge YES NO
EQCharge NO NO
STDATE = 10/28/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 10
# of Charges 2 3
MinSOC 31.1
MaxTemp 80 79
FullCharge NO NO NO
EQCharge NO NO NO

SECOND PAGE:
STDATE = 10/29/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 3
# of Charges 2 2 1
MinSOC 65.5
MaxTemp 80 79 74
FullCharge NO NO YES
EQCharge NO NO NO
The records returned are based on expressions from a query.
For example:
Monday Tues.
Charge Rtn: =IIf([WkDay]=2,[ChgRtn]) =IIf([WkDay]=3,[ChgRtn])
# of Chgs: =Sum(IIf([WkDay]=2,[SumVid])) =Sum(IIf([WkDay]=3,[SumVid]))
MinSOC: =Min(IIf([WkDay]=2,[MinSTSOC]))
=Min(IIf([WkDay]=3,[MinSTSOC]))

Nov 12 '05 #2
Thanks again MGFoster. I must be missing something because I put everything in
the report footer with no Grouping and it does show on one page but it will not
return some of the values for days other than Monday. The Yes/No values always
return a "No" response, (=Trim(IIf([WkDay]=4 And [EQChg]>0,"Yes","No"))). If
the IIf statement has any other number for WkDay=2 then it doesn't evaluate the
expression at all or properly. I did get some values to show if I added an
aggregate around the entire IIf statement in the ControlSource, like this:
=Sum(IIf([WkDay]=3,[SumVid])). So much time spent determining what doesn't work
but haven't found what works, though I get soo close so often.

Thanks,
mark

MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You want a summary report - don't put the data in the Detail Section,
put it in a footer section - Report Footer or some other group footer.
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6gWs4echKqOuFEgEQI9GACglbaHYZcpP6sAouXQzPf1m5 xgcFEAnij0
aXYef+EHj1m1XjIEG8vS3RZE
=BHsX
-----END PGP SIGNATURE-----

mark wrote:
How do I get all fields on one page of a report?
I have a report that has a column for each day of the week and 6
records for each day. I need each weekday's records returned on only
one detail page. Instead I am getting a new table layout for each day
of the week. I have tried grouping on every record and combination I
can, manipulating the Group Properties but can't get it right. I have
the entire report in the Detail Section, with the Week Starting date
(Monday), VehicleID, and BattID values listed in the report once at
the top. The queries WHERE clause is based on the Monday - Sunday
date range, the VehicleID and the BattID.
The STDATE from the query is obviously causing each day to be set on
another detail page. How do I get it all on one page?
I know it has to do with the Grouping of records and Group Properties
but everything I try doesn't work.

My entire report should look like this:

STDATE = 10/27/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 5 10 3 8
# of Charges 2 3 1 7
MinSOC 12.3 31.1 65.5 34.4
MaxTemp 80 79 74 45
FullCharge YES NO YES NO
EQCharge NO NO NO YES

But I get something like this:

FIRST PAGE:
STDATE = 10/27/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 5 # of Charges 2
MinSOC 12.3 MaxTemp
80 FullCharge YES NO
EQCharge NO NO

STDATE = 10/28/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 10 # of Charges 2 3
MinSOC 31.1 MaxTemp 80
79 FullCharge NO NO NO
EQCharge NO NO NO
SECOND PAGE:
STDATE = 10/29/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 3 # of Charges 2 2 1
MinSOC 65.5 MaxTemp 80
79 74 FullCharge NO NO YES
EQCharge NO NO NO

The records returned are based on expressions from a query.
For example:
Monday Tues.
Charge Rtn: =IIf([WkDay]=2,[ChgRtn]) =IIf([WkDay]=3,[ChgRtn])
# of Chgs: =Sum(IIf([WkDay]=2,[SumVid]))
=Sum(IIf([WkDay]=3,[SumVid]))
MinSOC: =Min(IIf([WkDay]=2,[MinSTSOC]))
=Min(IIf([WkDay]=3,[MinSTSOC]))



Nov 12 '05 #3
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yea, . . . In a summary section you have to put an aggregate function
around all the query values. Since you're using IIf() to determine
which value to put in the Control the IIf() will have to have the
aggregate function around it. E.g.:

=Sum(IIf(WkDay=4,<numeric>, <other numeric>))

Since all aggregates work only on numbers you'll have to change Yes/No
to 1/0 . . .. Hmmm, not sure that is correct - Sum, Count, Avg
wouldn't have any real meaning for the 1/0 possibilities.

It seems you're trying to put single instance values (the "yes/no"
values) among summary values - this will not work. Usually single
instance values go above the summary values. Is there some way to do
that w/ your data/rpt?

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6ntPoechKqOuFEgEQKGgwCg6Z28wfNDNF9e9rOInSSZ2U LT2X8Anio+
NnTGMbihFS5pElfue88sOLbA
=Yy9p
-----END PGP SIGNATURE-----

mark wrote:
Thanks again MGFoster. I must be missing something because I put
everything in the report footer with no Grouping and it does show on one
page but it will not return some of the values for days other than
Monday. The Yes/No values always return a "No" response,
(=Trim(IIf([WkDay]=4 And [EQChg]>0,"Yes","No"))). If the IIf statement
has any other number for WkDay=2 then it doesn't evaluate the expression
at all or properly. I did get some values to show if I added an
aggregate around the entire IIf statement in the ControlSource, like
this: =Sum(IIf([WkDay]=3,[SumVid])). So much time spent determining
what doesn't work but haven't found what works, though I get soo close
so often.

Thanks,
mark

MGFoster wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You want a summary report - don't put the data in the Detail Section,
put it in a footer section - Report Footer or some other group footer.
- --
MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP6gWs4echKqOuFEgEQI9GACglbaHYZcpP6sAouXQzPf1m5 xgcFEAnij0
aXYef+EHj1m1XjIEG8vS3RZE
=BHsX
-----END PGP SIGNATURE-----

mark wrote:
How do I get all fields on one page of a report?
I have a report that has a column for each day of the week and 6
records for each day. I need each weekday's records returned on only
one detail page. Instead I am getting a new table layout for each day
of the week. I have tried grouping on every record and combination I
can, manipulating the Group Properties but can't get it right. I
have the entire report in the Detail Section, with the Week Starting
date (Monday), VehicleID, and BattID values listed in the report once
at the top. The queries WHERE clause is based on the Monday - Sunday
date range, the VehicleID and the BattID.
The STDATE from the query is obviously causing each day to be set on
another detail page. How do I get it all on one page?
I know it has to do with the Grouping of records and Group Properties
but everything I try doesn't work.

My entire report should look like this:

STDATE = 10/27/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 5 10 3 8
# of Charges 2 3 1 7
MinSOC 12.3 31.1 65.5 34.4
MaxTemp 80 79 74 45
FullCharge YES NO YES NO
EQCharge NO NO NO YES

But I get something like this:

FIRST PAGE:
STDATE = 10/27/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 5 # of Charges 2
MinSOC 12.3 MaxTemp
80 FullCharge YES NO
EQCharge NO NO
STDATE = 10/28/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 10 # of Charges 2 3
MinSOC 31.1 MaxTemp 80
79 FullCharge NO NO NO
EQCharge NO NO NO SECOND PAGE:
STDATE = 10/29/03 VehicleID = 1 BattID = 1
Monday Tues. Weds. Thurs. etc...
Charge Rtn 3 # of Charges 2
2 1
MinSOC 65.5 MaxTemp 80
79 74 FullCharge NO NO
YES EQCharge NO NO NO
The records returned are based on expressions from a query.
For example:
Monday Tues.
Charge Rtn: =IIf([WkDay]=2,[ChgRtn]) =IIf([WkDay]=3,[ChgRtn])
# of Chgs: =Sum(IIf([WkDay]=2,[SumVid]))
=Sum(IIf([WkDay]=3,[SumVid]))
MinSOC: =Min(IIf([WkDay]=2,[MinSTSOC]))
=Min(IIf([WkDay]=3,[MinSTSOC]))



Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Nathan Bloomfield | last post: by
8 posts views Thread by Armando | last post: by
4 posts views Thread by lupo666 | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.