There has to be a better way than the query I have set up

[BrianPaul]

I have a database of all the NCAA Teams schedules. I have two fields I am
working with based on a table called Tscheduledgames. Field 1 = Home team and
field 2 = visitors team.

here is what I did which may be the only way to do it...looking for a better
way.

I want to also provide a conference schedule also. working with the ACC
conference. keep in mind home team visitors team fields.

if I put boston college in the home team colum criteria, query results
boston college games. okay thats basic. now when I add them all to the home
team column

I get all the ACC games and who they play in the visitors field.

However If I also put all the ACC teams in the visitor field and run the
query, I get nothing. I was hopeing that I would get the conference schedule
that way. So to get around it. I saved the query based on all the acc teams
in the home team field and created another querry based on that query and
listed all the ACC teams in the visitors team, thus; I go a schedule that way.

Question: cant I just do it with 1 query instead on nesting another to
achieve my result. Thanks, just trying to become more proficient at access.

 

[Ken Sheridan]

If you are doing this by listing all the ACC teams one above the other in the
criteria rows for both columns you are actually producing a series of
criteria for the same team playing itself, all tagged together with Boolean
OR operations, which would translate to something like the following in SQL:

WHERE ([Home Team] = "Team 1" AND ([Visitor= Team 1")
OR ([Home Team] = "Team 2" AND ([Visitor= Team 2")
OR ([Home Team] = "Team 3" AND ([Visitor= Team 3")
OR ([Home Team] = "Team 4" AND ([Visitor= Team 4")
<and so on>

So you are not going to get any rows returned. For value lists like this
you can do it much more easily with the IN operator. In SQL it would look
like this:

WHERE ([Home Team] IN ("Team 1", "Team 2", "Team 3","Team 4", <and so on>)
AND ([Visitor] IN ("Team 1", "Team 2", "Team 3","Team 4", <and so on>)

In query design view just enter IN ("Team 1", "Team 2", "Team 3","Team 4",
<and so on>) in the first criteria row for Home Team and the same in the
criteria row for Visitor. This will return rows where any team in the list
plays any other team in the list.

If for any reason you ever wanted to return rows where either the home team
or the visitor were in the list of ACC teams but not necessarily both you'd
just put the Home team criteria on the first line and the Visitor criteria on
the second line. This would change the operation from a Boolean AND to a
Boolean OR.

Ken Sheridan
Stafford, England

 

[BrianPaul]

Thanks, I have heard of the IN operator but never seen an example of its use.

I like further like to know about your second statement I could get all the
ACC teams that play other teams out of there conference. What confuses me
about it is when you state the first line and second line. Do you mean first
line of home team and second line of visitors team? or both on home team.

If you are doing this by listing all the ACC teams one above the other in the
criteria rows for both columns you are actually producing a series of
criteria for the same team playing itself, all tagged together with Boolean
OR operations, which would translate to something like the following in SQL:

WHERE ([Home Team] = "Team 1" AND ([Visitor= Team 1")
OR ([Home Team] = "Team 2" AND ([Visitor= Team 2")
OR ([Home Team] = "Team 3" AND ([Visitor= Team 3")
OR ([Home Team] = "Team 4" AND ([Visitor= Team 4")
<and so on>

So you are not going to get any rows returned. For value lists like this
you can do it much more easily with the IN operator. In SQL it would look
like this:

WHERE ([Home Team] IN ("Team 1", "Team 2", "Team 3","Team 4", <and so on>)
AND ([Visitor] IN ("Team 1", "Team 2", "Team 3","Team 4", <and so on>)

In query design view just enter IN ("Team 1", "Team 2", "Team 3","Team 4",
<and so on>) in the first criteria row for Home Team and the same in the
criteria row for Visitor. This will return rows where any team in the list
plays any other team in the list.

If for any reason you ever wanted to return rows where either the home team
or the visitor were in the list of ACC teams but not necessarily both you'd
just put the Home team criteria on the first line and the Visitor criteria on
the second line. This would change the operation from a Boolean AND to a
Boolean OR.

Ken Sheridan
Stafford, England

I have a database of all the NCAA Teams schedules. I have two fields I am
working with based on a table called Tscheduledgames. Field 1 = Home team and
field 2 = visitors team.

here is what I did which may be the only way to do it...looking for a better
way.

I want to also provide a conference schedule also. working with the ACC
conference. keep in mind home team visitors team fields.

if I put boston college in the home team colum criteria, query results
boston college games. okay thats basic. now when I add them all to the home
team column

I get all the ACC games and who they play in the visitors field.

However If I also put all the ACC teams in the visitor field and run the
query, I get nothing. I was hopeing that I would get the conference schedule
that way. So to get around it. I saved the query based on all the acc teams
in the home team field and created another querry based on that query and
listed all the ACC teams in the visitors team, thus; I go a schedule that way.

Question: cant I just do it with 1 query instead on nesting another to
achieve my result. Thanks, just trying to become more proficient at access.

I want to also provide a conference schedule also. working with the ACC
conference. keep in mind home team visitors team fields.

if I put boston college in the home team colum criteria, query results
boston college games. okay thats basic. now when I add them all to the home
team column

I get all the ACC games and who they play in the visitors field.

However If I also put all the ACC teams in the visitor field and run the
query, I get nothing. I was hopeing that I would get the conference schedule
that way. So to get around it. I saved the query based on all the acc teams
in the home team field and created another querry based on that query and
listed all the ACC teams in the visitors team, thus; I go a schedule that way.

Question: cant I just do it with 1 query instead on nesting another to
achieve my result. Thanks, just trying to become more proficient at access.

 

[Ken Sheridan]

I mean line of home team and second line of visitors team. In the query
design grid if you enter two criteria under separate columns on the same line
this produces a Boolean AND, so in a query for suppliers of a product say if
you entered "London" under City and "Widget" on the same criteria line under
Product the result in SQL would be:

WHERE City = "London" AND Product = "Widget"

which would give you those suppliers in London who produce widgets.

If however you entered then on separate criteria lines, this produces a
Boolean OR:

WHERE City = "London" OR Product = "Widget"

which would give you all suppliers in London, whatever they produce, and all
suppliers who produce widgets, wherever they are located.

In your case the result of putting the criteria on separate lines in the
query design grid would give you:

WHERE ([Home Team] IN ("Team 1", "Team 2", "Team 3","Team 4", <and so on>)
OR ([Visitor] IN ("Team 1", "Team 2", "Team 3","Team 4", <and so on>)

so if either the home team or the visiting team is in the list that row
would be returned, but if neither team is in the list it wouldn't. This
gives you a result set of matches, therefore, of where either the home team
or the visiting team (or both) is in the list.

I'm signing off for tonight now; its getting late here!

Ken Sheridan
Stafford, England

 

[BrianPaul]

I didn't know you could do that. been working with access since 2.0 and
never knew you could do that. Thanks for the education. Could have done
that with alot of databases I have worked with. Thanks, Thats why I replied
to the post in that manner because I thought you couldn't.

I mean line of home team and second line of visitors team. In the query
design grid if you enter two criteria under separate columns on the same line
this produces a Boolean AND, so in a query for suppliers of a product say if
you entered "London" under City and "Widget" on the same criteria line under
Product the result in SQL would be:

WHERE City = "London" AND Product = "Widget"

which would give you those suppliers in London who produce widgets.

If however you entered then on separate criteria lines, this produces a
Boolean OR:

WHERE City = "London" OR Product = "Widget"

which would give you all suppliers in London, whatever they produce, and all
suppliers who produce widgets, wherever they are located.

In your case the result of putting the criteria on separate lines in the
query design grid would give you:

WHERE ([Home Team] IN ("Team 1", "Team 2", "Team 3","Team 4", <and so on>)
OR ([Visitor] IN ("Team 1", "Team 2", "Team 3","Team 4", <and so on>)

so if either the home team or the visiting team is in the list that row
would be returned, but if neither team is in the list it wouldn't. This
gives you a result set of matches, therefore, of where either the home team
or the visiting team (or both) is in the list.

I'm signing off for tonight now; its getting late here!

Ken Sheridan
Stafford, England

Thanks, I have heard of the IN operator but never seen an example of its use.

I like further like to know about your second statement I could get all the
ACC teams that play other teams out of there conference. What confuses me
about it is when you state the first line and second line. Do you mean first
line of home team and second line of visitors team? or both on home team.