query won't return results

[Joanne]

I have 3 tables, tblCourses, tblHoles, tblScores

tblCourses tblHoles tblScores
CourseID HolesID ScoresID
Name CourseID HolesID
City HoleNumber HoleNumber
Etc Par Approach
etc putts
etc

tblHoles has CourseID as foreign key
tblScores has HolesID as foreign key

These tables are related using CourseID to CourseID, and HolesID to
HolesID as shown above.

I want to use this query to populate my form, but the query will not
return any results, not in the form, and not in the query builder.

I simply cannot figure out why I get no info in my form fields.

Also, do I need CourseID in the Scores table, or will they synchronize
because courseID is related to holes table, and holesID is related to
scores table?

Could someone please help me here.

Thanks
Joanne

 

[Ken Snell [MVP]]

What query are you trying to use? Post the SQL statement of it.

 

[Joanne]

Ken
Thanks for your consideration of my problem.
Following is the SQL statement of my query

SELECT tblCourses.CourseID, tblCourses.Name, tblCourses.Street1,
tblCourses.City, tblCourses.State, tblCourses.Zip, tblCourses.Phone,
tblCourses.WebSite, tblHoles.HoleID, tblHoles.HoleNumber,
tblHoles.WhiteTees, tblHoles.RedTees, tblHoles.Par AS tblHoles_Par,
tblScores.ScoreID, tblScores.Date, tblScores.Green, tblScores.Fairway,
tblScores.Sand, tblScores.Water, tblScores.Lost, tblScores.Fringe,
tblScores.Approach, tblScores.Putts, tblScores.Pars
FROM (tblCourses INNER JOIN tblHoles ON tblCourses.CourseID =
tblHoles.CourseID) INNER JOIN tblScores ON tblHoles.HoleID =
tblScores.HoleID;

 

[Ken Snell [MVP]]

Let's back up here. The query that you've provided is one that would be
acceptable for a report, but it's not one that is useful for entering new
data.

Your table structure has tblCourses as the main table. You must have a
record in that table before you can enter a related record in tblHoles. And
you need a record in tblHoles before you can enter a related record in
tblScores. So, your data entry process first needs to focus on entering data
into tblCourses, then on entering data into tblHoles, and then into
tblScores.

Create a form that is based on a query that uses tblCourses as its data
table. Use that form to enter data into tblCourses.

You can follow a similar process with individual forms for each table, but
ACCESS provides the ability for you to use subforms. So, you could create a
form for entering data into tblCourses, and then have a subform in that form
(tied to the main form by the linking fields -- use the LinkMasterFields and
LinkChildFields properties of the subform control) where the subform is
based on tblHoles; you then can enter data in the main part of the form and
then in the subform for that course.

And you can set up a form that has tblHoles as the source of data for the
main form, and have the subform be the tblScores data.

 

[Joanne]

Thanks Ken, I'll work on this.
I completely understand that the main form would be based on
tblcourses and the subform would be based on tblholes to get all the
info in for the actual golf courses.

Then I need to get my score from the course into the database. If I
understand you correctly, I should create a second form, using
tblholes as the record source for it and creating a subform here also
in which I can enter my scores for each hole.

So when I want to print out a score card / course info sheet for my
next round, I would use a report that is based on all three tables
allowing me to have the course info, hole info, and then the blanks
for score info that I would then fill in as I complete the round and
subsequently enter into the database.

Am I in the ball park (or on the course) with my understanding here?

Thanks a lot for your help and clarification Ken

 

[Ken Snell [MVP]]

Yes, I believe you're on the right track (or course).