don't want info to show

[Anne M]

I have a report based on query..which is a team list with names and their
role on team ie coach, assistant and player.

Report is almost what I want and my knowledge is limited so I need some
advice.

I want the team list to show the names of the individuals and their role on
team if they are coach or assistant but not if they are player but since
they are based on the same field (role) how do I set it so I can get it to
show only what I want

thanks.

Anne M

[jimfortune]

Anne M wrote:

I have a report based on query..which is a team list with names and their role on team ie coach, assistant and player.

Report is almost what I want and my knowledge is limited so I need some advice.

I want the team list to show the names of the individuals and their role on team if they are coach or assistant but not if they are player but since they are based on the same field (role) how do I set it so I can get it to show only what I want

thanks.

Anne M

In the Query Design grid under role put <> "player" in the criteria
box. The resulting query SQL view should look similar to:

SELECT RosterName, role FROM tblRoster WHERE role <> "player";

Don't use Name as the name of a field since it can cause Access to
become confused.

James A. Fortune

[Anne M]

thanks but I guess I should explain a little more...I don't want to exclude
the player names just their role..

Ravens
John Smith coach
Bill Smith assistant
Jill Smith
Ann Brown
etc for all the players

<ji********@compumarc.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Anne M wrote:

I have a report based on query..which is a team list with names and

their

role on team ie coach, assistant and player.

Report is almost what I want and my knowledge is limited so I need

some

advice.

I want the team list to show the names of the individuals and their

role on

team if they are coach or assistant but not if they are player but

since

they are based on the same field (role) how do I set it so I can get

it to

show only what I want

thanks.

Anne M

In the Query Design grid under role put <> "player" in the criteria
box. The resulting query SQL view should look similar to:

SELECT RosterName, role FROM tblRoster WHERE role <> "player";

Don't use Name as the name of a field since it can cause Access to
become confused.

James A. Fortune

[jimfortune]

Anne M wrote:

thanks but I guess I should explain a little more...I don't want to exclude the player names just their role..

Ravens
John Smith coach
Bill Smith assistant
Jill Smith
Ann Brown
etc for all the players

<ji********@compumarc.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Anne M wrote:

I have a report based on query..which is a team list with names

and their

role on team ie coach, assistant and player.

Report is almost what I want and my knowledge is limited so I need

some

advice.

I want the team list to show the names of the individuals and

their role on

team if they are coach or assistant but not if they are player but

since

they are based on the same field (role) how do I set it so I can

get it to

show only what I want

thanks.

Anne M

In the Query Design grid under role put <> "player" in the criteria
box. The resulting query SQL view should look similar to:

SELECT RosterName, role FROM tblRoster WHERE role <> "player";

Don't use Name as the name of a field since it can cause Access to
become confused.

James A. Fortune

Assuming tblRoster has a 'Team' field, try

SELECT tblRoster.RosterName, IIf([Role]='Player',Null,[Role]) AS
RosterRole FROM tblRoster WHERE tblRoster.[Team] = 'Ravens';

For your report you can do sorting and grouping by Team (show Group
Header) with:

SELECT tblRoster.RosterName, IIf([Role]='Player',Null,[Role]) AS
RosterRole, Team FROM tblRoster

as the RecordSource for the report. Drag Team from the Field List into
the Group Header.

James A. Fortune

[Phil Stanton]

Two methods

IIf(Role = 'Player',"",Role)
Note that it is IIf not If. This should show a blank space if the condition
is true, otherwise it should show the role.

The other method is to have an indicator in the table for True or False,
depending whether you want to show the role (You may have other roles that
you don't want to display and that will give additional functionality). The
IIf statement then becomes something like
IIf(PrintInd = True, Role, "")

HTH

Phil
"Anne M" <am******@ns.sympatico.ca> wrote in message
news:dA********************@ursa-nb00s0.nbnet.nb.ca...

thanks but I guess I should explain a little more...I don't want to
exclude the player names just their role..

Ravens
John Smith coach
Bill Smith assistant
Jill Smith
Ann Brown
etc for all the players

<ji********@compumarc.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Anne M wrote:

I have a report based on query..which is a team list with names and

their

role on team ie coach, assistant and player.

Report is almost what I want and my knowledge is limited so I need

some

advice.

I want the team list to show the names of the individuals and their

role on

team if they are coach or assistant but not if they are player but

since

they are based on the same field (role) how do I set it so I can get

it to

show only what I want

thanks.

Anne M

In the Query Design grid under role put <> "player" in the criteria
box. The resulting query SQL view should look similar to:

SELECT RosterName, role FROM tblRoster WHERE role <> "player";

Don't use Name as the name of a field since it can cause Access to
become confused.

James A. Fortune

[Terry Kreft]

.... and Phil's number 2 is better because it's easily extensible.

--
Terry Kreft
MVP Microsoft Access
"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in message
news:42***********************@ptn-nntp-reader02.plus.net...

Two methods

IIf(Role = 'Player',"",Role)
Note that it is IIf not If. This should show a blank space if the condition is true, otherwise it should show the role.

The other method is to have an indicator in the table for True or False,
depending whether you want to show the role (You may have other roles that
you don't want to display and that will give additional functionality). The IIf statement then becomes something like
IIf(PrintInd = True, Role, "")

HTH

Phil
"Anne M" <am******@ns.sympatico.ca> wrote in message
news:dA********************@ursa-nb00s0.nbnet.nb.ca...

thanks but I guess I should explain a little more...I don't want to
exclude the player names just their role..

Ravens
John Smith coach
Bill Smith assistant
Jill Smith
Ann Brown
etc for all the players

<ji********@compumarc.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Anne M wrote:
I have a report based on query..which is a team list with names and
their
role on team ie coach, assistant and player.

Report is almost what I want and my knowledge is limited so I need
some
advice.

I want the team list to show the names of the individuals and their
role on
team if they are coach or assistant but not if they are player but
since
they are based on the same field (role) how do I set it so I can get
it to
show only what I want

thanks.

Anne M

In the Query Design grid under role put <> "player" in the criteria
box. The resulting query SQL view should look similar to:

SELECT RosterName, role FROM tblRoster WHERE role <> "player";

Don't use Name as the name of a field since it can cause Access to
become confused.

James A. Fortune

[Anne M]

More help needed on Phils method 2
I created a print indicator field (true or false) in my participation table
where I link my member, role and team tables. I created update query to make
coach and assistant show as true. Worked fine. I added the printind field to
my query. Everything works the way I want it but now where do I put
IIf(PrintInd = True, Role, "") to get rid of the player role. I tried to use
it in my query and or report but can't seem to do it right...not sure of
where to use the expression ...

thanks again...

"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in message
news:42***********************@ptn-nntp-reader02.plus.net...

Two methods

IIf(Role = 'Player',"",Role)
Note that it is IIf not If. This should show a blank space if the
condition is true, otherwise it should show the role.

The other method is to have an indicator in the table for True or False,
depending whether you want to show the role (You may have other roles that
you don't want to display and that will give additional functionality).
The IIf statement then becomes something like
IIf(PrintInd = True, Role, "")

HTH

Phil
"Anne M" <am******@ns.sympatico.ca> wrote in message
news:dA********************@ursa-nb00s0.nbnet.nb.ca...

thanks but I guess I should explain a little more...I don't want to
exclude the player names just their role..

Ravens
John Smith coach
Bill Smith assistant
Jill Smith
Ann Brown
etc for all the players

<ji********@compumarc.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Anne M wrote:
I have a report based on query..which is a team list with names and
their
role on team ie coach, assistant and player.

Report is almost what I want and my knowledge is limited so I need
some
advice.

I want the team list to show the names of the individuals and their
role on
team if they are coach or assistant but not if they are player but
since
they are based on the same field (role) how do I set it so I can get
it to
show only what I want

thanks.

Anne M

In the Query Design grid under role put <> "player" in the criteria
box. The resulting query SQL view should look similar to:

SELECT RosterName, role FROM tblRoster WHERE role <> "player";

Don't use Name as the name of a field since it can cause Access to
become confused.

James A. Fortune

[Phil Stanton]

Hi Anne

In the query that is the source of your report, type the expression
IIf(PrintInd = True, Role, "") in a blank column on the top line. It should
change to Expr1:IIf(PrintInd = True, Role, ""). Rename "Expr1" to something
more meaningful like "PrintRole". Run the query to make sure it works. Then
in your report you want to use this new PrintRole field rather than the Role
Field

Phil
"Anne M" <am******@ns.sympatico.ca> wrote in message
news:mi********************@ursa-nb00s0.nbnet.nb.ca...

More help needed on Phils method 2
I created a print indicator field (true or false) in my participation
table where I link my member, role and team tables. I created update query
to make coach and assistant show as true. Worked fine. I added the
printind field to my query. Everything works the way I want it but now
where do I put IIf(PrintInd = True, Role, "") to get rid of the player
role. I tried to use it in my query and or report but can't seem to do it
right...not sure of where to use the expression ...

thanks again...

"Phil Stanton" <ph******@xxstantonfamily.co.uk> wrote in message
news:42***********************@ptn-nntp-reader02.plus.net...

Two methods

IIf(Role = 'Player',"",Role)
Note that it is IIf not If. This should show a blank space if the
condition is true, otherwise it should show the role.

The other method is to have an indicator in the table for True or False,
depending whether you want to show the role (You may have other roles
that you don't want to display and that will give additional
functionality). The IIf statement then becomes something like
IIf(PrintInd = True, Role, "")

HTH

Phil
"Anne M" <am******@ns.sympatico.ca> wrote in message
news:dA********************@ursa-nb00s0.nbnet.nb.ca...

thanks but I guess I should explain a little more...I don't want to
exclude the player names just their role..

Ravens
John Smith coach
Bill Smith assistant
Jill Smith
Ann Brown
etc for all the players

<ji********@compumarc.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
Anne M wrote:
> I have a report based on query..which is a team list with names and
their
> role on team ie coach, assistant and player.
>
> Report is almost what I want and my knowledge is limited so I need
some
> advice.
>
> I want the team list to show the names of the individuals and their
role on
> team if they are coach or assistant but not if they are player but
since
> they are based on the same field (role) how do I set it so I can get
it to
> show only what I want
>
> thanks.
>
> Anne M

In the Query Design grid under role put <> "player" in the criteria
box. The resulting query SQL view should look similar to:

SELECT RosterName, role FROM tblRoster WHERE role <> "player";

Don't use Name as the name of a field since it can cause Access to
become confused.

James A. Fortune