Grouping items in Columns Version: 97

  • Thread starter Thread starter Nick Burns
  • Start date Start date
N

Nick Burns

I have a coworker who needs to create a report as follows (currently being
done in Excel 97)

Team # Player Company Team # Player Company1A Player1
Company | 1B Player1 Company1A Player2 Company | 1B
Player2 Company1A Player3 Company | 1B Player3 Company1A
Player4 Company | 1B Player4
Company---------------------------------------------------------------------
--2A Player1 Company | 2B Player1 Company2A Player2
Company | 2B Player2 Company2A Player3 Company | 2B
<BLANK>2A Player4 Company | 2B <BLANK>2A Player5
Company | 2B <BLANK>
----------------------------------------------------------------------------
----

As you can see some teams may not be full, and others may have more than 4.
I know there's a way to tell the report to create a new column once the Team
# changes, but not sure how to accomplish this.

On the blank fields, it would be fine if the Team # didn't appear, I can see
how that might be a little more difficult to achieve.

Any help would be appreciated.

Thanks
 
Ok, I'm getting close I have this set:
Column Layout - Down, Then Across
Grid Settings - Number of Columns set to 2
New Row Or Col property setting for group Team# header section - Before
Section

This gets the column to print as wanted, but after the 2nd column is
printed, it creates a new page. Team 2A and 2B are on the second page, etc.

How do I get all the teams to print on one page, and only going to the
second page when page 1 is full?
 
I still don't understand what you're looking for, but it
sounds like maybe you want to set the number of columns to 4
or 5.

Be sure you uncheck the column widths box (Page Setup -
Columns) and set the column width manually. Rhis allows the
report and page headers/footers to span the entire page.

You may also want to consider setting the group's (not the
section's) KeepTogether property to the whole group instead
of using New Row or Column.
 
The report I want should look like this:
Team # Player Company | Team # Player Company
1A Player1 CompanyName | 1B Player1 CompanyName
1A Player2 CompanyName | 1B Player2 CompanyName
1A Player3 CompanyName | 1B Player3 CompanyName
1A Player4 CompanyName | 1B Player4 CompanyName
-----------------------------------------------------------------------
2A Player1 CompanyName | 2B Player1 CompanyName
2A Player2 CompanyName | 2B Player2 CompanyName
2A Player3 CompanyName | 2B <BLANK>
2A Player4 CompanyName | 2B <BLANK>
2A Player5 CompanyName | 2B <BLANK>

-----------------------------------------------------------------------
So two columns is definitly what I need, and the second post gets close
but when it gets done with the two columns, it creates a second page, so
that
Teams 2A and 2B are on page 2, and Teams 3A and 3B are on page 3, etc.
I need them all on one page until that page is full, then it can make
another page.

Marshall Barton said:
I still don't understand what you're looking for, but it
sounds like maybe you want to set the number of columns to 4
or 5.

Be sure you uncheck the column widths box (Page Setup -
Columns) and set the column width manually. Rhis allows the
report and page headers/footers to span the entire page.

You may also want to consider setting the group's (not the
section's) KeepTogether property to the whole group instead
of using New Row or Column.
--
Marsh
MVP [MS Access]




Nick said:
Ok, I'm getting close I have this set:
Column Layout - Down, Then Across
Grid Settings - Number of Columns set to 2
New Row Or Col property setting for group Team# header section - Before
Section

This gets the column to print as wanted, but after the 2nd column is
printed, it creates a new page. Team 2A and 2B are on the second page, etc.

How do I get all the teams to print on one page, and only going to the
second page when page 1 is full?



"Nick Burns" wrote
Company-------------------------------------------------------------------- -
Click to expand...
-
Click to expand...
 
That looks like it should be Across Then Down, are you sure
you're using Down Then Across?

If you have Down Then Across, then I'll guess that you still
have the group header's New Row or Column set to Yes.

I'm grasping a straws here because, as I understand what you
have, it looks like it should be doing what you want.
 
When I use Across Then Down I get this:
Team # Player Company | Team # Player Company
1A Player1 CompanyName | 1A Player2 CompanyName
1A Player3 CompanyName | 1A Player4 CompanyName
1B Player1 CompanyName | 1B Player2 CompanyName
1B Player3 CompanyName | 1B Player4 CompanyName
2A Player1 CompanyName | 2A Player2 CompanyName
2A Player3 CompanyName | 2A Player4 CompanyName
2A Player5 CompanyName | 2B <BLANK>
2B <BLANK>

Is there a setting I'm missing? I have a feeling this will take some VBA,
but not sure what methods I need to modify.

Marshall Barton said:
That looks like it should be Across Then Down, are you sure
you're using Down Then Across?

If you have Down Then Across, then I'll guess that you still
have the group header's New Row or Column set to Yes.

I'm grasping a straws here because, as I understand what you
have, it looks like it should be doing what you want.
--
Marsh
MVP [MS Access]


Nick said:
The report I want should look like this:
Team # Player Company | Team # Player Company
1A Player1 CompanyName | 1B Player1 CompanyName
1A Player2 CompanyName | 1B Player2 CompanyName
1A Player3 CompanyName | 1B Player3 CompanyName
1A Player4 CompanyName | 1B Player4 CompanyName
-----------------------------------------------------------------------
2A Player1 CompanyName | 2B Player1 CompanyName
2A Player2 CompanyName | 2B Player2 CompanyName
2A Player3 CompanyName | 2B <BLANK>
2A Player4 CompanyName | 2B <BLANK>
2A Player5 CompanyName | 2B <BLANK>

-----------------------------------------------------------------------
So two columns is definitly what I need, and the second post gets close
but when it gets done with the two columns, it creates a second page, so
that
Teams 2A and 2B are on page 2, and Teams 3A and 3B are on page 3, etc.
I need them all on one page until that page is full, then it can make
another page.

"Marshall Barton" wrote
Click to expand...
Company------------------------------------------------------------------- -
-
Click to expand...
Click to expand...
-
 
Actually, it looks like this:
Team # Player Company | Team # Player Company
1A Player1 CompanyName | 1A Player2 CompanyName
1A Player3 CompanyName | 1A Player4 CompanyName
1B Player1 CompanyName | 1B Player2 CompanyName
1B Player3 CompanyName | 1B Player4 CompanyName
2A Player1 CompanyName | 2A Player2 CompanyName
2A Player3 CompanyName | 2A Player4 CompanyName
2A Player5 CompanyName | 2B Player1 CompanyName
2B Player2 CompanyName
Since there are no more players for team 2B, they're just left off. In my
(corrected) post, the remaining spots for 2B could be left blank (the "2B"
doesn't have to appear for empty slots)


Nick Burns said:
When I use Across Then Down I get this:
Team # Player Company | Team # Player Company
1A Player1 CompanyName | 1A Player2 CompanyName
1A Player3 CompanyName | 1A Player4 CompanyName
1B Player1 CompanyName | 1B Player2 CompanyName
1B Player3 CompanyName | 1B Player4 CompanyName
2A Player1 CompanyName | 2A Player2 CompanyName
2A Player3 CompanyName | 2A Player4 CompanyName
2A Player5 CompanyName | 2B <BLANK>
2B <BLANK>

Is there a setting I'm missing? I have a feeling this will take some VBA,
but not sure what methods I need to modify.

Marshall Barton said:
That looks like it should be Across Then Down, are you sure
you're using Down Then Across?

If you have Down Then Across, then I'll guess that you still
have the group header's New Row or Column set to Yes.

I'm grasping a straws here because, as I understand what you
have, it looks like it should be doing what you want.
--
Marsh
MVP [MS Access]


Nick said:
The report I want should look like this:
Team # Player Company | Team # Player Company
1A Player1 CompanyName | 1B Player1 CompanyName
1A Player2 CompanyName | 1B Player2 CompanyName
1A Player3 CompanyName | 1B Player3 CompanyName
1A Player4 CompanyName | 1B Player4 CompanyName
-----------------------------------------------------------------------
2A Player1 CompanyName | 2B Player1 CompanyName
2A Player2 CompanyName | 2B Player2 CompanyName
2A Player3 CompanyName | 2B <BLANK>
2A Player4 CompanyName | 2B <BLANK>
2A Player5 CompanyName | 2B <BLANK>

-----------------------------------------------------------------------
So two columns is definitly what I need, and the second post gets close
but when it gets done with the two columns, it creates a second page, so
that
Teams 2A and 2B are on page 2, and Teams 3A and 3B are on page 3, etc.
I need them all on one page until that page is full, then it can make
another page.

I still don't understand what you're looking for, but it
sounds like maybe you want to set the number of columns to 4
or 5.

Be sure you uncheck the column widths box (Page Setup -
Columns) and set the column width manually. Rhis allows the
report and page headers/footers to span the entire page.

You may also want to consider setting the group's (not the
section's) KeepTogether property to the whole group instead
of using New Row or Column.


Nick Burns wrote:
Ok, I'm getting close I have this set:
Column Layout - Down, Then Across
Grid Settings - Number of Columns set to 2
New Row Or Col property setting for group Team# header section - Before
Section

This gets the column to print as wanted, but after the 2nd column is
printed, it creates a new page. Team 2A and 2B are on the second page,
etc.

How do I get all the teams to print on one page, and only going to the
second page when page 1 is full?



"Nick Burns" wrote
I have a coworker who needs to create a report as follows (currently
being
done in Excel 97)

Team # Player Company Team # Player Company1A Player1
Company | 1B Player1 Company1A Player2 Company | 1B
Player2 Company1A Player3 Company | 1B Player3
Company1A
Player4 Company | 1B Player4
Click to expand...
Company-------------------------------------------------------------------
Click to expand...
-
-
--2A Player1 Company | 2B Player1 Company2A
Player2
Company | 2B Player2 Company2A Player3 Company | 2B
<BLANK>2A Player4 Company | 2B <BLANK>2A Player5
Company | 2B <BLANK>
Click to expand...
------------------------------------------------------------------------
Click to expand...
Click to expand...
-
Click to expand...
 
w00t! I got it done!
took 3 queries:
qryTeamNumbers:
SELECT DISTINCT Left([Team],Len([Team])-1) AS TeamNum
FROM GolfTeams;

qryTeamA:
SELECT GolfTeams.Team, GolfTeams.Player, GolfTeams.Company,
Left([Team],Len([Team])-1) AS TeamNum
FROM GolfTeams
WHERE (((Right([Team],1))="A"))
ORDER BY GolfTeams.Team;


qryTeamB:
SELECT GolfTeams.Team, GolfTeams.Player, GolfTeams.Company,
Left([Team],Len([Team])-1) AS TeamNum
FROM GolfTeams
WHERE (((Right([Team],1))="B"))
ORDER BY GolfTeams.Team;

Then a Master Report based on qryTeamNumbers grouping on TeamNum
Then 2 SubReports based on qryTeamA and qryTeamB Parent-Child link on
TeamNum

Now this is somewhat 99% of what I need, if I can get the Team # to show
where there are no Players, it would be complete.
But as it stands, this does do what I need it to do.

Nick Burns said:
Actually, it looks like this:
Team # Player Company | Team # Player Company
1A Player1 CompanyName | 1A Player2 CompanyName
1A Player3 CompanyName | 1A Player4 CompanyName
1B Player1 CompanyName | 1B Player2 CompanyName
1B Player3 CompanyName | 1B Player4 CompanyName
2A Player1 CompanyName | 2A Player2 CompanyName
2A Player3 CompanyName | 2A Player4 CompanyName
2A Player5 CompanyName | 2B Player1 CompanyName
2B Player2 CompanyName
Since there are no more players for team 2B, they're just left off. In my
(corrected) post, the remaining spots for 2B could be left blank (the "2B"
doesn't have to appear for empty slots)


Nick Burns said:
When I use Across Then Down I get this:
Team # Player Company | Team # Player Company
1A Player1 CompanyName | 1A Player2 CompanyName
1A Player3 CompanyName | 1A Player4 CompanyName
1B Player1 CompanyName | 1B Player2 CompanyName
1B Player3 CompanyName | 1B Player4 CompanyName
2A Player1 CompanyName | 2A Player2 CompanyName
2A Player3 CompanyName | 2A Player4 CompanyName
2A Player5 CompanyName | 2B <BLANK>
2B <BLANK>

Is there a setting I'm missing? I have a feeling this will take some VBA,
but not sure what methods I need to modify.

Marshall Barton said:
That looks like it should be Across Then Down, are you sure
you're using Down Then Across?

If you have Down Then Across, then I'll guess that you still
have the group header's New Row or Column set to Yes.

I'm grasping a straws here because, as I understand what you
have, it looks like it should be doing what you want.
--
Marsh
MVP [MS Access]


Nick Burns wrote:

The report I want should look like this:
Team # Player Company | Team # Player Company
1A Player1 CompanyName | 1B Player1 CompanyName
1A Player2 CompanyName | 1B Player2 CompanyName
1A Player3 CompanyName | 1B Player3 CompanyName
1A Player4 CompanyName | 1B Player4 CompanyName
-----------------------------------------------------------------------
2A Player1 CompanyName | 2B Player1 CompanyName
2A Player2 CompanyName | 2B Player2 CompanyName
2A Player3 CompanyName | 2B <BLANK>
2A Player4 CompanyName | 2B <BLANK>
2A Player5 CompanyName | 2B <BLANK>

-----------------------------------------------------------------------
So two columns is definitly what I need, and the second post gets close
but when it gets done with the two columns, it creates a second page, so
that
Teams 2A and 2B are on page 2, and Teams 3A and 3B are on page 3, etc.
I need them all on one page until that page is full, then it can make
another page.

I still don't understand what you're looking for, but it
sounds like maybe you want to set the number of columns to 4
or 5.

Be sure you uncheck the column widths box (Page Setup -
Columns) and set the column width manually. Rhis allows the
report and page headers/footers to span the entire page.

You may also want to consider setting the group's (not the
section's) KeepTogether property to the whole group instead
of using New Row or Column.


Nick Burns wrote:
Ok, I'm getting close I have this set:
Column Layout - Down, Then Across
Grid Settings - Number of Columns set to 2
New Row Or Col property setting for group Team# header section - Before
Section

This gets the column to print as wanted, but after the 2nd column is
printed, it creates a new page. Team 2A and 2B are on the second page,
etc.

How do I get all the teams to print on one page, and only going to the
second page when page 1 is full?



"Nick Burns" wrote
I have a coworker who needs to create a report as follows (currently
being
done in Excel 97)

Team # Player Company Team # Player Company1A Player1
Company | 1B Player1 Company1A Player2 Company | 1B
Player2 Company1A Player3 Company | 1B Player3
Company1A
Player4 Company | 1B Player4
Click to expand...
Company-------------------------------------------------------------------
-
-
--2A Player1 Company | 2B Player1 Company2A
Player2
Company | 2B Player2 Company2A Player3 Company | 2B
<BLANK>2A Player4 Company | 2B <BLANK>2A Player5
Company | 2B <BLANK>
Click to expand...
------------------------------------------------------------------------
Click to expand...
-
Click to expand...
Click to expand...
 
I guess that the A and B teams are actually part of a single
team?? In that case, using subreports with the queries
you've created is a good apprach (but I don't think you need
multiple columns doing it this way).

Without a properly normalized table struture, I think it
would get very messy trying to generate the team number for
missing team members. Since you are using a flat table for
all your data, the most straightforward way to do it is to
make up dummy players with a blank player name.
 
Yes, granted, I was using a flat table for testing purposes (I'm at home and
didn't have access to the actual db). The coworker is an Admin Asst. and
most likely doesn't have the table normalized. My suggestion would be to set
the tables as such: (-< = 1toM, >-=Mto1, ><=MtoM)
tblCompany-<tblClients
tblTeamNumbers (1,2,3,etc)
tblSubTeam(A,B)
tblTeams[fldTeamNumber,fldSubTeam]
tblTeamNumbers-<tblTeams>-tblSubTeam
tblClients[fldTeamNumber,fldSubTeam]>-tblTeams

Thoughts?

Marshall Barton said:
I guess that the A and B teams are actually part of a single
team?? In that case, using subreports with the queries
you've created is a good apprach (but I don't think you need
multiple columns doing it this way).

Without a properly normalized table struture, I think it
would get very messy trying to generate the team number for
missing team members. Since you are using a flat table for
all your data, the most straightforward way to do it is to
make up dummy players with a blank player name.
--
Marsh
MVP [MS Access]




Nick said:
w00t! I got it done!
took 3 queries:
qryTeamNumbers:
SELECT DISTINCT Left([Team],Len([Team])-1) AS TeamNum
FROM GolfTeams;

qryTeamA:
SELECT GolfTeams.Team, GolfTeams.Player, GolfTeams.Company,
Left([Team],Len([Team])-1) AS TeamNum
FROM GolfTeams
WHERE (((Right([Team],1))="A"))
ORDER BY GolfTeams.Team;


qryTeamB:
SELECT GolfTeams.Team, GolfTeams.Player, GolfTeams.Company,
Left([Team],Len([Team])-1) AS TeamNum
FROM GolfTeams
WHERE (((Right([Team],1))="B"))
ORDER BY GolfTeams.Team;

Then a Master Report based on qryTeamNumbers grouping on TeamNum
Then 2 SubReports based on qryTeamA and qryTeamB Parent-Child link on
TeamNum

Now this is somewhat 99% of what I need, if I can get the Team # to show
where there are no Players, it would be complete.
But as it stands, this does do what I need it to do.
Click to expand...
Click to expand...
 
Not sure I followed that but I think the core tables would
be:
Players: PlayerID, playername, etc
Teams: TeamID, teamname, wrc
TeamPLayers: TeamID, PlayerID, PlayerPosition (1,2,3,...)

And add a table of consequtive numbers:
Numbers: SeqNum (1,2,3, ...)

Then you can easily fill in the missing players with an
outer join from Numbers to TeamPlayers:

SELECT SeqNum AS PlayerPos, PlayerID, TeamID
FROM Numbers LEFT JOIN TemaPlayers
ON SeqNum = PlayerPosition
WHERE SeqNum <= 5

and go from there with your report.
--
Marsh
MVP [MS Access]



Nick said:
Yes, granted, I was using a flat table for testing purposes (I'm at home and
didn't have access to the actual db). The coworker is an Admin Asst. and
most likely doesn't have the table normalized. My suggestion would be to set
the tables as such: (-< = 1toM, >-=Mto1, ><=MtoM)
tblCompany-<tblClients
tblTeamNumbers (1,2,3,etc)
tblSubTeam(A,B)
tblTeams[fldTeamNumber,fldSubTeam]
tblTeamNumbers-<tblTeams>-tblSubTeam
tblClients[fldTeamNumber,fldSubTeam]>-tblTeams


I guess that the A and B teams are actually part of a single
team?? In that case, using subreports with the queries
you've created is a good apprach (but I don't think you need
multiple columns doing it this way).

Without a properly normalized table struture, I think it
would get very messy trying to generate the team number for
missing team members. Since you are using a flat table for
all your data, the most straightforward way to do it is to
make up dummy players with a blank player name.


Nick said:
w00t! I got it done!
took 3 queries:
qryTeamNumbers:
SELECT DISTINCT Left([Team],Len([Team])-1) AS TeamNum
FROM GolfTeams;

qryTeamA:
SELECT GolfTeams.Team, GolfTeams.Player, GolfTeams.Company,
Left([Team],Len([Team])-1) AS TeamNum
FROM GolfTeams
WHERE (((Right([Team],1))="A"))
ORDER BY GolfTeams.Team;


qryTeamB:
SELECT GolfTeams.Team, GolfTeams.Player, GolfTeams.Company,
Left([Team],Len([Team])-1) AS TeamNum
FROM GolfTeams
WHERE (((Right([Team],1))="B"))
ORDER BY GolfTeams.Team;

Then a Master Report based on qryTeamNumbers grouping on TeamNum
Then 2 SubReports based on qryTeamA and qryTeamB Parent-Child link on
TeamNum

Now this is somewhat 99% of what I need, if I can get the Team # to show
where there are no Players, it would be complete.
But as it stands, this does do what I need it to do.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top