qry pulling wrong data

[blee via AccessMonster.com]

HELP! I'm at wit's end w/ this beast; thank you for your help. Any assistance
with this particular project would be much appreciated, and also any
recommendations on good Access books for beginners.

I have 2 tables, one for Athletes, one for Injuries, which have the common
field of Athlete ID #. There is an Injury Report based on a query of the
Injury table and Athlete table (to correlate Athlete Name with the ID#).
Problem is, the query pulls together the wrong data: it works fine for ID#
alone, but adding First and/or Last Name makes the query (and thus report)
list the wrong athlete w/ the injury.

Any suggestions as to what I'm doing wrong or missing?

Thank you
Brian

 

[Jeff Boyce]

Brian

Post the SQL of your query. Open the query in design view, change the view
to SQL, copy the SQL statement, and paste it into your next post.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[blee via AccessMonster.com]

Here it is:

SELECT tblAthletes.AthFirstName, tblAthletes.AthLastName, tblInjuryTracking.
[Injured Region], tblInjuryTracking.[Injury Description], tblInjuryTracking.
DOI, tblInjuryTracking.[Injury Location], tblInjuryTracking.[Went to ER?],
tblInjuryTracking.[Called Guardian?], tblInjuryTracking.[MD Consult],
tblInjuryTracking.[Exempt from PE?], tblInjuryTracking.[Need to be seen by PA?
], tblInjuryTracking.[Date Cleared]
FROM tblAthletes INNER JOIN tblInjuryTracking ON tblAthletes.AthleteID =
tblInjuryTracking.AthleteID;

Thanks

Brian

Post the SQL of your query. Open the query in design view, change the view
to SQL, copy the SQL statement, and paste it into your next post.

Regards

Jeff Boyce
Microsoft Office/Access MVP

HELP! I'm at wit's end w/ this beast; thank you for your help. Any
assistance

[quoted text clipped - 12 lines]

Thank you
Brian

 

[blee via AccessMonster.com]

Any errors jumping out here?

Thanks
Brian

Here it is:

SELECT tblAthletes.AthFirstName, tblAthletes.AthLastName, tblInjuryTracking.
[Injured Region], tblInjuryTracking.[Injury Description], tblInjuryTracking.
DOI, tblInjuryTracking.[Injury Location], tblInjuryTracking.[Went to ER?],
tblInjuryTracking.[Called Guardian?], tblInjuryTracking.[MD Consult],
tblInjuryTracking.[Exempt from PE?], tblInjuryTracking.[Need to be seen by PA?
], tblInjuryTracking.[Date Cleared]
FROM tblAthletes INNER JOIN tblInjuryTracking ON tblAthletes.AthleteID =
tblInjuryTracking.AthleteID;

Thanks

Brian

[quoted text clipped - 11 lines]

 

[Jeff Boyce]

The SQL seems reasonable, so my next question is about the underlying data.
Can you describe the field types for the Athlete and Injury primary and
foreign keys?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Any errors jumping out here?

Thanks
Brian

Here it is:

SELECT tblAthletes.AthFirstName, tblAthletes.AthLastName,
tblInjuryTracking.
[Injured Region], tblInjuryTracking.[Injury Description],
tblInjuryTracking.
DOI, tblInjuryTracking.[Injury Location], tblInjuryTracking.[Went to ER?],
tblInjuryTracking.[Called Guardian?], tblInjuryTracking.[MD Consult],
tblInjuryTracking.[Exempt from PE?], tblInjuryTracking.[Need to be seen by
PA?
], tblInjuryTracking.[Date Cleared]
FROM tblAthletes INNER JOIN tblInjuryTracking ON tblAthletes.AthleteID =
tblInjuryTracking.AthleteID;

Thanks

Brian

[quoted text clipped - 11 lines]

Thank you
Brian

 

[blee via AccessMonster.com]

AthleteID is the Primary Key in Athlete table; it is an AutoNumber.
InjuryID is the Primary Key in Injuries table; it is an AutoNumber. AthleteID
is in this table as a Number field.
I get the error message "typed incorrectly or too complex to be evaluated"
when clicking on "+" in Athlete table.

Thank you
Brian

The SQL seems reasonable, so my next question is about the underlying data.
Can you describe the field types for the Athlete and Injury primary and
foreign keys?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Any errors jumping out here?

[quoted text clipped - 22 lines]

 

[Jeff Boyce]

?! ... the "+" in the Athlete table?!

This implies you are working directly in the tables, rather than via queries
and forms. Access tables are great data containers, but, contrary to their
superficial appearance, are NOT spreadsheets. The implication of the error
message is that there's an issue with data typing.

Your AthleteID (foreign key) in the Injuries table is a "number" field ...
what type of number?

Regards

Jeff Boyce
Microsoft Office/Access MVP

AthleteID is the Primary Key in Athlete table; it is an AutoNumber.
InjuryID is the Primary Key in Injuries table; it is an AutoNumber.
AthleteID
is in this table as a Number field.
I get the error message "typed incorrectly or too complex to be evaluated"
when clicking on "+" in Athlete table.

Thank you
Brian

The SQL seems reasonable, so my next question is about the underlying
data.
Can you describe the field types for the Athlete and Injury primary and
foreign keys?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Any errors jumping out here?

[quoted text clipped - 22 lines]

Thank you
Brian

 

[blee via AccessMonster.com]

Sorry, new to Access. A foreign key is a key from another table?

AthleteID is "long integer"; Row Source: SELECT tblAthletes.AthleteID,
tblAthletes.AthLastName FROM tblAthletes ORDER BY tblAthletes.AthLastName,
tblAthletes.AthFirstName;

Brian

?! ... the "+" in the Athlete table?!

This implies you are working directly in the tables, rather than via queries
and forms. Access tables are great data containers, but, contrary to their
superficial appearance, are NOT spreadsheets. The implication of the error
message is that there's an issue with data typing.

Your AthleteID (foreign key) in the Injuries table is a "number" field ...
what type of number?

Regards

Jeff Boyce
Microsoft Office/Access MVP

AthleteID is the Primary Key in Athlete table; it is an AutoNumber.
InjuryID is the Primary Key in Injuries table; it is an AutoNumber.

[quoted text clipped - 21 lines]

 

[Jeff Boyce]

Brian

The AthleteID field in the Injuries table is a long integer, right? The
"RowSource" information implies that your Injuries table uses a "lookup"
field for AthleteID in Injuries. Lookup fields are quite confusing and
generally frowned on in these newsgroups. The main reason for this is that
the field stores one thing, but displays something else. It could be that
the problem you are seeing is related to this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, new to Access. A foreign key is a key from another table?

AthleteID is "long integer"; Row Source: SELECT tblAthletes.AthleteID,
tblAthletes.AthLastName FROM tblAthletes ORDER BY tblAthletes.AthLastName,
tblAthletes.AthFirstName;

Brian

?! ... the "+" in the Athlete table?!

This implies you are working directly in the tables, rather than via
queries
and forms. Access tables are great data containers, but, contrary to
their
superficial appearance, are NOT spreadsheets. The implication of the
error
message is that there's an issue with data typing.

Your AthleteID (foreign key) in the Injuries table is a "number" field ...
what type of number?

Regards

Jeff Boyce
Microsoft Office/Access MVP

AthleteID is the Primary Key in Athlete table; it is an AutoNumber.
InjuryID is the Primary Key in Injuries table; it is an AutoNumber.

[quoted text clipped - 21 lines]

Thank you
Brian

 

[blee via AccessMonster.com]

Thanks, Jeff.

Yes, AthleteID is long integer in Injuries table. The form which collects the
data for the Injuries table uses a combo box called "Athlete ID" to enable
the user to look up athletes, and then enter data regarding an injury. What
other way can I have the data in the Athlete table available for reference on
the Injuries form/table?

Thank you
Brian

Brian

The AthleteID field in the Injuries table is a long integer, right? The
"RowSource" information implies that your Injuries table uses a "lookup"
field for AthleteID in Injuries. Lookup fields are quite confusing and
generally frowned on in these newsgroups. The main reason for this is that
the field stores one thing, but displays something else. It could be that
the problem you are seeing is related to this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, new to Access. A foreign key is a key from another table?

[quoted text clipped - 27 lines]

 

[Jeff Boyce]

Using the form (and the combo box) is the common method you'll see here in
the 'groups.

I'm not sure I can help with the original issue...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks, Jeff.

Yes, AthleteID is long integer in Injuries table. The form which collects
the
data for the Injuries table uses a combo box called "Athlete ID" to enable
the user to look up athletes, and then enter data regarding an injury.
What
other way can I have the data in the Athlete table available for reference
on
the Injuries form/table?

Thank you
Brian

Brian

The AthleteID field in the Injuries table is a long integer, right? The
"RowSource" information implies that your Injuries table uses a "lookup"
field for AthleteID in Injuries. Lookup fields are quite confusing and
generally frowned on in these newsgroups. The main reason for this is
that
the field stores one thing, but displays something else. It could be that
the problem you are seeing is related to this...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sorry, new to Access. A foreign key is a key from another table?

[quoted text clipped - 27 lines]

Thank you
Brian