Sub-query, Union, Nested or what?

[Lee Hunter]

Tbl is as follows;
name, dodate, team.

I must first select the single name that matches a specific dodate, then
select all the names that match the team of the first selected name,
regardles of what their date is.

Short of running a make table for the first select, then using that as a
join with the original tbl, what other options are available?

Thanks for the help.

 

[Baz]

Maybe this (warning: untested):

SELECT Tbl1.name FROM Tbl AS Tbl1 INNER JOIN Tbl AS Tbl2 ON Tbl1.team =
Tbl2.team WHERE Tbl2.dodate = [Please Enter the Do Date]

 

[Jeff Boyce]

Lee

Please note that "name" is a reserved word in Access. You may think that
you know what you mean when you use "name", but Access thinks that it knows
what it means... Consider changing the fieldname to something more
descriptive (and that is not a reserved word).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

Using a subquery in the where clause that asks for the date and name.

SELECT *
FROM YourTable
WHERE YourTable.Team in
(SELECT Tmp.Team
FROM YourTable as Tmp
WHERE DoDate = [What Date?]
AND Tmp.Name = [What Name?]

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Lee Hunter]

What a great innovative solution!

Perfect result.

Would you please explain how (why) it works?

Maybe this (warning: untested):

SELECT Tbl1.name FROM Tbl AS Tbl1 INNER JOIN Tbl AS Tbl2 ON Tbl1.team =
Tbl2.team WHERE Tbl2.dodate = [Please Enter the Do Date]

Tbl is as follows;
name, dodate, team.

I must first select the single name that matches a specific dodate, then
select all the names that match the team of the first selected name,
regardles of what their date is.

Short of running a make table for the first select, then using that as a
join with the original tbl, what other options are available?

Thanks for the help.

 

[Baz]

I'll try!

The key to it is to pretend that you have two separate tables: the one from
which you are selecting the first name/team (by dodate), and the one from
which you are selecting all of the team members.

This is achieved by the use of aliases: by naming (aliasing) the same table
once as Tbl1 and again as Tbl2, we can treat Tbl1 and Tbl2 as two different
tables. We can then select the first name/team from Tbl2 (using the WHERE
clause), and join it to Tbl1 to get all of the team members.

Solutions using a sub-query will also work, but they are cumbersome by
comparison and tend to be slow in Access.

Hope that helps!

Baz

What a great innovative solution!

Perfect result.

Would you please explain how (why) it works?

Maybe this (warning: untested):

SELECT Tbl1.name FROM Tbl AS Tbl1 INNER JOIN Tbl AS Tbl2 ON Tbl1.team =
Tbl2.team WHERE Tbl2.dodate = [Please Enter the Do Date]

Tbl is as follows;
name, dodate, team.

I must first select the single name that matches a specific dodate,
then
select all the names that match the team of the first selected name,
regardles of what their date is.

Short of running a make table for the first select, then using that as
a
join with the original tbl, what other options are available?

Thanks for the help.

 

[Lee Hunter]

I get almost all EXCEPT....

SELECT Tbl1.name FROM Tbl AS Tbl1

Why can it be Tbl1.name or not have to be Tbl.name?

I thought the AS clause defined a an "output" alias.

Not true? If so,how canyou SELECT from the "output"?

I know it's a "fine point" but I really am trying to understand.

thanks again.

This is a piece of art

I'll try!

The key to it is to pretend that you have two separate tables: the one from
which you are selecting the first name/team (by dodate), and the one from
which you are selecting all of the team members.

This is achieved by the use of aliases: by naming (aliasing) the same table
once as Tbl1 and again as Tbl2, we can treat Tbl1 and Tbl2 as two different
tables. We can then select the first name/team from Tbl2 (using the WHERE
clause), and join it to Tbl1 to get all of the team members.

Solutions using a sub-query will also work, but they are cumbersome by
comparison and tend to be slow in Access.

Hope that helps!

Baz

What a great innovative solution!

Perfect result.

Would you please explain how (why) it works?

Maybe this (warning: untested):

SELECT Tbl1.name FROM Tbl AS Tbl1 INNER JOIN Tbl AS Tbl2 ON Tbl1.team =
Tbl2.team WHERE Tbl2.dodate = [Please Enter the Do Date]

Tbl is as follows;
name, dodate, team.

I must first select the single name that matches a specific dodate,
then
select all the names that match the team of the first selected name,
regardles of what their date is.

Short of running a make table for the first select, then using that as
a
join with the original tbl, what other options are available?

Thanks for the help.

 

[Baz]

Hi Lee,

Not sure where you got the notion of an "output" alias, I don't think that's
a helpful way of looking at it. If you got it from Access help or a book or
something, then that is just plain sloppy (of whoever wrote it, I mean, not
you!).

Once you've aliased a table, you *must* refer to it using the alias
everywhere else in the query. If you selected Tbl.name you would get an
error, and quite right too: Access would not know which "name" field you
were referring to, the one in Tbl1 or the one in Tbl2. It matters too,
because we're only selecting one name from Tbl2, but we are potentially
selecting lots of names from Tbl1.

n.b. As someone else pointed out, "name" is not a good name for a field, I
do recommend that you change it to something that isn't a reserved word.

HTH

Baz

I get almost all EXCEPT....

SELECT Tbl1.name FROM Tbl AS Tbl1

Why can it be Tbl1.name or not have to be Tbl.name?

I thought the AS clause defined a an "output" alias.

Not true? If so,how canyou SELECT from the "output"?

I know it's a "fine point" but I really am trying to understand.

thanks again.

This is a piece of art

I'll try!

The key to it is to pretend that you have two separate tables: the one
from
which you are selecting the first name/team (by dodate), and the one from
which you are selecting all of the team members.

This is achieved by the use of aliases: by naming (aliasing) the same
table
once as Tbl1 and again as Tbl2, we can treat Tbl1 and Tbl2 as two
different
tables. We can then select the first name/team from Tbl2 (using the
WHERE
clause), and join it to Tbl1 to get all of the team members.

Solutions using a sub-query will also work, but they are cumbersome by
comparison and tend to be slow in Access.

Hope that helps!

Baz

What a great innovative solution!

Perfect result.

Would you please explain how (why) it works?

:

Maybe this (warning: untested):

SELECT Tbl1.name FROM Tbl AS Tbl1 INNER JOIN Tbl AS Tbl2 ON Tbl1.team
=
Tbl2.team WHERE Tbl2.dodate = [Please Enter the Do Date]

Tbl is as follows;
name, dodate, team.

I must first select the single name that matches a specific dodate,
then
select all the names that match the team of the first selected name,
regardles of what their date is.

Short of running a make table for the first select, then using that
as
a
join with the original tbl, what other options are available?

Thanks for the help.

 

[Lee Hunter]

I really don't know where I picked up that idea, but you have me straightened
out now.

Thanks again for your help and your patience.

You're "my hero"

BTW, the real fields of LastName and FirstName were changed in my example to
make the problem simpler?

Guess that faled too.

Anyway. I've made enough mistakes with "reserved names" to not make them
anymore

Hi Lee,

Not sure where you got the notion of an "output" alias, I don't think that's
a helpful way of looking at it. If you got it from Access help or a book or
something, then that is just plain sloppy (of whoever wrote it, I mean, not
you!).

Once you've aliased a table, you *must* refer to it using the alias
everywhere else in the query. If you selected Tbl.name you would get an
error, and quite right too: Access would not know which "name" field you
were referring to, the one in Tbl1 or the one in Tbl2. It matters too,
because we're only selecting one name from Tbl2, but we are potentially
selecting lots of names from Tbl1.

n.b. As someone else pointed out, "name" is not a good name for a field, I
do recommend that you change it to something that isn't a reserved word.

HTH

Baz

I get almost all EXCEPT....

SELECT Tbl1.name FROM Tbl AS Tbl1

Why can it be Tbl1.name or not have to be Tbl.name?

I thought the AS clause defined a an "output" alias.

Not true? If so,how canyou SELECT from the "output"?

I know it's a "fine point" but I really am trying to understand.

thanks again.

This is a piece of art

I'll try!

The key to it is to pretend that you have two separate tables: the one
from
which you are selecting the first name/team (by dodate), and the one from
which you are selecting all of the team members.

This is achieved by the use of aliases: by naming (aliasing) the same
table
once as Tbl1 and again as Tbl2, we can treat Tbl1 and Tbl2 as two
different
tables. We can then select the first name/team from Tbl2 (using the
WHERE
clause), and join it to Tbl1 to get all of the team members.

Solutions using a sub-query will also work, but they are cumbersome by
comparison and tend to be slow in Access.

Hope that helps!

Baz

What a great innovative solution!

Perfect result.

Would you please explain how (why) it works?

:

Maybe this (warning: untested):

SELECT Tbl1.name FROM Tbl AS Tbl1 INNER JOIN Tbl AS Tbl2 ON Tbl1.team
=
Tbl2.team WHERE Tbl2.dodate = [Please Enter the Do Date]

Tbl is as follows;
name, dodate, team.

I must first select the single name that matches a specific dodate,
then
select all the names that match the team of the first selected name,
regardles of what their date is.

Short of running a make table for the first select, then using that
as
a
join with the original tbl, what other options are available?

Thanks for the help.

 

[Baz]

Fair enough! Happy New Year,

Baz

I really don't know where I picked up that idea, but you have me
straightened
out now.

Thanks again for your help and your patience.

You're "my hero"

BTW, the real fields of LastName and FirstName were changed in my example
to
make the problem simpler?

Guess that faled too.

Anyway. I've made enough mistakes with "reserved names" to not make them
anymore

Hi Lee,

Not sure where you got the notion of an "output" alias, I don't think
that's
a helpful way of looking at it. If you got it from Access help or a book
or
something, then that is just plain sloppy (of whoever wrote it, I mean,
not
you!).

Once you've aliased a table, you *must* refer to it using the alias
everywhere else in the query. If you selected Tbl.name you would get an
error, and quite right too: Access would not know which "name" field you
were referring to, the one in Tbl1 or the one in Tbl2. It matters too,
because we're only selecting one name from Tbl2, but we are potentially
selecting lots of names from Tbl1.

n.b. As someone else pointed out, "name" is not a good name for a field,
I
do recommend that you change it to something that isn't a reserved word.

HTH

Baz

I get almost all EXCEPT....

SELECT Tbl1.name FROM Tbl AS Tbl1

Why can it be Tbl1.name or not have to be Tbl.name?

I thought the AS clause defined a an "output" alias.

Not true? If so,how canyou SELECT from the "output"?

I know it's a "fine point" but I really am trying to understand.

thanks again.

This is a piece of art

:

I'll try!

The key to it is to pretend that you have two separate tables: the one
from
which you are selecting the first name/team (by dodate), and the one
from
which you are selecting all of the team members.

This is achieved by the use of aliases: by naming (aliasing) the same
table
once as Tbl1 and again as Tbl2, we can treat Tbl1 and Tbl2 as two
different
tables. We can then select the first name/team from Tbl2 (using the
WHERE
clause), and join it to Tbl1 to get all of the team members.

Solutions using a sub-query will also work, but they are cumbersome by
comparison and tend to be slow in Access.

Hope that helps!

Baz

What a great innovative solution!

Perfect result.

Would you please explain how (why) it works?

:

Maybe this (warning: untested):

SELECT Tbl1.name FROM Tbl AS Tbl1 INNER JOIN Tbl AS Tbl2 ON
Tbl1.team
=
Tbl2.team WHERE Tbl2.dodate = [Please Enter the Do Date]

Tbl is as follows;
name, dodate, team.

I must first select the single name that matches a specific
dodate,
then
select all the names that match the team of the first selected
name,
regardles of what their date is.

Short of running a make table for the first select, then using
that
as
a
join with the original tbl, what other options are available?

Thanks for the help.