Table Relationship Question

[Guest]

My son is a football coach. I'm trying to set up a DB that he can use to
look at a variety of values related to a player in a variety of different
ways. All data are related to the athlete, so I really only need one table.
However, in order to not store calculated values and not hardcode formulas
into the queries of that single table, I'd rather use tables that use a
calculated value to determine other, basically calculated, values. For
example, if a player lifts more than 630 pounds (total of 3 weight lifting
exercises), then he is in Group 1; between 560 and 630, Group 2, etc. The
coach will enter values for the three exercises and a query can calculate the
total, but how do I then use that value to access the table that will return
the Group number? In Excel, it's a 'lookup' function. Then there are
subgroups within each group, used to distribute the players among limited
numbers of weight lifting stations. If the player is in Group 1 and bench
presses less than 215 lbs, then he's in sub-group B, otherwise he's in
sub-group A. Seems like another table keyed by calculated values. However,
I'm having trouble discovering how to do this.

 

[John Vinson]

My son is a football coach. I'm trying to set up a DB that he can use to
look at a variety of values related to a player in a variety of different
ways. All data are related to the athlete, so I really only need one table.

Um?

You don't need a table of Positions? Teams? Games?

However, in order to not store calculated values and not hardcode formulas
into the queries of that single table, I'd rather use tables that use a
calculated value to determine other, basically calculated, values. For
example, if a player lifts more than 630 pounds (total of 3 weight lifting
exercises), then he is in Group 1; between 560 and 630, Group 2, etc. The
coach will enter values for the three exercises and a query can calculate the
total, but how do I then use that value to access the table that will return
the Group number? In Excel, it's a 'lookup' function.

This could be done using the builtin DLookUp() function, but I'd
really suggest using a fairly advanced query technique called a "non
equi join". If you create a LiftGroups table like:

Group Low High
1 630 10000 <hey, Superman might show up>
2 530 630
3 480 530
<etc>

You can then include this in a Query with a JOIN clause (in the SQL
window) like

INNER JOIN LiftGroups
ON yourtable.Weight >= LiftGroups.Low AND yourtable.Weight <
LiftGroups.High

Then there are
subgroups within each group, used to distribute the players among limited
numbers of weight lifting stations. If the player is in Group 1 and bench
presses less than 215 lbs, then he's in sub-group B, otherwise he's in
sub-group A. Seems like another table keyed by calculated values. However,
I'm having trouble discovering how to do this.

Not sure how you're defining the subgroup but a similar table should
work.


John W. Vinson[MVP]

 

[Guest]

I understand the 'join' theory and have tried all I know to get this to work,
but I get nothing but a series of error messages, from 'join not supported'
to 'syntax error', to 'operator missing'; all depending on what I try. The
table relationship setup does not seem to support this kind of join, as it
wants to look at fields that are equal. From what I can see, the DLookup()
expression is VB related, is that correct? Any suggestions about where to
look for ways to debug what I'm not doing right?

 

[John Vinson]

I understand the 'join' theory and have tried all I know to get this to work,
but I get nothing but a series of error messages, from 'join not supported'
to 'syntax error', to 'operator missing'; all depending on what I try. The
table relationship setup does not seem to support this kind of join, as it
wants to look at fields that are equal. From what I can see, the DLookup()
expression is VB related, is that correct? Any suggestions about where to
look for ways to debug what I'm not doing right?

Please describe the tables you're trying to join. Indicate the
relevant fieldnames and the data type of each. It might also help to
open a query in SQL view and post it here, with the error message that
it's giving.

Non-equi joins are obscure and can be tricky, but they DO work.

John W. Vinson[MVP]

 

[Guest]

Error Message: Syntax error (missing operator) in query expression SELECT
PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS TotalLift
FROM PlayerInformation INNER JOIN GroupBreakpoint ON
(PlayerInformation.TotalLift<GroupBreakpoint.TotalHigh) AND
(PlayerInformation.TotalLift>=GroupBreakpoint.TotalLow) AS GroupNum;

There is the query and the error message. If I leave off 'AS GroupNum' I
get a dialogue box requesting a value for TotalLift, but no error message.

PlayerInformation is the table with data unique to each player. The name
fields are text and the Bench/Squat/CleanMas fields are Integer.

The GroupBreakpoint table is set up as per your suggestion in this set of
emails. TotalHigh and TotalLow are Double, but I'm not sure why I did that.

Are there table relationships that should be established before this will
work? In most examples of INNER JOIN, there is an expectation that fields
from the joined tables will be equal at some point.

Thanks for your help.

 

[John Vinson]

Error Message: Syntax error (missing operator) in query expression SELECT
PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS TotalLift
FROM PlayerInformation INNER JOIN GroupBreakpoint ON
(PlayerInformation.TotalLift<GroupBreakpoint.TotalHigh) AND
(PlayerInformation.TotalLift>=GroupBreakpoint.TotalLow) AS GroupNum;

There is the query and the error message. If I leave off 'AS GroupNum' I
get a dialogue box requesting a value for TotalLift, but no error message.

You can't define an alias (AS GroupNum) in a join clause - only in a
SELECT clause. It's not at all clear what you want the GroupNum alias
to refer to!

Given the fact that you're joining on a sum of values rather than on a
value, I'd suggest a different approach: a subquery. Try

SELECT
PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS
TotalLift,
(SELECT GroupNum FROM GroupBreakpoint WHERE
[BenchMax]+[SquatMax]+[CleanMax]<GroupBreakpoint.TotalHigh AND
[BenchMax]+[SquatMax]+[CleanMax]>=GroupBreakpoint.TotalLow) AS
GroupNum
FROM PlayerInformation;

PlayerInformation is the table with data unique to each player. The name
fields are text and the Bench/Squat/CleanMas fields are Integer.

The GroupBreakpoint table is set up as per your suggestion in this set of
emails. TotalHigh and TotalLow are Double, but I'm not sure why I did that.

They should be Integer to match the weight fields.

Are there table relationships that should be established before this will
work? In most examples of INNER JOIN, there is an expectation that fields
from the joined tables will be equal at some point.

Most - but in this case that's not what you want. No, there would not
need to be a defined relationship.

John W. Vinson[MVP]

 

[Guest]

Very helpful clarifications and suggestions! I'll work with this and report
how it comes out. Thanks!

Error Message: Syntax error (missing operator) in query expression SELECT
PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS TotalLift
FROM PlayerInformation INNER JOIN GroupBreakpoint ON
(PlayerInformation.TotalLift<GroupBreakpoint.TotalHigh) AND
(PlayerInformation.TotalLift>=GroupBreakpoint.TotalLow) AS GroupNum;

There is the query and the error message. If I leave off 'AS GroupNum' I
get a dialogue box requesting a value for TotalLift, but no error message.

You can't define an alias (AS GroupNum) in a join clause - only in a
SELECT clause. It's not at all clear what you want the GroupNum alias
to refer to!

Given the fact that you're joining on a sum of values rather than on a
value, I'd suggest a different approach: a subquery. Try

SELECT
PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS
TotalLift,
(SELECT GroupNum FROM GroupBreakpoint WHERE
[BenchMax]+[SquatMax]+[CleanMax]<GroupBreakpoint.TotalHigh AND
[BenchMax]+[SquatMax]+[CleanMax]>=GroupBreakpoint.TotalLow) AS
GroupNum
FROM PlayerInformation;

PlayerInformation is the table with data unique to each player. The name
fields are text and the Bench/Squat/CleanMas fields are Integer.

The GroupBreakpoint table is set up as per your suggestion in this set of
emails. TotalHigh and TotalLow are Double, but I'm not sure why I did that.

They should be Integer to match the weight fields.

Are there table relationships that should be established before this will
work? In most examples of INNER JOIN, there is an expectation that fields
from the joined tables will be equal at some point.

Most - but in this case that's not what you want. No, there would not
need to be a defined relationship.

John W. Vinson[MVP]

 

[Guest]

This sub-select worked very well. Thanks again.

Very helpful clarifications and suggestions! I'll work with this and report
how it comes out. Thanks!

Error Message: Syntax error (missing operator) in query expression SELECT
PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS TotalLift
FROM PlayerInformation INNER JOIN GroupBreakpoint ON
(PlayerInformation.TotalLift<GroupBreakpoint.TotalHigh) AND
(PlayerInformation.TotalLift>=GroupBreakpoint.TotalLow) AS GroupNum;

There is the query and the error message. If I leave off 'AS GroupNum' I
get a dialogue box requesting a value for TotalLift, but no error message.

You can't define an alias (AS GroupNum) in a join clause - only in a
SELECT clause. It's not at all clear what you want the GroupNum alias
to refer to!

Given the fact that you're joining on a sum of values rather than on a
value, I'd suggest a different approach: a subquery. Try

SELECT
PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS
TotalLift,
(SELECT GroupNum FROM GroupBreakpoint WHERE
[BenchMax]+[SquatMax]+[CleanMax]<GroupBreakpoint.TotalHigh AND
[BenchMax]+[SquatMax]+[CleanMax]>=GroupBreakpoint.TotalLow) AS
GroupNum
FROM PlayerInformation;

PlayerInformation is the table with data unique to each player. The name
fields are text and the Bench/Squat/CleanMas fields are Integer.

The GroupBreakpoint table is set up as per your suggestion in this set of
emails. TotalHigh and TotalLow are Double, but I'm not sure why I did that.

They should be Integer to match the weight fields.

Are there table relationships that should be established before this will
work? In most examples of INNER JOIN, there is an expectation that fields
from the joined tables will be equal at some point.

Most - but in this case that's not what you want. No, there would not
need to be a defined relationship.

John W. Vinson[MVP]

 

[Guest]

One more step in this effort.

Now I need to use the calculated GroupNum returned by the sub-select you
suggested in order to determine other values. Within the group of players of
similar strength (the reason for the group), there are two sub-groups for
each lifting exercise: bench press, squat, clean. These sub-groups help
distribute the players over the two lifting stations for each exercise.
These sub-groups are based on the strength of each player. So, for example,
within the top lifting group, there are those to lift relatively more or less
in bench press, squat, and clean. Since each player is different, a player
can be in the lower lifting group for clean but the upper group for bench
press, etc. These sub-groups do not effect the overall group rank.

I have a table that looks something like this with respect to bench press,
using the same pattern for the other exercises.

GroupNum BenchBreakLow BenchBreakHigh BenchSubGroup
1 0 215 B
1 216 500 A

I have tried the following sub-query, thinking that the GroupNum determined
by the recent effort would be used by the new sub-query. But that doesn't
work. I get a dialogue box asking me to enter a GroupNum number.

SELECT PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS TotalLift,
(SELECT GroupNumber FROM GroupBreakpoint WHERE
[BenchMax]+[SquatMax]+[CleanMax] < GroupBreakpoint.TotalHigh AND
[BenchMax]+[SquatMax]+[CleanMax] >= GroupBreakpoint.TotalLow) AS GroupNum,
(SELECT BenchSubGroup FROM LiftSubGroup WHERE GroupNum =
LiftSubGroup.GroupNumber AND PlayerInformation.BenchMax >
LiftSubGroup.BenchBreakLow AND PlayerInformation.BenchMax <=
LiftSubGroup.BenchBreakHigh) AS BenchSub
FROM PlayerInformation;

A similar sub-query will be used to retrieve the Squat and Clean sub-groups.

Any suggestions?

This sub-select worked very well. Thanks again.

Very helpful clarifications and suggestions! I'll work with this and report
how it comes out. Thanks!

On Tue, 8 Aug 2006 12:59:02 -0700, GeorgeB

Error Message: Syntax error (missing operator) in query expression SELECT
PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS TotalLift
FROM PlayerInformation INNER JOIN GroupBreakpoint ON
(PlayerInformation.TotalLift<GroupBreakpoint.TotalHigh) AND
(PlayerInformation.TotalLift>=GroupBreakpoint.TotalLow) AS GroupNum;

There is the query and the error message. If I leave off 'AS GroupNum' I
get a dialogue box requesting a value for TotalLift, but no error message.

You can't define an alias (AS GroupNum) in a join clause - only in a
SELECT clause. It's not at all clear what you want the GroupNum alias
to refer to!

Given the fact that you're joining on a sum of values rather than on a
value, I'd suggest a different approach: a subquery. Try

SELECT
PlayerInformation.LastName, PlayerInformation.FirstName,
PlayerInformation.BenchMax, PlayerInformation.SquatMax,
PlayerInformation.CleanMax, [BenchMax]+[SquatMax]+[CleanMax] AS
TotalLift,
(SELECT GroupNum FROM GroupBreakpoint WHERE
[BenchMax]+[SquatMax]+[CleanMax]<GroupBreakpoint.TotalHigh AND
[BenchMax]+[SquatMax]+[CleanMax]>=GroupBreakpoint.TotalLow) AS
GroupNum
FROM PlayerInformation;

PlayerInformation is the table with data unique to each player. The name
fields are text and the Bench/Squat/CleanMas fields are Integer.

The GroupBreakpoint table is set up as per your suggestion in this set of
emails. TotalHigh and TotalLow are Double, but I'm not sure why I did that.

They should be Integer to match the weight fields.

Are there table relationships that should be established before this will
work? In most examples of INNER JOIN, there is an expectation that fields
from the joined tables will be equal at some point.

Most - but in this case that's not what you want. No, there would not
need to be a defined relationship.

John W. Vinson[MVP]