Robert:
Firstly the T1 is an alias for the table. This is used to distinguish
different instances of the same table in a query. I'm not sure why I used it
here as I don't think its necessary in fact. Where it is necessary is when
you join a table to itself, e.g. a Employees table to show each employee's
line manager:
SELECT E1.FirstName & " " & E1.LastName As Employee,
E2.FirstName & " " & E2.LastName As Manager
FROM Employees As E1 INNER JOIN Employees AS E2
ON E2.EmployeeID = E1.ManagerID;
Or when a subquery is correlated with an outer query, e.g. to get the latest
transaction per client:
SELECT ClientID, TransactionDate, Amount
FROM Transactions AS T1
WHERE TransactionDate =
(SELECT MAX(TransactionDate)
FROM Transactions AS T2
WHERE T2.ClientID = T1.ClientID);
Getting back to your database, you can return the average 3 putts per round
as a single row simply by omitting the outer query from your first query,
i.e. the first subquery now becomes the outer query:
SELECT COUNT(*)/
(SELECT COUNT(*)
FROM
(SELECT DISTINCT [DateOfRound]
FROM [Strokes]))
AS Avg3Puts
FROM [Strokes]
WHERE [Number of Putts] > 2;
Because this query returns only one row, and your first query also returns
only one row you can join them very easily by not joining them! To explain
that apparent contradiction, if you include two tables (and a query's result
set is a table, just not a 'base table') in a query without joining them you
get what's known as the Cartesian product of the two tables, which simply
means that each row in one is joined to each row in the other (so called
after René Descartes, the 17th century French philosopher and scientist – in
mathematics a Cartesian coordinate is each of a set of coordinates describing
the position of a point in relation to a set of intersecting straight axes).
So if each table had 1,000 rows the result would be 1,000,000 rows. In your
case, as each query's result table has one row the result is of course one
row, so all you need to do is put both queries in the FROM clause of another
query but without any JOIN clause:
SELECT GreenPct, FairwayPct, AvgPutts, Avg3Puts
FROM [First Query], [Second Query];
It could in fact also be done in one step by putting the second query as a
subquery in the first query's SELECT clause.
The Cartesian product of two (or more) tables is used quite often in
queries, usually to return all permutations and then throw out the irrelevant
ones. Here's an example for returning room vacancies:
PARAMETERS [Enter Start Date:] DateTime, [Enter End Date:] DateTime;
SELECT Rooms.RoomNumber, C1.CalDate
FROM Rooms, Calendar AS C1
WHERE C1.CalDate BETWEEN [Enter Start Date:] AND [Enter End Date:]
AND NOT EXISTS
(SELECT *
FROM RoomOccupations, Calendar AS C2
WHERE C2.CalDate >= EntryDate
AND (C2.CalDate <= DepartureDate OR DepartureDate IS NULL)
AND C2.CalDate = C1.CalDate
AND RoomOccupations.RoomNumber = Rooms.RoomNumber);
As you see this starts by returning the Cartesian product of a rooms and
'calendar' table (simply a table of all dates over a period), i.e. every
possible room/date combination. This is then restricted to a date range
entered by the user as parameters at runtime. The subquery, which again
starts by returning the Cartesian product of two tables, identifies when each
room IS occupied, and by applying the NOT EXISTS predicate to this the outer
query returns one row for every room/date when the room is UNOCCUPIED. Note
how the two instances of the Calendar table are distinguished by the aliases
C1 and C2, so that the subquery can be correlated with the outer query on the
date and room number.
Ken Sheridan
Stafford, England
Robert F. said:
Okay, in the table I renamed ‘Date’ to ‘DateOfRound’ like you suggested and
plugged my table names into your expression. I then plugged it into the SQL
window instead of a blank Field cell in the design grid (I didn’t know about
that). It returned the correct answers but in a way I didn’t anticipate. The
Answer based on 3 rounds of data is 2. So it returned the following:
8/28/08 : 2 This round had 2 “3 puttsâ€
9/3/08 : 2 This round had 1 “3 puttâ€
9/28/08 : 2 This round had 3 “3 puttsâ€
The answer is correct but it is not displayed the way I anticipated. I just
need it to tell me that my average ‘3 putts’ per round is “2.†How can I make
it say that?
My goal is to have one query named Statistics that returns four pieces of
information:
• Percent of Greens In Regulation,
• Percent of Fairways In Regulation,
• Average Putts, and
• Average 3 Putts Per Round.
My first query provides the first 3 pieces of information and my second
provides the fourth. How can I combine them into one query? I tried to paste
the second to the end of the first but it didn’t work. Here are the two query
strings
FIRST QUERY:
SELECT -Sum([Green In Reg])/Count(*) AS GreenPct, -Sum([Fairway In
Reg])/Count(*) AS FairwayPct, Sum([Number of Putts])/Count(*) AS AvgPutts
FROM Strokes;
SECOND QUERY:
SELECT (SELECT COUNT(*)
FROM [Strokes]
WHERE [Number of Putts] > 2)/(SELECT COUNT(*)
FROM
(SELECT DISTINCT [DateOfRound]
FROM [Strokes])) AS Avg3Puts
FROM Strokes AS T1
WHERE (((T1.[Number of Putts])>2))
GROUP BY T1.DateOfRound;
PS. I’m learning a ton here and really appreciate your help. For my benefit,
what does T1 mean in the second query?
--
Respectfully,
Robert F.
:
Robert:
The SQL is the whole query, not a subquery. Open the query designer, switch
to SQL view and paste in the SQL in place of what's there already, then
change the references to [YourTable] to the actual table name.
Ken Sheridan
Stafford, England
:
Thanks Ken,
I did a cut and paste and placed the expression in a blank Field cell and
this is the error I’m getting:
The syntax of the subquery in this expression is incorrect. Check the
subquery’s syntax and enclose the subquery in parentheses.
I’m new to Access, which part is the subquery that needs placed in
parentheses?
--
Respectfully,
Robert F.
:
Try this:
SELECT [Date], COUNT(*) AS 3Puts,
(SELECT COUNT(*)
FROM [YourTable]
WHERE [Number of Putts] = 3)/
(SELECT COUNT(*)
FROM
(SELECT DISTINCT [Date]
FROM [YourTable]))
AS Avg3Puts
FROM [YourTable] As T1
WHERE [Number of Putts] = 3
GROUP BY [Date];
I'd advise against using date as a column name, however, as it’s the name of
a built in function. Something like DateOfRound would be better.
Ken Sheridan
Stafford, England
:
This is a follow on question to my post on 9/22/08 “Query on checkboxes.â€
First I really appreciate those who are on here helping people like me learn
MS Access and find solutions to problems.
In my golf database, I have a table with the following columns:
Name/Date/Course/Hole/Tees/Fairway in Reg/Green in Reg/Strokes/Number of
Putts.
Okay, for my question: I want to run a query to count the number of 3 putts
by date and then average those counts to produce my ‘average 3 putts per
round.’ How do I set that up? Thanks in advance.
