Weird Problem with a Subreport

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels

Have you tried using a Union query to join the two lists together and using
that as the record source of the report?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels

Will that allow me to keep the two lists seperate? They can't be mixed
together. Also would it be possible to put a label before the second list
starts?

--
Chels


"Klatuu" wrote:

Have you tried using a Union query to join the two lists together and using
that as the record source of the report?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels

The two lists are separate. That is why you could use a Union query to put
the together on one report. You will have to make sure you can order the
union query so the lists are in the order you want. Also, to present a lable
between lists and to keep the list separate, you could use the report
Sorting and Grouping and group by the list identifier. The use a Group
Header and you can put a label in the group header.

Here is an example of how to use the Union Query to identify the lists:

SELECT 0 AS ListID, SomeField, AnotherField, AnyField FROM FirstList;
UNION ALL
SELECT 1 As ListID, SomeField, AnotherField, AnyField FROM SecondList;

The output of your query will now have a field named ListID. You would
group on ListID and sort on ListID in the report.

Here is an example where I do exactly this in a report. Note the first
calculated field is used to sort the two and the second field is what I use
for the Group Header Labels:

SELECT 0 As GroupType, "Active Contracts With No Start Date" As
Group_Description, ContractType, ClientName, PropertyName FROM
qselMonActRptNoStart;
UNION ALL SELECT 1 As GroupType, "InActive Contracts With No End Date" As
Group_Description, ContractType, ClientName,PropertyName FROM
qselMonActRptNoEnd;

--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Will that allow me to keep the two lists seperate? They can't be mixed
together. Also would it be possible to put a label before the second list
starts?

--
Chels


"Klatuu" wrote:

Have you tried using a Union query to join the two lists together and using
that as the record source of the report?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels

Thank you so much! Using the union query to identify the lists worked
wonderfully. I am running into more problems now though.

I have another field I usually put in my queries just for sorting to keep
everything in order because the Unit #s dont keep themselves in the proper
order. How can i tell the query to sort with a field but not show it? When I
view the query everything is in the right order but when I view the report
they're not. Also I am a little confused as to how to do the grouping so
there is a header between the two lists. I did find the sorting and grouping
and i told it to group based on List ID. This is what I have

ListID Group Properties: ( I have List ID sorting Ascending, I dont know if
I should but I can't figure out how to tell it not to sort)
Group Header: Yes
Group Footer: No
Group On: Each value (Is this correct?)
Group Interval: 1 (?)
Keep together: No

I have a header of shutdown in front of the whole list but the two lists
arent sorting properly or seperating like they are in the query list.
--
Chels


"Klatuu" wrote:

The two lists are separate. That is why you could use a Union query to put
the together on one report. You will have to make sure you can order the
union query so the lists are in the order you want. Also, to present a lable
between lists and to keep the list separate, you could use the report
Sorting and Grouping and group by the list identifier. The use a Group
Header and you can put a label in the group header.

Here is an example of how to use the Union Query to identify the lists:

SELECT 0 AS ListID, SomeField, AnotherField, AnyField FROM FirstList;
UNION ALL
SELECT 1 As ListID, SomeField, AnotherField, AnyField FROM SecondList;

The output of your query will now have a field named ListID. You would
group on ListID and sort on ListID in the report.

Here is an example where I do exactly this in a report. Note the first
calculated field is used to sort the two and the second field is what I use
for the Group Header Labels:

SELECT 0 As GroupType, "Active Contracts With No Start Date" As
Group_Description, ContractType, ClientName, PropertyName FROM
qselMonActRptNoStart;
UNION ALL SELECT 1 As GroupType, "InActive Contracts With No End Date" As
Group_Description, ContractType, ClientName,PropertyName FROM
qselMonActRptNoEnd;

--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Will that allow me to keep the two lists seperate? They can't be mixed
together. Also would it be possible to put a label before the second list
starts?

--
Chels


"Klatuu" wrote:

Have you tried using a Union query to join the two lists together and using
that as the record source of the report?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels

You group settings are correct. You should be grouping by GroupType. The
field I showed in my example that separates the list. You can also use the
sorting and grouping to order the groups. Just create another group below
the first group, but don't identify any headers or footers for the group.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Thank you so much! Using the union query to identify the lists worked
wonderfully. I am running into more problems now though.

I have another field I usually put in my queries just for sorting to keep
everything in order because the Unit #s dont keep themselves in the proper
order. How can i tell the query to sort with a field but not show it? When I
view the query everything is in the right order but when I view the report
they're not. Also I am a little confused as to how to do the grouping so
there is a header between the two lists. I did find the sorting and grouping
and i told it to group based on List ID. This is what I have

ListID Group Properties: ( I have List ID sorting Ascending, I dont know if
I should but I can't figure out how to tell it not to sort)
Group Header: Yes
Group Footer: No
Group On: Each value (Is this correct?)
Group Interval: 1 (?)
Keep together: No

I have a header of shutdown in front of the whole list but the two lists
arent sorting properly or seperating like they are in the query list.
--
Chels


"Klatuu" wrote:

The two lists are separate. That is why you could use a Union query to put
the together on one report. You will have to make sure you can order the
union query so the lists are in the order you want. Also, to present a lable
between lists and to keep the list separate, you could use the report
Sorting and Grouping and group by the list identifier. The use a Group
Header and you can put a label in the group header.

Here is an example of how to use the Union Query to identify the lists:

SELECT 0 AS ListID, SomeField, AnotherField, AnyField FROM FirstList;
UNION ALL
SELECT 1 As ListID, SomeField, AnotherField, AnyField FROM SecondList;

The output of your query will now have a field named ListID. You would
group on ListID and sort on ListID in the report.

Here is an example where I do exactly this in a report. Note the first
calculated field is used to sort the two and the second field is what I use
for the Group Header Labels:

SELECT 0 As GroupType, "Active Contracts With No Start Date" As
Group_Description, ContractType, ClientName, PropertyName FROM
qselMonActRptNoStart;
UNION ALL SELECT 1 As GroupType, "InActive Contracts With No End Date" As
Group_Description, ContractType, ClientName,PropertyName FROM
qselMonActRptNoEnd;

--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Will that allow me to keep the two lists seperate? They can't be mixed
together. Also would it be possible to put a label before the second list
starts?

--
Chels


"Klatuu" wrote:

Have you tried using a Union query to join the two lists together and using
that as the record source of the report?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels

OK you lost me a bit....
So the settings for my grouping are all fine? What do you mean by "You
should be grouping by groupType"?

How do I make a second group below? Am I making these groups to tell them
which records to put in which group? I dont really understand the properties
very much, sorry. Am I telling it to group ListID 0 together and ListID 1 in
another group? Will I need to add anything to my query?

If I am not understanding you sorry I am new to the grouping thing!
--
Chels


"Klatuu" wrote:

You group settings are correct. You should be grouping by GroupType. The
field I showed in my example that separates the list. You can also use the
sorting and grouping to order the groups. Just create another group below
the first group, but don't identify any headers or footers for the group.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Thank you so much! Using the union query to identify the lists worked
wonderfully. I am running into more problems now though.

I have another field I usually put in my queries just for sorting to keep
everything in order because the Unit #s dont keep themselves in the proper
order. How can i tell the query to sort with a field but not show it? When I
view the query everything is in the right order but when I view the report
they're not. Also I am a little confused as to how to do the grouping so
there is a header between the two lists. I did find the sorting and grouping
and i told it to group based on List ID. This is what I have

ListID Group Properties: ( I have List ID sorting Ascending, I dont know if
I should but I can't figure out how to tell it not to sort)
Group Header: Yes
Group Footer: No
Group On: Each value (Is this correct?)
Group Interval: 1 (?)
Keep together: No

I have a header of shutdown in front of the whole list but the two lists
arent sorting properly or seperating like they are in the query list.
--
Chels


"Klatuu" wrote:

The two lists are separate. That is why you could use a Union query to put
the together on one report. You will have to make sure you can order the
union query so the lists are in the order you want. Also, to present a lable
between lists and to keep the list separate, you could use the report
Sorting and Grouping and group by the list identifier. The use a Group
Header and you can put a label in the group header.

Here is an example of how to use the Union Query to identify the lists:

SELECT 0 AS ListID, SomeField, AnotherField, AnyField FROM FirstList;
UNION ALL
SELECT 1 As ListID, SomeField, AnotherField, AnyField FROM SecondList;

The output of your query will now have a field named ListID. You would
group on ListID and sort on ListID in the report.

Here is an example where I do exactly this in a report. Note the first
calculated field is used to sort the two and the second field is what I use
for the Group Header Labels:

SELECT 0 As GroupType, "Active Contracts With No Start Date" As
Group_Description, ContractType, ClientName, PropertyName FROM
qselMonActRptNoStart;
UNION ALL SELECT 1 As GroupType, "InActive Contracts With No End Date" As
Group_Description, ContractType, ClientName,PropertyName FROM
qselMonActRptNoEnd;

--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Will that allow me to keep the two lists seperate? They can't be mixed
together. Also would it be possible to put a label before the second list
starts?

--
Chels


"Klatuu" wrote:

Have you tried using a Union query to join the two lists together and using
that as the record source of the report?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels

OK scratch that! The grouping is working.... I didnt have to change anything
I just didnt notice it was working the first time. What confused me is it is
putting the group header in front of both groups. I only want to header in
front of the second group. How do I get the first header to go away? Do I
need to put that first list in it's own group without a header?

I still dont know what to do about the sorting though.... should i add my
sorting field to the query? If so can I tell the query not to make the
sorting field visible or do I need to do that in the report like I did with
the List ID field?
--
Chels


"Chels" wrote:

OK you lost me a bit....
So the settings for my grouping are all fine? What do you mean by "You
should be grouping by groupType"?

How do I make a second group below? Am I making these groups to tell them
which records to put in which group? I dont really understand the properties
very much, sorry. Am I telling it to group ListID 0 together and ListID 1 in
another group? Will I need to add anything to my query?

If I am not understanding you sorry I am new to the grouping thing!
--
Chels


"Klatuu" wrote:

You group settings are correct. You should be grouping by GroupType. The
field I showed in my example that separates the list. You can also use the
sorting and grouping to order the groups. Just create another group below
the first group, but don't identify any headers or footers for the group.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Thank you so much! Using the union query to identify the lists worked
wonderfully. I am running into more problems now though.

I have another field I usually put in my queries just for sorting to keep
everything in order because the Unit #s dont keep themselves in the proper
order. How can i tell the query to sort with a field but not show it? When I
view the query everything is in the right order but when I view the report
they're not. Also I am a little confused as to how to do the grouping so
there is a header between the two lists. I did find the sorting and grouping
and i told it to group based on List ID. This is what I have

ListID Group Properties: ( I have List ID sorting Ascending, I dont know if
I should but I can't figure out how to tell it not to sort)
Group Header: Yes
Group Footer: No
Group On: Each value (Is this correct?)
Group Interval: 1 (?)
Keep together: No

I have a header of shutdown in front of the whole list but the two lists
arent sorting properly or seperating like they are in the query list.
--
Chels


"Klatuu" wrote:

The two lists are separate. That is why you could use a Union query to put
the together on one report. You will have to make sure you can order the
union query so the lists are in the order you want. Also, to present a lable
between lists and to keep the list separate, you could use the report
Sorting and Grouping and group by the list identifier. The use a Group
Header and you can put a label in the group header.

Here is an example of how to use the Union Query to identify the lists:

SELECT 0 AS ListID, SomeField, AnotherField, AnyField FROM FirstList;
UNION ALL
SELECT 1 As ListID, SomeField, AnotherField, AnyField FROM SecondList;

The output of your query will now have a field named ListID. You would
group on ListID and sort on ListID in the report.

Here is an example where I do exactly this in a report. Note the first
calculated field is used to sort the two and the second field is what I use
for the Group Header Labels:

SELECT 0 As GroupType, "Active Contracts With No Start Date" As
Group_Description, ContractType, ClientName, PropertyName FROM
qselMonActRptNoStart;
UNION ALL SELECT 1 As GroupType, "InActive Contracts With No End Date" As
Group_Description, ContractType, ClientName,PropertyName FROM
qselMonActRptNoEnd;

--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Will that allow me to keep the two lists seperate? They can't be mixed
together. Also would it be possible to put a label before the second list
starts?

--
Chels


"Klatuu" wrote:

Have you tried using a Union query to join the two lists together and using
that as the record source of the report?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels

If you don't want to show the header on the first group, you can use some
simple VBA to do that.
Open the report in design view.
Right Click on the group header and you will get the properties dialog
Select the Events Tab
Select On Format by clicking on the small command button to the right of it
with the 3 dots.
Select Code Builder.

The VBA editor will open with the cursor positioned in the sub.
Enter the following (sort of, I'll show the differences next)

If Me.txtGroupType = 0 Then
Me.GroupHeader1.Visible = False
Else
Me.GroupHeader1.Visible = False
End If

Now the differences.
txtGroupType is the name of the text box where you will show the group
type or the group description.
= 0 should be the value you will expect for the first group
GroupHeader1 is usually the default name for the first group header.

That should take care of hiding the header for the first group.

As to the sorting after that. I would not bother to sort the query. You
can use the next Group in the Sorting and Grouping dialog. It will sort on
whatever field or fields you select, but not including the first one already
identifyed. So everything that starts with 0 (using my example), will be in
the first group, but then if you use say contract type(using my example), The
report will show the contract type in order for each group. For example:
0 A
0 B
0 C
Group 1
1 A
1 C
1 D

Just don't select a group header or footer for the contract group.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

OK scratch that! The grouping is working.... I didnt have to change anything
I just didnt notice it was working the first time. What confused me is it is
putting the group header in front of both groups. I only want to header in
front of the second group. How do I get the first header to go away? Do I
need to put that first list in it's own group without a header?

I still dont know what to do about the sorting though.... should i add my
sorting field to the query? If so can I tell the query not to make the
sorting field visible or do I need to do that in the report like I did with
the List ID field?
--
Chels


"Chels" wrote:

OK you lost me a bit....
So the settings for my grouping are all fine? What do you mean by "You
should be grouping by groupType"?

How do I make a second group below? Am I making these groups to tell them
which records to put in which group? I dont really understand the properties
very much, sorry. Am I telling it to group ListID 0 together and ListID 1 in
another group? Will I need to add anything to my query?

If I am not understanding you sorry I am new to the grouping thing!
--
Chels


"Klatuu" wrote:

You group settings are correct. You should be grouping by GroupType. The
field I showed in my example that separates the list. You can also use the
sorting and grouping to order the groups. Just create another group below
the first group, but don't identify any headers or footers for the group.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Thank you so much! Using the union query to identify the lists worked
wonderfully. I am running into more problems now though.

I have another field I usually put in my queries just for sorting to keep
everything in order because the Unit #s dont keep themselves in the proper
order. How can i tell the query to sort with a field but not show it? When I
view the query everything is in the right order but when I view the report
they're not. Also I am a little confused as to how to do the grouping so
there is a header between the two lists. I did find the sorting and grouping
and i told it to group based on List ID. This is what I have

ListID Group Properties: ( I have List ID sorting Ascending, I dont know if
I should but I can't figure out how to tell it not to sort)
Group Header: Yes
Group Footer: No
Group On: Each value (Is this correct?)
Group Interval: 1 (?)
Keep together: No

I have a header of shutdown in front of the whole list but the two lists
arent sorting properly or seperating like they are in the query list.
--
Chels


"Klatuu" wrote:

The two lists are separate. That is why you could use a Union query to put
the together on one report. You will have to make sure you can order the
union query so the lists are in the order you want. Also, to present a lable
between lists and to keep the list separate, you could use the report
Sorting and Grouping and group by the list identifier. The use a Group
Header and you can put a label in the group header.

Here is an example of how to use the Union Query to identify the lists:

SELECT 0 AS ListID, SomeField, AnotherField, AnyField FROM FirstList;
UNION ALL
SELECT 1 As ListID, SomeField, AnotherField, AnyField FROM SecondList;

The output of your query will now have a field named ListID. You would
group on ListID and sort on ListID in the report.

Here is an example where I do exactly this in a report. Note the first
calculated field is used to sort the two and the second field is what I use
for the Group Header Labels:

SELECT 0 As GroupType, "Active Contracts With No Start Date" As
Group_Description, ContractType, ClientName, PropertyName FROM
qselMonActRptNoStart;
UNION ALL SELECT 1 As GroupType, "InActive Contracts With No End Date" As
Group_Description, ContractType, ClientName,PropertyName FROM
qselMonActRptNoEnd;

--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Will that allow me to keep the two lists seperate? They can't be mixed
together. Also would it be possible to put a label before the second list
starts?

--
Chels


"Klatuu" wrote:

Have you tried using a Union query to join the two lists together and using
that as the record source of the report?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels

Ok I have this as the Code for the Group Header:

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
If Me.txtGroupType = 0 Then
Me.GroupHeader1.Visible = False
Else
Me.GroupHeader1.Visible = False
End If
End Sub

It is not working. I have never tried using VBA code before. I only have the
one group should I have two?

The problem with the sorting is that the field I usually use for sorting is
not in the query or report. It is just a number assigned to each entry to
keep them all in the proper order because none of the information in the
records can be used for sorting. I usually include it in the queries (It is
called Place Holder) but don't make it visible and then when I make the
reports from those queries everything is usually in the right order. When I
tried to include that field in the Union query it keeps putting it in as
blank and asking for a parameter value.

--
Chels


"Klatuu" wrote:

If you don't want to show the header on the first group, you can use some
simple VBA to do that.
Open the report in design view.
Right Click on the group header and you will get the properties dialog
Select the Events Tab
Select On Format by clicking on the small command button to the right of it
with the 3 dots.
Select Code Builder.

The VBA editor will open with the cursor positioned in the sub.
Enter the following (sort of, I'll show the differences next)

If Me.txtGroupType = 0 Then
Me.GroupHeader1.Visible = False
Else
Me.GroupHeader1.Visible = False
End If

Now the differences.
txtGroupType is the name of the text box where you will show the group
type or the group description.
= 0 should be the value you will expect for the first group
GroupHeader1 is usually the default name for the first group header.

That should take care of hiding the header for the first group.

As to the sorting after that. I would not bother to sort the query. You
can use the next Group in the Sorting and Grouping dialog. It will sort on
whatever field or fields you select, but not including the first one already
identifyed. So everything that starts with 0 (using my example), will be in
the first group, but then if you use say contract type(using my example), The
report will show the contract type in order for each group. For example:
0 A
0 B
0 C
Group 1
1 A
1 C
1 D

Just don't select a group header or footer for the contract group.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

OK scratch that! The grouping is working.... I didnt have to change anything
I just didnt notice it was working the first time. What confused me is it is
putting the group header in front of both groups. I only want to header in
front of the second group. How do I get the first header to go away? Do I
need to put that first list in it's own group without a header?

I still dont know what to do about the sorting though.... should i add my
sorting field to the query? If so can I tell the query not to make the
sorting field visible or do I need to do that in the report like I did with
the List ID field?
--
Chels


"Chels" wrote:

OK you lost me a bit....
So the settings for my grouping are all fine? What do you mean by "You
should be grouping by groupType"?

How do I make a second group below? Am I making these groups to tell them
which records to put in which group? I dont really understand the properties
very much, sorry. Am I telling it to group ListID 0 together and ListID 1 in
another group? Will I need to add anything to my query?

If I am not understanding you sorry I am new to the grouping thing!
--
Chels


"Klatuu" wrote:

You group settings are correct. You should be grouping by GroupType. The
field I showed in my example that separates the list. You can also use the
sorting and grouping to order the groups. Just create another group below
the first group, but don't identify any headers or footers for the group.
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Thank you so much! Using the union query to identify the lists worked
wonderfully. I am running into more problems now though.

I have another field I usually put in my queries just for sorting to keep
everything in order because the Unit #s dont keep themselves in the proper
order. How can i tell the query to sort with a field but not show it? When I
view the query everything is in the right order but when I view the report
they're not. Also I am a little confused as to how to do the grouping so
there is a header between the two lists. I did find the sorting and grouping
and i told it to group based on List ID. This is what I have

ListID Group Properties: ( I have List ID sorting Ascending, I dont know if
I should but I can't figure out how to tell it not to sort)
Group Header: Yes
Group Footer: No
Group On: Each value (Is this correct?)
Group Interval: 1 (?)
Keep together: No

I have a header of shutdown in front of the whole list but the two lists
arent sorting properly or seperating like they are in the query list.
--
Chels


"Klatuu" wrote:

The two lists are separate. That is why you could use a Union query to put
the together on one report. You will have to make sure you can order the
union query so the lists are in the order you want. Also, to present a lable
between lists and to keep the list separate, you could use the report
Sorting and Grouping and group by the list identifier. The use a Group
Header and you can put a label in the group header.

Here is an example of how to use the Union Query to identify the lists:

SELECT 0 AS ListID, SomeField, AnotherField, AnyField FROM FirstList;
UNION ALL
SELECT 1 As ListID, SomeField, AnotherField, AnyField FROM SecondList;

The output of your query will now have a field named ListID. You would
group on ListID and sort on ListID in the report.

Here is an example where I do exactly this in a report. Note the first
calculated field is used to sort the two and the second field is what I use
for the Group Header Labels:

SELECT 0 As GroupType, "Active Contracts With No Start Date" As
Group_Description, ContractType, ClientName, PropertyName FROM
qselMonActRptNoStart;
UNION ALL SELECT 1 As GroupType, "InActive Contracts With No End Date" As
Group_Description, ContractType, ClientName,PropertyName FROM
qselMonActRptNoEnd;

--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

Will that allow me to keep the two lists seperate? They can't be mixed
together. Also would it be possible to put a label before the second list
starts?

--
Chels


"Klatuu" wrote:

Have you tried using a Union query to join the two lists together and using
that as the record source of the report?
--
Dave Hargis, Microsoft Access MVP


"Chels" wrote:

You guys have saved me before so I hope you can do it again! I am using
Access 2000. I am trying to create a report that will display two lists of
Unit numbers with remarks for each Unit number. I want the lists to be one
after the other but I would also like them to be in columns because the data
is not very detailed but there are a lot of units.

I have had no problem with creating one report displayed in 3 columns for
one list of numbers. I thought I could add a subreport with the other list of
numbers and it would put it at the end of the first list. That didnt work.
The Subreport will only show as many rows as will fit in the area I delegated
for the subreport in design view. Also the subreport keeps putting itself in
numerical order using the first unit number in it's list. I don't know how it
is doing it but I want it to go at the end of the first list.

Then I tried making a report with a subreport for each list. The first
subreport goes in fine. Then the second subreport (the one I tried adding as
a subreport in my first attempt) again will only show the number of units it
can fit in the area it's given and it keeps putting itself in order. I even
tried putting these subreports side by side in design view to see if it would
give me two lists side by side but again it will only show the limited units
and it is STILL putting itself in numerical order with the first just beside
it.

Does anyone know why these things are happening? I have never tried using
subreports so maybe i am doing this totally wrong? I am adding the subreports
to the "details" section so this is also making me have HUGE spaces between
entries. Also it doesnt matter if the page is setup to be in columns or not I
get the same problems.

Thank you for any help!
--
Chels