Grouping items in Columns Version: 97

[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

 

[Nick Burns]

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?

 

[Marshall Barton]

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]

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.
--
Marsh
MVP [MS Access]

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-------------------------------------------------------------------- -

-

 

[Marshall Barton]

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.

 

[Nick Burns]

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.

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]

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

Company------------------------------------------------------------------- -
-

-

 

[Nick Burns]

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)

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.

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]

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

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>

------------------------------------------------------------------------

-

 

[Nick Burns]

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.

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)

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.

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

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>

------------------------------------------------------------------------

-

 

[Marshall Barton]

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 Burns]

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?

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]

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.

 

[Marshall Barton]

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]

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.

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.