query returns no records

J

Joanne

I have 3 tables (I have records in each of them) and I want to create
a query of all fields in all the tables on which to base a report
and/or a form.

When I run the query (SQL statement below) it returns no records at
all. Can't figure out what I am doing wrong. One thing I am
absolutely unsure of is am I supposed to put the primary and foreign
keys from the tables into the query, or will the records stay
synchronized on the reports and/or forms without them being in the
query?

Using MSAccess 2003, WinXP


SELECT tblCourses.Name, tblCourses.Street1, tblCourses.City,
tblCourses.State, tblCourses.Zip, tblCourses.Phone,
tblCourses.WebSite, tblCourses.Picture, tblCourses.In_Yardage,
tblCourses.Out_Yardage, tblHoles.HoleNumber, tblHoles.WhiteTees,
tblHoles.RedTees, tblHoles.[Gold Tees], tblHoles.[Blue Tees],
tblHoles.[Black Tees], tblHoles.Par, tblScores.Date,
tblScores.HoleNum, tblScores.Green, tblScores.Fairway, tblScores.Sand,
tblScores.Water, tblScores.Lost, tblScores.Fringe, tblScores.Approach,
tblScores.Putts, tblScores.ParsMade
FROM (tblCourses INNER JOIN tblScores ON tblCourses.CourseID =
tblScores.CourseID) INNER JOIN tblHoles ON tblCourses.CourseID =
tblHoles.CourseID;

Your help is as always very much appreciated.

Joanne
 
K

Ken Snell [MVP]

Joanne -

Did you change the table relationships since you and I chatted about your
form setups? The query that you have right now is relating Scores to
Courses. However, your earlier table design had Scores being related to
Holes, and Holes related to Courses.

Try this query:

SELECT tblCourses.Name, tblCourses.Street1, tblCourses.City,
tblCourses.State, tblCourses.Zip, tblCourses.Phone,
tblCourses.WebSite, tblCourses.Picture, tblCourses.In_Yardage,
tblCourses.Out_Yardage, tblHoles.HoleNumber, tblHoles.WhiteTees,
tblHoles.RedTees, tblHoles.[Gold Tees], tblHoles.[Blue Tees],
tblHoles.[Black Tees], tblHoles.Par, tblScores.Date,
tblScores.HoleNum, tblScores.Green, tblScores.Fairway, tblScores.Sand,
tblScores.Water, tblScores.Lost, tblScores.Fringe, tblScores.Approach,
tblScores.Putts, tblScores.ParsMade
FROM (tblScores INNER JOIN tblHoles ON tblScores.HoleID =
tblHoles.HoleID) INNER JOIN tblCourses ON tblCourses.CourseID =
tblHoles.CourseID;
 
J

Joanne

Ken
Yes I did change the relationships but now I have set them back to
what you originally adivsed. I was confused as to how to pull up one
specific round of golf, and thought that I should use the date of the
round as the criteria to limit the holes to a particular round, so
that is why I was messing around with the relationships. Also, I have
added a few fields due to the different yardages of the different
colored T boxes.
So here is the sql statement with all of the above applied, and still
I am getting no records returned.

tblCourses has 2 records (Course name etc) in it
tblHoles has 36 records (hole info on 2 courses) in it
tblScores has 9 records (9 holes, 1 round of golf) in it

I see by reading thru the sql statement that the primary and foreign
keys are omitted from the query statement. Is this correct procedure
or should I be putting the primary and foreign keys into the query
grid when designing the query? Could the fact that I omitted the keys
in the query grid be why I am not getting the records to appear?

SELECT tblCourses.Name, tblCourses.Street1, tblCourses.City,
tblCourses.State, tblCourses.Zip, tblCourses.Phone,
tblCourses.WebSite, tblCourses.Picture, tblCourses.[White In_Yardage],
tblCourses.[White Out_Yardage], tblCourses.[Red In_Yardage],
tblCourses.[Red Out_Yardage], tblCourses.[Gold In_Yardage],
tblCourses.[Gold Out_Yardage], tblCourses.[Blue In_Yardage],
tblCourses.[Blue Out_Yardage], tblCourses.[Black In_Yardage],
tblCourses.[Blace Out_Yardage], tblHoles.HoleNumber,
tblHoles.WhiteTees, tblHoles.RedTees, tblHoles.[Gold Tees],
tblHoles.[Blue Tees], tblHoles.[Black Tees], tblHoles.Par,
tblScores.Date, tblScores.HoleNum, tblScores.Green, tblScores.Fairway,
tblScores.Sand, tblScores.Water, tblScores.Lost, tblScores.Fringe,
tblScores.Approach, tblScores.Putts, tblScores.ParsMade
FROM (tblCourses INNER JOIN tblHoles ON tblCourses.CourseID =
tblHoles.CourseID) INNER JOIN tblScores ON tblHoles.HoleID =
tblScores.HoleID;

Thanks a bunch for your time and patience
Joanne
Joanne -

Did you change the table relationships since you and I chatted about your
form setups? The query that you have right now is relating Scores to
Courses. However, your earlier table design had Scores being related to
Holes, and Holes related to Courses.

Try this query:

SELECT tblCourses.Name, tblCourses.Street1, tblCourses.City,
tblCourses.State, tblCourses.Zip, tblCourses.Phone,
tblCourses.WebSite, tblCourses.Picture, tblCourses.In_Yardage,
tblCourses.Out_Yardage, tblHoles.HoleNumber, tblHoles.WhiteTees,
tblHoles.RedTees, tblHoles.[Gold Tees], tblHoles.[Blue Tees],
tblHoles.[Black Tees], tblHoles.Par, tblScores.Date,
tblScores.HoleNum, tblScores.Green, tblScores.Fairway, tblScores.Sand,
tblScores.Water, tblScores.Lost, tblScores.Fringe, tblScores.Approach,
tblScores.Putts, tblScores.ParsMade
FROM (tblScores INNER JOIN tblHoles ON tblScores.HoleID =
tblHoles.HoleID) INNER JOIN tblCourses ON tblCourses.CourseID =
tblHoles.CourseID;
Click to expand...
 
K

Ken Snell [MVP]

So long as the query does not need to be used for entering data, you do not
need to include the primary and foreign keys as output fields for the query.

When you say the query is not returning any records, do you mean that no
records are displayed if you open the query directly from the database
window? or that no records are displayed in a form or report that is based
on this query?

--

Ken Snell
<MS ACCESS MVP>

Joanne said:
Ken
Yes I did change the relationships but now I have set them back to
what you originally adivsed. I was confused as to how to pull up one
specific round of golf, and thought that I should use the date of the
round as the criteria to limit the holes to a particular round, so
that is why I was messing around with the relationships. Also, I have
added a few fields due to the different yardages of the different
colored T boxes.
So here is the sql statement with all of the above applied, and still
I am getting no records returned.

tblCourses has 2 records (Course name etc) in it
tblHoles has 36 records (hole info on 2 courses) in it
tblScores has 9 records (9 holes, 1 round of golf) in it

I see by reading thru the sql statement that the primary and foreign
keys are omitted from the query statement. Is this correct procedure
or should I be putting the primary and foreign keys into the query
grid when designing the query? Could the fact that I omitted the keys
in the query grid be why I am not getting the records to appear?

SELECT tblCourses.Name, tblCourses.Street1, tblCourses.City,
tblCourses.State, tblCourses.Zip, tblCourses.Phone,
tblCourses.WebSite, tblCourses.Picture, tblCourses.[White In_Yardage],
tblCourses.[White Out_Yardage], tblCourses.[Red In_Yardage],
tblCourses.[Red Out_Yardage], tblCourses.[Gold In_Yardage],
tblCourses.[Gold Out_Yardage], tblCourses.[Blue In_Yardage],
tblCourses.[Blue Out_Yardage], tblCourses.[Black In_Yardage],
tblCourses.[Blace Out_Yardage], tblHoles.HoleNumber,
tblHoles.WhiteTees, tblHoles.RedTees, tblHoles.[Gold Tees],
tblHoles.[Blue Tees], tblHoles.[Black Tees], tblHoles.Par,
tblScores.Date, tblScores.HoleNum, tblScores.Green, tblScores.Fairway,
tblScores.Sand, tblScores.Water, tblScores.Lost, tblScores.Fringe,
tblScores.Approach, tblScores.Putts, tblScores.ParsMade
FROM (tblCourses INNER JOIN tblHoles ON tblCourses.CourseID =
tblHoles.CourseID) INNER JOIN tblScores ON tblHoles.HoleID =
tblScores.HoleID;

Thanks a bunch for your time and patience
Joanne
Joanne -

Did you change the table relationships since you and I chatted about your
form setups? The query that you have right now is relating Scores to
Courses. However, your earlier table design had Scores being related to
Holes, and Holes related to Courses.

Try this query:

SELECT tblCourses.Name, tblCourses.Street1, tblCourses.City,
tblCourses.State, tblCourses.Zip, tblCourses.Phone,
tblCourses.WebSite, tblCourses.Picture, tblCourses.In_Yardage,
tblCourses.Out_Yardage, tblHoles.HoleNumber, tblHoles.WhiteTees,
tblHoles.RedTees, tblHoles.[Gold Tees], tblHoles.[Blue Tees],
tblHoles.[Black Tees], tblHoles.Par, tblScores.Date,
tblScores.HoleNum, tblScores.Green, tblScores.Fairway, tblScores.Sand,
tblScores.Water, tblScores.Lost, tblScores.Fringe, tblScores.Approach,
tblScores.Putts, tblScores.ParsMade
FROM (tblScores INNER JOIN tblHoles ON tblScores.HoleID =
tblHoles.HoleID) INNER JOIN tblCourses ON tblCourses.CourseID =
tblHoles.CourseID;
Click to expand...
Click to expand...
 
J

Joanne

Ken
No records are returning when I run the query in the database window.

Thanks for the clarification regarding when to include the keys in a
query.

Joanne
 
K

Ken Snell [MVP]

The fact that the query isn't returning any records suggests that there are
no records in tblHoles and/or tblScores that link back to a record in
tblCourses. Check that these two children tables have values in their
foreign key fields that are found in the table that is the parent to the
one.

If that looks ok, then post examples of the data that you have in the three
tables.
 
J

Joanne

Ken
I must not have the foreign keys done correctly, because they show no
information in thier fields about the linking tables.
In tblHoles, the foreign key CourseID has a 0 in it's field throughout
the table, likewise, in tblScores, the foreign key HoleID has no value
at all in its field.

To create the foreign key, I simply added a field in tblHoles called
CourseID and used number/long integer as it's data type: in tblScores,
I added a field called HolesID and used number/long integer as it's
data type. I then created relationships tblCourses.CourseID related to
tblHoles.CourseID, and tblHoles.HoleID to tblScores.HoleID.

This must be where my big error is, because when I test build a query
on just a single table, it returns the records from that table. It is
when I use all 3 tables in a query that I get no returns.

What is it that I am doing wrong, please? It's a sure bet I do not
know enough about the use of foreign keys to be successful here.

Thanks for your time and efforts
Joanne
 
K

Ken Snell [MVP]

OK you're almost there.

Just establishing the relationships will not automatically put the value of
the primary key from the main table into the foreign key of the child table.
The zero that you see is the default value for the field (if you open the
table in design view, and look at that field, you'll see a zero in the
Default Value property -- that is the default behavior for ACCESS for long
integer fields).

The correct value that goes in the CourseID field in tblHoles would be the
real value of CourseID field in tblCourses, likely a 1 or 2 or such. If you
manually edit the record to put that value into the tblHoles table, the
linking will work as you expect. Do the same for the tblScores table's
records, using the real values from HoleID in tblHoles. Now your query
should return the expected records.

Now, I assume that you created the data per our earlier exchange about using
forms and subforms. What I'm guessing is that you did not set up the
LinkMasterFields and LinkChildFields properties of the subform control such
that the subform's data were "linked" to the main form's data.

Open in design view the form that has Courses on the main form and Holes on
the subform. Right-click on the top edge of the subform and select
Properties. Click on Data tab. What values do you see in the Link Master
Fields and Link Child Fields properties? Both should be CourseID. If not,
put that field name in both properties.

Similarly, in the form that relates Holes and Scores, the properties should
be HoleID.

When you use these linking properties, then the form will automatically put
the correct value into the foreign key fields for you when you enter new
data.
 
J

Joanne

Thanks for the clarification Ken.
I will manually insert the COurseID and the HoleID as you suggested.
Actually, I have not created any forms as yet because no returns would
get generated from the tables. I have used Datasheet mode to enter my
records directly into the tables.
Will follow your directions and get this closer to where I want it to
be and will post how I am doing (rest assured that I definitely will
post if I get stalled again - you guys are the greatest thing since
sliced bread, I swear!!)
Thank you, Thank you, Thank you
 
K

Ken Snell [MVP]

Good luck. Note that it's not recommended to do data entry directly via
tables, especially not in an application where people other than you will be
entering data. But, to get the data started, your approach is
understandable.
 
J

Joanne

I take note of your advice to not enter directly into the tables

I will create a data entry form now that I can populate my controls
with a correctly running query. But I was wondering if on my data
entry form do I need to put the foreign key control for data entry, or
can I simply make my foreign key be equal to the primary key in a
particular table even if I chose not to use a subform where I can use
the properties to link the parent and child.
I'm thinking in terms of something like making the tblHoles.CourseID =
tblCourses.CourseID so that whenever I pull the info about Course #1,
it brings me the hole info of the same course because
TblHoles.CourseID = TblCourses.CourseID.

I hope I am being clear enough here. It seems I used to do this, and I
think I also used to use another approach which used a query to look
at tblCourses.CourseID and made tblHoles.CourseID equal to it.

Thanks for all your help Ken - I am now able to move forward on this
little app. Don't want to spend all my free time doing this instead
of actually enjoying my new found sport.

Joanne
 
K

Ken Snell [MVP]

The typical manner of doing this is to put an unbound combo box in the
form's header section. Use that combo box to select the "foreign key field"
value that you want to use for the data entry.

Then you write code on the Current event of the form that writes the value
from the combobox value into the control that is bound to the foreign key
field in the form's RecordSource; and do this only for new records.

Private Sub Form_Current()
If Me.NewRecord = True Then
Me.ForeignKeyFieldName.Value = Me.ComboBocName.Value
End If
End Sub
 
K

Ken Snell [MVP]

You may find, depending upon how you want to do data entry, that you will
need to use a few more coded events in addition to the form's Current event.
The code I provided will put the value in only when the you first start
creating the new record. If the form opens to a new record, and you then
select the combo box value in the header, the code will not then put the
value into the foreign key field because the new record has already started.

In this case, you might use the AfterUpdate event of the combo box (if
you're using a single forms view for the form) as well:

Private Sub ComboBoxName_AfterUpdate()
If Len(Me.ForeignKeyFieldName.Value & "") = 0 Then
Me.ForeignKeyFieldName.Value = Me.ComboBoxName.Value
End If
End Sub

If you're using a continuous forms view for the form, then you may want to
use the form's BeforeUpdate event too:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Len(Me.ForeignKeyFieldName.Value & "") = 0 Then
Me.ForeignKeyFieldName.Value = Me.ComboBoxName.Value
End If
End Sub


You'll need to look at how you'll use the form to ensure that you get the
right result.

Additionally, you could move the combo box from the header into the detail
section, and then bind the combo box to the foreign key field; that would
allow you to select the foreign key value for each record. It's a bit more
tedious for data entry, but you could use the AfterUpdate event of the combo
box to set the Default Value for the next new record so that you wouldn't
have to repetitively select the same number over and over (this code will
cause a new record to "select" the last-selected value in the combo box for
you):

Private Sub ComboBoxName_AfterUpdate()
Me.ComboBoxName.DefaultValue = """" & _
Me.ComboBoxName.Value & """"
End Sub
 
Top