don't want info to show

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

Anne M wrote:[color=blue]
> I have a report based on query..which is a team list with names and[/color]
their[color=blue]
> role on team ie coach, assistant and player.
>
> Report is almost what I want and my knowledge is limited so I need[/color]
some[color=blue]
> advice.
>
> I want the team list to show the names of the individuals and their[/color]
role on[color=blue]
> team if they are coach or assistant but not if they are player but[/color]
since[color=blue]
> they are based on the same field (role) how do I set it so I can get[/color]
it to[color=blue]
> show only what I want
>
> thanks.
>
> Anne M[/color]

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

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

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

Anne M wrote:[color=blue]
> thanks but I guess I should explain a little more...I don't want to[/color]
exclude[color=blue]
> the player names just their role..
>
> Ravens
> John Smith coach
> Bill Smith assistant
> Jill Smith
> Ann Brown
> etc for all the players
>
> <jimfortune@compumarc.com> wrote in message
> news:1109880313.336005.249720@l41g2000cwc.googlegr oups.com...[color=green]
> > Anne M wrote:[color=darkred]
> >> I have a report based on query..which is a team list with names[/color][/color][/color]
and[color=blue][color=green]
> > their[color=darkred]
> >> role on team ie coach, assistant and player.
> >>
> >> Report is almost what I want and my knowledge is limited so I need[/color]
> > some[color=darkred]
> >> advice.
> >>
> >> I want the team list to show the names of the individuals and[/color][/color][/color]
their[color=blue][color=green]
> > role on[color=darkred]
> >> team if they are coach or assistant but not if they are player but[/color]
> > since[color=darkred]
> >> they are based on the same field (role) how do I set it so I can[/color][/color][/color]
get[color=blue][color=green]
> > it to[color=darkred]
> >> show only what I want
> >>
> >> thanks.
> >>
> >> Anne M[/color]
> >
> > 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
> >[/color][/color]

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

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" <amschaus@ns.sympatico.ca> wrote in message
news:dAKVd.13123$oh4.473209@ursa-nb00s0.nbnet.nb.ca...[color=blue]
> 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
>
> <jimfortune@compumarc.com> wrote in message
> news:1109880313.336005.249720@l41g2000cwc.googlegr oups.com...[color=green]
>> Anne M wrote:[color=darkred]
>>> I have a report based on query..which is a team list with names and[/color]
>> their[color=darkred]
>>> role on team ie coach, assistant and player.
>>>
>>> Report is almost what I want and my knowledge is limited so I need[/color]
>> some[color=darkred]
>>> advice.
>>>
>>> I want the team list to show the names of the individuals and their[/color]
>> role on[color=darkred]
>>> team if they are coach or assistant but not if they are player but[/color]
>> since[color=darkred]
>>> they are based on the same field (role) how do I set it so I can get[/color]
>> it to[color=darkred]
>>> show only what I want
>>>
>>> thanks.
>>>
>>> Anne M[/color]
>>
>> 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
>>[/color]
>
>[/color]

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



--
Terry Kreft
MVP Microsoft Access


"Phil Stanton" <philnoxx@xxstantonfamily.co.uk> wrote in message
news:42278e49$0$16784$ed2619ec@ptn-nntp-reader02.plus.net...[color=blue]
> Two methods
>
> IIf(Role = 'Player',"",Role)
> Note that it is IIf not If. This should show a blank space if the[/color]
condition[color=blue]
> 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).[/color]
The[color=blue]
> IIf statement then becomes something like
> IIf(PrintInd = True, Role, "")
>
> HTH
>
> Phil
>
>
> "Anne M" <amschaus@ns.sympatico.ca> wrote in message
> news:dAKVd.13123$oh4.473209@ursa-nb00s0.nbnet.nb.ca...[color=green]
> > 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
> >
> > <jimfortune@compumarc.com> wrote in message
> > news:1109880313.336005.249720@l41g2000cwc.googlegr oups.com...[color=darkred]
> >> 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
> >>[/color]
> >
> >[/color]
>
>[/color]

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" <philnoxx@xxstantonfamily.co.uk> wrote in message
news:42278e49$0$16784$ed2619ec@ptn-nntp-reader02.plus.net...[color=blue]
> 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" <amschaus@ns.sympatico.ca> wrote in message
> news:dAKVd.13123$oh4.473209@ursa-nb00s0.nbnet.nb.ca...[color=green]
>> 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
>>
>> <jimfortune@compumarc.com> wrote in message
>> news:1109880313.336005.249720@l41g2000cwc.googlegr oups.com...[color=darkred]
>>> 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
>>>[/color]
>>
>>[/color]
>
>[/color]

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" <amschaus@ns.sympatico.ca> wrote in message
news:mi4Wd.13893$oh4.493836@ursa-nb00s0.nbnet.nb.ca...[color=blue]
> 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" <philnoxx@xxstantonfamily.co.uk> wrote in message
> news:42278e49$0$16784$ed2619ec@ptn-nntp-reader02.plus.net...[color=green]
>> 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" <amschaus@ns.sympatico.ca> wrote in message
>> news:dAKVd.13123$oh4.473209@ursa-nb00s0.nbnet.nb.ca...[color=darkred]
>>> 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
>>>
>>> <jimfortune@compumarc.com> wrote in message
>>> news:1109880313.336005.249720@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
>>>>
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]