I am trying to create a query that will combine a number of fields in
a text string for output as a report on student progress - the SQL is
below:-
SELECT [Forename] & " " & [Progress comment] & " " & [Behaviour and
effort comment] & " " & [Forename] & " " & [Audience comment] &
Chr(10) & Chr(10) & [Personal pronoun query]!expr1 & " " &
[Organisation and planning comment] AS expr1
FROM Report_data, [Personal pronoun query];
The query works fine until I add the external query [Personal pronoun
query] that outputs a pronoun for the reports based upon sex (see
below):
SELECT Report_data.Sex, IIf(Report_data!Sex="male","He","She") AS
expr1
FROM Report_data;
The problem is that with the pronoun query added, the final query then
produces a record for each student based upon the number of records in
the pronoun query - i.e. if I have 4 students, then I will get 4 * 4
records in the final query.
I hope that this all makes sense.
jaredisnot,
Here is your query, straightened up.
SELECT [Forename] & " " &
[Progress comment] & " " &
[Behaviour and effort comment] & " " &
[Forename] & " " &
[Audience comment] & Chr(10) & Chr(10) &
[Personal pronoun query]!expr1 & " " &
[Organisation and planning comment] AS expr1
FROM Report_data
,[Personal pronoun query];
In the FROM clause:
FROM Report_data
,[Personal pronoun query];
Every row in Report_data is matched to every row in [Personal pronoun query] (rows *
rows).
The error that you have reported is exactly what the database has been instructed to do.
You probably need to INNER JOIN the tables, although on what column(s), I am not sure.
FROM Report_data
INNER JOIN
[Personal pronoun query]
ON Report_data.SomeColumn = [Personal pronoun query].SomeColumn.
I can't tell you what you should replace "SomeColumn" with. There does not appear to be
an obvious candidate column(s) in the query above.
What are joins?
Whenever you run a Query (SELECT, UPDATE, INSERT, DELETE, SELECT...INTO,
TRANSFORM...PIVOT) with more than two tables, you must instruct the database on what
columns are used to "join" the tables together.
INNER JOIN means that rows in both tables are returned where values match in both sets of
columns (one set from each table) that are used in the "join".
LEFT JOIN means that all rows in the left-hand table are returned, and when a row in the
right-hand table has no match for the specified "join" conditions, NULLS are returned in
any output columns of the right-hand table.
RIGHT JOIN means that all rows in the right-hand table are returned, and when a row in the
left-hand table has no match for the specified "join" conditions, NULLS are returned in
any output columns for the left-hand table.
"Left" and "right" refer to SQL code. It means to the left and right of the join keyword
of the SELECT statement (INNER JOIN, LEFT JOIN, RIGHT JOIN are all join keywords).
INNER JOIN Example:
SELECT L1.Key
,R1.Key
FROM LeftHandTable AS L1
INNER JOIN
RightHandTable AS R1
ON L1.Key = R1.Key
Rows in both tables are returned every time L1.Key and R1.Key values match. No other rows
are returned.
LEFT JOIN Example:
SELECT L1.Key
,R1.Key
FROM LeftHandTable AS L1
LEFT JOIN
RightHandTable AS R1
ON L1.Key = R1.Key
All rows in LeftHandTable are retained. Any time there is no R1.Key value for an L1.Key
value, the R1.Key output value is Null.
Left/Right-ness can better be demonstrated if written as shown below, but due to reasons
of horizontal crowding (which reduces visual clarity), it usually isn't written this way.
FROM LeftHandTable AS L1 LEFT JOIN RightHandTable AS R1
ON L1.Key = R1.Key
LeftHandTable is effectively to the "left" of the LEFT JOIN keyword.
RIGHT JOIN Example:
SELECT L1.Key
,R1.Key
FROM LeftHandTable AS L1
RIGHT JOIN
RightHandTable AS R1
ON L1.Key = R1.Key
All rows in RightHandTable are retained. Any time there is no L1.Key value for an R1.Key
value, the L1.Key output value is Null.
CARTESIAN JOIN:
If you leave these instructions (join names) out of a query, the database will join every
row in the first table with every row in the second table. The number of output rows
equals the number of rows in the first table multiplied by the number of rows in the
second table. This is called a Cartesian Join (the results are called a Cartesian
Product), and is not used except for very specific queries.
For two large tables, say with 100,000 rows each, the output would be 10,000,000,000 rows.
EXAMPLES IN PRACTICE:
Two example tables:
Items:
ItemID -- Primary Key
ItemName
ItemID, ItemName
1, Popcorn
2, Chips
3, Soda
4, Fillet Mignon
Prices:
PriceID -- Primary Key
ItemID --- Foreign Key (to ItemID in Items)
ItemPrice
StartDate
EndDate
(ItemID, ItemPrice, and StartDate would also be a unique index.)
PriceID, ItemID, ItemPrice, StartDate, EndDate
1, 1, 1.00, 06/01/2007, 06/08/2007
2, 2, 3.50, 06/01/2007, 06/08/2007
3, 3, 1.00, 06/01/2007, 06/08/2007
INNER JOIN Example:
SELECT I1.ItemName
,P1.ItemPrice
FROM Items AS I1
INNER JOIN
Prices AS P1
ON I1.ItemID = P1.ItemID
Returns
ItemName, Price
Popcorn, 1.00
Chips, 3.50
Soda, 1.00
Notice that Fillet Mignon does not appear. There is no = match (after the ON clause) for
ItemID between the two tables.
LEFT JOIN Example:
SELECT I1.ItemName
,P1.ItemPrice
FROM Items AS I1
LEFT JOIN
Prices AS P1
ON I1.ItemID = P1.ItemID
Returns
ItemName, Price
Popcorn, 1.00
Chips, 3.50
Soda, 1.00
Fillet Mingon, Null
Fillet Mignon does appear now, even though there is no match on ItemID between the two
tables. The output column (ItemPrice) from the "right hand" table (Prices) leaves a Null
behind.
Notes:
The above joins are conducted on table primary keys. Joins can be done on any column.
Usually they are done on columns that represent the same types of information, like
numbers, dates, words, etc. With conversion functions like CInt() and CStr(), numbers and
words can be compared in a join. Usuing functions in a join is generally not a first
choice as it will usually stop the use of any indexes, and this can slow down many
queries.
Joins are usually done on primary keys, or between primary keys and foreign keys, because
these columns represent either the final way of identifying the table rows, or are the
natural information connections between the tables.
Extras:
DDL SQL (These queries are usable to create the two tables above so you can run the last
two queries above on your own. You copy and paste these, one each, into the SQL View of
an MS Access Query, and then execute the query. You will need to manually enter in the
sample data noted above into the tables created this way.)
CREATE TABLE Items
(ItemID AUTOINCREMENT
,ItemName TEXT(255) NOT NULL
,CONSTRAINT pk_Items
PRIMARY KEY (ItemID)
)
CREATE TABLE Prices
(PriceID AUTOINCREMENT
,ItemID INTEGER NOT NULL
,ItemPrice CURRENCY NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME
,CONSTRAINT pk_Prices
PRIMARY KEY (PriceID)
,CONSTRAINT fk_Prices_Items
FOREIGN KEY (ItemID)
REFERENCES Items (ItemID)
,CONSTRAINT un_Prices_ItemId_ItemPrice_StartDate
UNIQUE (ItemID
,ItemPrice
,StartDate)
)
Sincerely,
Chris O.- Hide quoted text -
- Show quoted text -
