Access Structured Query Language (SQL) - Access Database Design and Programming - page 89

FROM PUBLISHERS INNER JOIN BOOKS
ON PUBLISHERS.PubID = BOOKS.PubID;

produces the table tblDISTINCT that is shown in Table 6-4.

Table 6-4. The tblDISTINCT table
PubName

Alpha Press
B ig House
Small House

Now consider what happens if the PUBLISHERS table is changed by adding a new
publisher with the same name as an existing publisher (but a different PubID and phone),
as we have done in Table 6-5. The previous DISTINCT statement will give the same
result table as before, thus leaving out the new publisher.

Table 6-5. The PUBLISHERS ( altered) table

PubID PubName PubPhone

1 Big House 123-456-7890

2 Alpha Press 999-999-9999

3 Small House 714-000-0000

4 Small House 555-123-1111

What is called for is a selection criterion that will return both publisher names simply
because they come from different rows of the PUBLISHERS table. This is the purpose of
DISTINCTROW. Thus, the statement:

SELECT DISTINCTROW PubName
FROM PUBLISHERS INNER JOIN BOOKS
ON PUBLISHERS.PubID = BOOKS.PubID;

produces the result table tblDISTINCTROW shown in Table 6-6 (note that we also had to
add a book to the BOOKS table, with PubID 4).

Table 6-6. The tblDISTINCTROW table
PubName

S mall House
B ig House
A lpha Press
S mall House

We can now describe how DISTINCTROW works. Consider the following SQL
skeleton:

SELECT DISTINCTROW ColumnsRequested
FROM TablesClause