OK, doing a GCSE database unit with my lot. It's not my strongest area 
but a basic 3-5 table job in Access usually causes me no bother.

This time I have a kid doing a 3 table football database -

Team -< match >- venue

In the match table we have MatchID, VenueID, HomeID and AwayID. Both 
HomeID and AwayID are related to the TeamID in the Team table.

Tables all done, relationships, forms... no problem.

When doing our first query we need to see the name of each team. We can 
get the ID to appear easily enough, but we can't find a way to get the 
appropriate Team Name to work. I reckon we need a query within a query 
(to look up the name using the HomeID/AwayID fields).

As a last resort we could just stick to the ID numbers and discuss it in 
the evaluation but I'd rather find a solution than not, and I'm sure 
there is one...

TIA

HH

On Mon, 23 Apr 2007 19:04:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>OK, doing a GCSE database unit with my lot. It's not my strongest area 
>but a basic 3-5 table job in Access usually causes me no bother.
>
>This time I have a kid doing a 3 table football database -
>
>Team -< match >- venue
>
>In the match table we have MatchID, VenueID, HomeID and AwayID. Both 
>HomeID and AwayID are related to the TeamID in the Team table.
>
>Tables all done, relationships, forms... no problem.
>
>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...
>
Two ways of doing it:

1. The *best* way - you need 4 tables. Team is OK, Venue is OK. Split
Match into:
Match (MatchID, VenueID)
Match_Teams(MatchID,TeamID)

Team -<Match_Team>-Match>-Venue

2. The quick way - copy Teams. Call the the copy Team2. Relate this to
AwayID. Full of problems somewhere down the line...

HTH

-- 
Pope FatMax 2.2

Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
contained the following:

>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...

Team names are unique.  Use them instead of ids. :-)

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

On Mon, 23 Apr 2007 19:37:58 +0100, black-dog
wrote:

>Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>contained the following:
>
>>When doing our first query we need to see the name of each team. We can 
>>get the ID to appear easily enough, but we can't find a way to get the 
>>appropriate Team Name to work. I reckon we need a query within a query 
>>(to look up the name using the HomeID/AwayID fields).
>>
>>As a last resort we could just stick to the ID numbers and discuss it in 
>>the evaluation but I'd rather find a solution than not, and I'm sure 
>>there is one...
>
>Team names are unique.  Use them instead of ids. :-)

Who let the dog out :)

-- 
Pope FatMax 2.2

black-dog wrote:
> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
> contained the following:
> 
>> When doing our first query we need to see the name of each team. We can 
>> get the ID to appear easily enough, but we can't find a way to get the 
>> appropriate Team Name to work. I reckon we need a query within a query 
>> (to look up the name using the HomeID/AwayID fields).
>>
>> As a last resort we could just stick to the ID numbers and discuss it in 
>> the evaluation but I'd rather find a solution than not, and I'm sure 
>> there is one...
> 
> Team names are unique.  Use them instead of ids. :-)
> 
That's an astoundingly obvious suggestion.

I can't help but feel that Fat-Max's is a more... elegant? accurate?... 
solution - but yours is a damn sight quicker and easier for the scope of 
the current project.

I must put FM's on my list of to-do projects though (albeit a very 
looong list) :(

Many thanks to both of you.

HH

On Mon, 23 Apr 2007 19:51:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>black-dog wrote:
>> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>> contained the following:
>> 
>>>
>>> As a last resort we could just stick to the ID numbers and discuss it in 
>>> the evaluation but I'd rather find a solution than not, and I'm sure 
>>> there is one...
>> 
>> Team names are unique.  Use them instead of ids. :-)

>I can't help but feel that FatMax is more... elegant?

Post editing abounds ;)

-- 
Pope FatMax 2.2
Have you seen my latest slippers?
Now *that's* elegant

Message-ID: <462d0082$0$21372$c3e8da3@news.astraweb.com> from Happy
Hippy contained the following:

>> Team names are unique.  Use them instead of ids. :-)
>> 
>That's an astoundingly obvious suggestion.
>
>I can't help but feel that Fat-Max's is a more... elegant? accurate?... 

Normalised.

>solution - but yours is a damn sight quicker and easier for the scope of 
>the current project.

Glad to be of help!

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

Message-ID:  from FatMax
contained the following:

>>Team names are unique.  Use them instead of ids. :-)
>
>Who let the dog out :)

I let myself out.  Voluntary redundancy, remember? ;-)

I still pop into college every now and then though.  It's good to gloat.

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

> This time I have a kid doing a 3 table football database -
>
> Team -< match >- venue
>
> In the match table we have MatchID, VenueID, HomeID and AwayID. Both
> HomeID and AwayID are related to the TeamID in the Team table.
>
> Tables all done, relationships, forms... no problem.
>
> When doing our first query we need to see the name of each team. We can
> get the ID to appear easily enough, but we can't find a way to get the
> appropriate Team Name to work. I reckon we need a query within a query
> (to look up the name using the HomeID/AwayID fields).
>

Using your 3 table model. All you need to do is add a second instance
of the team table to the query.

SELECT
Venue.VenueName as [Venue]
,HomeTeam.TeamName as [Home Team]
,AwayTeam.TeamName as  [Away Team]
FROM
((Match
INNER JOIN Venue ON Match.VenueID = Venue.VenueID)
INNER JOIN Team AS HomeTeam ON Match.HomeID = HomeTeam.TeamID)
INNER JOIN Team AS AwayTeam ON Match.AwayID = AwayTeam.TeamID;

You'll see I have joined the team table twice and aliased one instance
HomeTeam and the other AwayTeam and then
joined these to the respective home and away ID's in the Match table.

Hope this helps you.
Regards
Nigel

On Mon, 23 Apr 2007 19:04:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>OK, doing a GCSE database unit with my lot. It's not my strongest area 
>but a basic 3-5 table job in Access usually causes me no bother.
>
>This time I have a kid doing a 3 table football database -
>
>Team -< match >- venue
>
>In the match table we have MatchID, VenueID, HomeID and AwayID. Both 
>HomeID and AwayID are related to the TeamID in the Team table.
>
>Tables all done, relationships, forms... no problem.
>
>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...
>
Two ways of doing it:

1. The *best* way - you need 4 tables. Team is OK, Venue is OK. Split
Match into:
Match (MatchID, VenueID)
Match_Teams(MatchID,TeamID)

Team -<Match_Team>-Match>-Venue

2. The quick way - copy Teams. Call the the copy Team2. Relate this to
AwayID. Full of problems somewhere down the line...

HTH

-- 
Pope FatMax 2.2

Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
contained the following:

>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...

Team names are unique.  Use them instead of ids. :-)

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

On Mon, 23 Apr 2007 19:37:58 +0100, black-dog
wrote:

>Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>contained the following:
>
>>When doing our first query we need to see the name of each team. We can 
>>get the ID to appear easily enough, but we can't find a way to get the 
>>appropriate Team Name to work. I reckon we need a query within a query 
>>(to look up the name using the HomeID/AwayID fields).
>>
>>As a last resort we could just stick to the ID numbers and discuss it in 
>>the evaluation but I'd rather find a solution than not, and I'm sure 
>>there is one...
>
>Team names are unique.  Use them instead of ids. :-)

Who let the dog out :)

-- 
Pope FatMax 2.2

black-dog wrote:
> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
> contained the following:
> 
>> When doing our first query we need to see the name of each team. We can 
>> get the ID to appear easily enough, but we can't find a way to get the 
>> appropriate Team Name to work. I reckon we need a query within a query 
>> (to look up the name using the HomeID/AwayID fields).
>>
>> As a last resort we could just stick to the ID numbers and discuss it in 
>> the evaluation but I'd rather find a solution than not, and I'm sure 
>> there is one...
> 
> Team names are unique.  Use them instead of ids. :-)
> 
That's an astoundingly obvious suggestion.

I can't help but feel that Fat-Max's is a more... elegant? accurate?... 
solution - but yours is a damn sight quicker and easier for the scope of 
the current project.

I must put FM's on my list of to-do projects though (albeit a very 
looong list) :(

Many thanks to both of you.

HH

On Mon, 23 Apr 2007 19:51:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>black-dog wrote:
>> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>> contained the following:
>> 
>>>
>>> As a last resort we could just stick to the ID numbers and discuss it in 
>>> the evaluation but I'd rather find a solution than not, and I'm sure 
>>> there is one...
>> 
>> Team names are unique.  Use them instead of ids. :-)

>I can't help but feel that FatMax is more... elegant?

Post editing abounds ;)

-- 
Pope FatMax 2.2
Have you seen my latest slippers?
Now *that's* elegant

Message-ID: <462d0082$0$21372$c3e8da3@news.astraweb.com> from Happy
Hippy contained the following:

>> Team names are unique.  Use them instead of ids. :-)
>> 
>That's an astoundingly obvious suggestion.
>
>I can't help but feel that Fat-Max's is a more... elegant? accurate?... 

Normalised.

>solution - but yours is a damn sight quicker and easier for the scope of 
>the current project.

Glad to be of help!

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

Message-ID:  from FatMax
contained the following:

>>Team names are unique.  Use them instead of ids. :-)
>
>Who let the dog out :)

I let myself out.  Voluntary redundancy, remember? ;-)

I still pop into college every now and then though.  It's good to gloat.

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

> This time I have a kid doing a 3 table football database -
>
> Team -< match >- venue
>
> In the match table we have MatchID, VenueID, HomeID and AwayID. Both
> HomeID and AwayID are related to the TeamID in the Team table.
>
> Tables all done, relationships, forms... no problem.
>
> When doing our first query we need to see the name of each team. We can
> get the ID to appear easily enough, but we can't find a way to get the
> appropriate Team Name to work. I reckon we need a query within a query
> (to look up the name using the HomeID/AwayID fields).
>

Using your 3 table model. All you need to do is add a second instance
of the team table to the query.

SELECT
Venue.VenueName as [Venue]
,HomeTeam.TeamName as [Home Team]
,AwayTeam.TeamName as  [Away Team]
FROM
((Match
INNER JOIN Venue ON Match.VenueID = Venue.VenueID)
INNER JOIN Team AS HomeTeam ON Match.HomeID = HomeTeam.TeamID)
INNER JOIN Team AS AwayTeam ON Match.AwayID = AwayTeam.TeamID;

You'll see I have joined the team table twice and aliased one instance
HomeTeam and the other AwayTeam and then
joined these to the respective home and away ID's in the Match table.

Hope this helps you.
Regards
Nigel

On Mon, 23 Apr 2007 19:04:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>OK, doing a GCSE database unit with my lot. It's not my strongest area 
>but a basic 3-5 table job in Access usually causes me no bother.
>
>This time I have a kid doing a 3 table football database -
>
>Team -< match >- venue
>
>In the match table we have MatchID, VenueID, HomeID and AwayID. Both 
>HomeID and AwayID are related to the TeamID in the Team table.
>
>Tables all done, relationships, forms... no problem.
>
>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...
>
Two ways of doing it:

1. The *best* way - you need 4 tables. Team is OK, Venue is OK. Split
Match into:
Match (MatchID, VenueID)
Match_Teams(MatchID,TeamID)

Team -<Match_Team>-Match>-Venue

2. The quick way - copy Teams. Call the the copy Team2. Relate this to
AwayID. Full of problems somewhere down the line...

HTH

-- 
Pope FatMax 2.2

Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
contained the following:

>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...

Team names are unique.  Use them instead of ids. :-)

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

On Mon, 23 Apr 2007 19:37:58 +0100, black-dog
wrote:

>Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>contained the following:
>
>>When doing our first query we need to see the name of each team. We can 
>>get the ID to appear easily enough, but we can't find a way to get the 
>>appropriate Team Name to work. I reckon we need a query within a query 
>>(to look up the name using the HomeID/AwayID fields).
>>
>>As a last resort we could just stick to the ID numbers and discuss it in 
>>the evaluation but I'd rather find a solution than not, and I'm sure 
>>there is one...
>
>Team names are unique.  Use them instead of ids. :-)

Who let the dog out :)

-- 
Pope FatMax 2.2

black-dog wrote:
> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
> contained the following:
> 
>> When doing our first query we need to see the name of each team. We can 
>> get the ID to appear easily enough, but we can't find a way to get the 
>> appropriate Team Name to work. I reckon we need a query within a query 
>> (to look up the name using the HomeID/AwayID fields).
>>
>> As a last resort we could just stick to the ID numbers and discuss it in 
>> the evaluation but I'd rather find a solution than not, and I'm sure 
>> there is one...
> 
> Team names are unique.  Use them instead of ids. :-)
> 
That's an astoundingly obvious suggestion.

I can't help but feel that Fat-Max's is a more... elegant? accurate?... 
solution - but yours is a damn sight quicker and easier for the scope of 
the current project.

I must put FM's on my list of to-do projects though (albeit a very 
looong list) :(

Many thanks to both of you.

HH

On Mon, 23 Apr 2007 19:51:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>black-dog wrote:
>> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>> contained the following:
>> 
>>>
>>> As a last resort we could just stick to the ID numbers and discuss it in 
>>> the evaluation but I'd rather find a solution than not, and I'm sure 
>>> there is one...
>> 
>> Team names are unique.  Use them instead of ids. :-)

>I can't help but feel that FatMax is more... elegant?

Post editing abounds ;)

-- 
Pope FatMax 2.2
Have you seen my latest slippers?
Now *that's* elegant

Message-ID: <462d0082$0$21372$c3e8da3@news.astraweb.com> from Happy
Hippy contained the following:

>> Team names are unique.  Use them instead of ids. :-)
>> 
>That's an astoundingly obvious suggestion.
>
>I can't help but feel that Fat-Max's is a more... elegant? accurate?... 

Normalised.

>solution - but yours is a damn sight quicker and easier for the scope of 
>the current project.

Glad to be of help!

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

Message-ID:  from FatMax
contained the following:

>>Team names are unique.  Use them instead of ids. :-)
>
>Who let the dog out :)

I let myself out.  Voluntary redundancy, remember? ;-)

I still pop into college every now and then though.  It's good to gloat.

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

> This time I have a kid doing a 3 table football database -
>
> Team -< match >- venue
>
> In the match table we have MatchID, VenueID, HomeID and AwayID. Both
> HomeID and AwayID are related to the TeamID in the Team table.
>
> Tables all done, relationships, forms... no problem.
>
> When doing our first query we need to see the name of each team. We can
> get the ID to appear easily enough, but we can't find a way to get the
> appropriate Team Name to work. I reckon we need a query within a query
> (to look up the name using the HomeID/AwayID fields).
>

Using your 3 table model. All you need to do is add a second instance
of the team table to the query.

SELECT
Venue.VenueName as [Venue]
,HomeTeam.TeamName as [Home Team]
,AwayTeam.TeamName as  [Away Team]
FROM
((Match
INNER JOIN Venue ON Match.VenueID = Venue.VenueID)
INNER JOIN Team AS HomeTeam ON Match.HomeID = HomeTeam.TeamID)
INNER JOIN Team AS AwayTeam ON Match.AwayID = AwayTeam.TeamID;

You'll see I have joined the team table twice and aliased one instance
HomeTeam and the other AwayTeam and then
joined these to the respective home and away ID's in the Match table.

Hope this helps you.
Regards
Nigel

On Mon, 23 Apr 2007 19:04:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>OK, doing a GCSE database unit with my lot. It's not my strongest area 
>but a basic 3-5 table job in Access usually causes me no bother.
>
>This time I have a kid doing a 3 table football database -
>
>Team -< match >- venue
>
>In the match table we have MatchID, VenueID, HomeID and AwayID. Both 
>HomeID and AwayID are related to the TeamID in the Team table.
>
>Tables all done, relationships, forms... no problem.
>
>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...
>
Two ways of doing it:

1. The *best* way - you need 4 tables. Team is OK, Venue is OK. Split
Match into:
Match (MatchID, VenueID)
Match_Teams(MatchID,TeamID)

Team -<Match_Team>-Match>-Venue

2. The quick way - copy Teams. Call the the copy Team2. Relate this to
AwayID. Full of problems somewhere down the line...

HTH

-- 
Pope FatMax 2.2

Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
contained the following:

>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...

Team names are unique.  Use them instead of ids. :-)

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

On Mon, 23 Apr 2007 19:37:58 +0100, black-dog
wrote:

>Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>contained the following:
>
>>When doing our first query we need to see the name of each team. We can 
>>get the ID to appear easily enough, but we can't find a way to get the 
>>appropriate Team Name to work. I reckon we need a query within a query 
>>(to look up the name using the HomeID/AwayID fields).
>>
>>As a last resort we could just stick to the ID numbers and discuss it in 
>>the evaluation but I'd rather find a solution than not, and I'm sure 
>>there is one...
>
>Team names are unique.  Use them instead of ids. :-)

Who let the dog out :)

-- 
Pope FatMax 2.2

black-dog wrote:
> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
> contained the following:
> 
>> When doing our first query we need to see the name of each team. We can 
>> get the ID to appear easily enough, but we can't find a way to get the 
>> appropriate Team Name to work. I reckon we need a query within a query 
>> (to look up the name using the HomeID/AwayID fields).
>>
>> As a last resort we could just stick to the ID numbers and discuss it in 
>> the evaluation but I'd rather find a solution than not, and I'm sure 
>> there is one...
> 
> Team names are unique.  Use them instead of ids. :-)
> 
That's an astoundingly obvious suggestion.

I can't help but feel that Fat-Max's is a more... elegant? accurate?... 
solution - but yours is a damn sight quicker and easier for the scope of 
the current project.

I must put FM's on my list of to-do projects though (albeit a very 
looong list) :(

Many thanks to both of you.

HH

On Mon, 23 Apr 2007 19:51:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>black-dog wrote:
>> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>> contained the following:
>> 
>>>
>>> As a last resort we could just stick to the ID numbers and discuss it in 
>>> the evaluation but I'd rather find a solution than not, and I'm sure 
>>> there is one...
>> 
>> Team names are unique.  Use them instead of ids. :-)

>I can't help but feel that FatMax is more... elegant?

Post editing abounds ;)

-- 
Pope FatMax 2.2
Have you seen my latest slippers?
Now *that's* elegant

Message-ID: <462d0082$0$21372$c3e8da3@news.astraweb.com> from Happy
Hippy contained the following:

>> Team names are unique.  Use them instead of ids. :-)
>> 
>That's an astoundingly obvious suggestion.
>
>I can't help but feel that Fat-Max's is a more... elegant? accurate?... 

Normalised.

>solution - but yours is a damn sight quicker and easier for the scope of 
>the current project.

Glad to be of help!

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

Message-ID:  from FatMax
contained the following:

>>Team names are unique.  Use them instead of ids. :-)
>
>Who let the dog out :)

I let myself out.  Voluntary redundancy, remember? ;-)

I still pop into college every now and then though.  It's good to gloat.

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

> This time I have a kid doing a 3 table football database -
>
> Team -< match >- venue
>
> In the match table we have MatchID, VenueID, HomeID and AwayID. Both
> HomeID and AwayID are related to the TeamID in the Team table.
>
> Tables all done, relationships, forms... no problem.
>
> When doing our first query we need to see the name of each team. We can
> get the ID to appear easily enough, but we can't find a way to get the
> appropriate Team Name to work. I reckon we need a query within a query
> (to look up the name using the HomeID/AwayID fields).
>

Using your 3 table model. All you need to do is add a second instance
of the team table to the query.

SELECT
Venue.VenueName as [Venue]
,HomeTeam.TeamName as [Home Team]
,AwayTeam.TeamName as  [Away Team]
FROM
((Match
INNER JOIN Venue ON Match.VenueID = Venue.VenueID)
INNER JOIN Team AS HomeTeam ON Match.HomeID = HomeTeam.TeamID)
INNER JOIN Team AS AwayTeam ON Match.AwayID = AwayTeam.TeamID;

You'll see I have joined the team table twice and aliased one instance
HomeTeam and the other AwayTeam and then
joined these to the respective home and away ID's in the Match table.

Hope this helps you.
Regards
Nigel

On Mon, 23 Apr 2007 19:04:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>OK, doing a GCSE database unit with my lot. It's not my strongest area 
>but a basic 3-5 table job in Access usually causes me no bother.
>
>This time I have a kid doing a 3 table football database -
>
>Team -< match >- venue
>
>In the match table we have MatchID, VenueID, HomeID and AwayID. Both 
>HomeID and AwayID are related to the TeamID in the Team table.
>
>Tables all done, relationships, forms... no problem.
>
>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...
>
Two ways of doing it:

1. The *best* way - you need 4 tables. Team is OK, Venue is OK. Split
Match into:
Match (MatchID, VenueID)
Match_Teams(MatchID,TeamID)

Team -<Match_Team>-Match>-Venue

2. The quick way - copy Teams. Call the the copy Team2. Relate this to
AwayID. Full of problems somewhere down the line...

HTH

-- 
Pope FatMax 2.2

Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
contained the following:

>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...

Team names are unique.  Use them instead of ids. :-)

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

On Mon, 23 Apr 2007 19:37:58 +0100, black-dog
wrote:

>Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>contained the following:
>
>>When doing our first query we need to see the name of each team. We can 
>>get the ID to appear easily enough, but we can't find a way to get the 
>>appropriate Team Name to work. I reckon we need a query within a query 
>>(to look up the name using the HomeID/AwayID fields).
>>
>>As a last resort we could just stick to the ID numbers and discuss it in 
>>the evaluation but I'd rather find a solution than not, and I'm sure 
>>there is one...
>
>Team names are unique.  Use them instead of ids. :-)

Who let the dog out :)

-- 
Pope FatMax 2.2

black-dog wrote:
> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
> contained the following:
> 
>> When doing our first query we need to see the name of each team. We can 
>> get the ID to appear easily enough, but we can't find a way to get the 
>> appropriate Team Name to work. I reckon we need a query within a query 
>> (to look up the name using the HomeID/AwayID fields).
>>
>> As a last resort we could just stick to the ID numbers and discuss it in 
>> the evaluation but I'd rather find a solution than not, and I'm sure 
>> there is one...
> 
> Team names are unique.  Use them instead of ids. :-)
> 
That's an astoundingly obvious suggestion.

I can't help but feel that Fat-Max's is a more... elegant? accurate?... 
solution - but yours is a damn sight quicker and easier for the scope of 
the current project.

I must put FM's on my list of to-do projects though (albeit a very 
looong list) :(

Many thanks to both of you.

HH

On Mon, 23 Apr 2007 19:51:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>black-dog wrote:
>> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>> contained the following:
>> 
>>>
>>> As a last resort we could just stick to the ID numbers and discuss it in 
>>> the evaluation but I'd rather find a solution than not, and I'm sure 
>>> there is one...
>> 
>> Team names are unique.  Use them instead of ids. :-)

>I can't help but feel that FatMax is more... elegant?

Post editing abounds ;)

-- 
Pope FatMax 2.2
Have you seen my latest slippers?
Now *that's* elegant

Message-ID: <462d0082$0$21372$c3e8da3@news.astraweb.com> from Happy
Hippy contained the following:

>> Team names are unique.  Use them instead of ids. :-)
>> 
>That's an astoundingly obvious suggestion.
>
>I can't help but feel that Fat-Max's is a more... elegant? accurate?... 

Normalised.

>solution - but yours is a damn sight quicker and easier for the scope of 
>the current project.

Glad to be of help!

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

Message-ID:  from FatMax
contained the following:

>>Team names are unique.  Use them instead of ids. :-)
>
>Who let the dog out :)

I let myself out.  Voluntary redundancy, remember? ;-)

I still pop into college every now and then though.  It's good to gloat.

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

> This time I have a kid doing a 3 table football database -
>
> Team -< match >- venue
>
> In the match table we have MatchID, VenueID, HomeID and AwayID. Both
> HomeID and AwayID are related to the TeamID in the Team table.
>
> Tables all done, relationships, forms... no problem.
>
> When doing our first query we need to see the name of each team. We can
> get the ID to appear easily enough, but we can't find a way to get the
> appropriate Team Name to work. I reckon we need a query within a query
> (to look up the name using the HomeID/AwayID fields).
>

Using your 3 table model. All you need to do is add a second instance
of the team table to the query.

SELECT
Venue.VenueName as [Venue]
,HomeTeam.TeamName as [Home Team]
,AwayTeam.TeamName as  [Away Team]
FROM
((Match
INNER JOIN Venue ON Match.VenueID = Venue.VenueID)
INNER JOIN Team AS HomeTeam ON Match.HomeID = HomeTeam.TeamID)
INNER JOIN Team AS AwayTeam ON Match.AwayID = AwayTeam.TeamID;

You'll see I have joined the team table twice and aliased one instance
HomeTeam and the other AwayTeam and then
joined these to the respective home and away ID's in the Match table.

Hope this helps you.
Regards
Nigel

On Mon, 23 Apr 2007 19:04:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>OK, doing a GCSE database unit with my lot. It's not my strongest area 
>but a basic 3-5 table job in Access usually causes me no bother.
>
>This time I have a kid doing a 3 table football database -
>
>Team -< match >- venue
>
>In the match table we have MatchID, VenueID, HomeID and AwayID. Both 
>HomeID and AwayID are related to the TeamID in the Team table.
>
>Tables all done, relationships, forms... no problem.
>
>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...
>
Two ways of doing it:

1. The *best* way - you need 4 tables. Team is OK, Venue is OK. Split
Match into:
Match (MatchID, VenueID)
Match_Teams(MatchID,TeamID)

Team -<Match_Team>-Match>-Venue

2. The quick way - copy Teams. Call the the copy Team2. Relate this to
AwayID. Full of problems somewhere down the line...

HTH

-- 
Pope FatMax 2.2

Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
contained the following:

>When doing our first query we need to see the name of each team. We can 
>get the ID to appear easily enough, but we can't find a way to get the 
>appropriate Team Name to work. I reckon we need a query within a query 
>(to look up the name using the HomeID/AwayID fields).
>
>As a last resort we could just stick to the ID numbers and discuss it in 
>the evaluation but I'd rather find a solution than not, and I'm sure 
>there is one...

Team names are unique.  Use them instead of ids. :-)

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

On Mon, 23 Apr 2007 19:37:58 +0100, black-dog
wrote:

>Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>contained the following:
>
>>When doing our first query we need to see the name of each team. We can 
>>get the ID to appear easily enough, but we can't find a way to get the 
>>appropriate Team Name to work. I reckon we need a query within a query 
>>(to look up the name using the HomeID/AwayID fields).
>>
>>As a last resort we could just stick to the ID numbers and discuss it in 
>>the evaluation but I'd rather find a solution than not, and I'm sure 
>>there is one...
>
>Team names are unique.  Use them instead of ids. :-)

Who let the dog out :)

-- 
Pope FatMax 2.2

black-dog wrote:
> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
> contained the following:
> 
>> When doing our first query we need to see the name of each team. We can 
>> get the ID to appear easily enough, but we can't find a way to get the 
>> appropriate Team Name to work. I reckon we need a query within a query 
>> (to look up the name using the HomeID/AwayID fields).
>>
>> As a last resort we could just stick to the ID numbers and discuss it in 
>> the evaluation but I'd rather find a solution than not, and I'm sure 
>> there is one...
> 
> Team names are unique.  Use them instead of ids. :-)
> 
That's an astoundingly obvious suggestion.

I can't help but feel that Fat-Max's is a more... elegant? accurate?... 
solution - but yours is a damn sight quicker and easier for the scope of 
the current project.

I must put FM's on my list of to-do projects though (albeit a very 
looong list) :(

Many thanks to both of you.

HH

On Mon, 23 Apr 2007 19:51:59 +0100, Happy Hippy <not@bleedin.likely>
wrote:

>black-dog wrote:
>> Message-ID: <462cf52c$0$7180$c3e8da3@news.astraweb.com> from Happy Hippy
>> contained the following:
>> 
>>>
>>> As a last resort we could just stick to the ID numbers and discuss it in 
>>> the evaluation but I'd rather find a solution than not, and I'm sure 
>>> there is one...
>> 
>> Team names are unique.  Use them instead of ids. :-)

>I can't help but feel that FatMax is more... elegant?

Post editing abounds ;)

-- 
Pope FatMax 2.2
Have you seen my latest slippers?
Now *that's* elegant

Message-ID: <462d0082$0$21372$c3e8da3@news.astraweb.com> from Happy
Hippy contained the following:

>> Team names are unique.  Use them instead of ids. :-)
>> 
>That's an astoundingly obvious suggestion.
>
>I can't help but feel that Fat-Max's is a more... elegant? accurate?... 

Normalised.

>solution - but yours is a damn sight quicker and easier for the scope of 
>the current project.

Glad to be of help!

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

Message-ID:  from FatMax
contained the following:

>>Team names are unique.  Use them instead of ids. :-)
>
>Who let the dog out :)

I let myself out.  Voluntary redundancy, remember? ;-)

I still pop into college every now and then though.  It's good to gloat.

-- 
black-dog

"Always spellcheck your wok to avoid mistakes"

> This time I have a kid doing a 3 table football database -
>
> Team -< match >- venue
>
> In the match table we have MatchID, VenueID, HomeID and AwayID. Both
> HomeID and AwayID are related to the TeamID in the Team table.
>
> Tables all done, relationships, forms... no problem.
>
> When doing our first query we need to see the name of each team. We can
> get the ID to appear easily enough, but we can't find a way to get the
> appropriate Team Name to work. I reckon we need a query within a query
> (to look up the name using the HomeID/AwayID fields).
>

Using your 3 table model. All you need to do is add a second instance
of the team table to the query.

SELECT
Venue.VenueName as [Venue]
,HomeTeam.TeamName as [Home Team]
,AwayTeam.TeamName as  [Away Team]
FROM
((Match
INNER JOIN Venue ON Match.VenueID = Venue.VenueID)
INNER JOIN Team AS HomeTeam ON Match.HomeID = HomeTeam.TeamID)
INNER JOIN Team AS AwayTeam ON Match.AwayID = AwayTeam.TeamID;

You'll see I have joined the team table twice and aliased one instance
HomeTeam and the other AwayTeam and then
joined these to the respective home and away ID's in the Match table.

Hope this helps you.
Regards
Nigel