By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,763 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,763 IT Pros & Developers. It's quick & easy.

don't want info to show

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

P: n/a
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


Nov 13 '05 #5

P: n/a
.... 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



Nov 13 '05 #6

P: n/a
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



Nov 13 '05 #7

P: n/a
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



Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.