Obtaining single records

G

George R

I have two tables with a many-to-many relationship, and a junction table.
They are tblOwners, tblLots, and tblJunction. I would like to print one
mailing lable for each owner who purchased at least one lot in 2003.
(Purchase date is a field in tblLots.) How can I get such a list without
creating duplicates? (Access 2003)
Thanks for your assistance.
 
J

John Viescas

SELECT tblOwners.*
FROM tblOwners
WHERE tblOwners.OwnerID IN
(SELECT OwnerID
FROM tblJunction
INNER JOIN tblLots
ON tblJunction.LotID = tblLots.LotID
WHERE Year(tblLots.PurchaseDate) = 2003)

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
M

Marshall Barton

George R said:
I have two tables with a many-to-many relationship, and a junction table.
They are tblOwners, tblLots, and tblJunction. I would like to print one
mailing lable for each owner who purchased at least one lot in 2003.
(Purchase date is a field in tblLots.) How can I get such a list without
creating duplicates? (Access 2003)
Click to expand...


I think this is what you're asking for:

SELECT tblOwners.ownername, tblOwners.address
FROM tblOwners INNER JOIN (tblJunction INNER JOIN
tblJunction
ON tblJunction.lotID = tblJunction.lotID)
ON tblOwners.= tblJunction.ownerID
WHERE Year(tblLots.PurchaseDate) = 2003
GROUP BY tblOwners.ownername, tblOwners.address
 
G

George R

Thank you John . You have again been a great help.
Thanks to you as well, Marsh
 
M

Marshall Barton

John said:
SELECT tblOwners.*
FROM tblOwners
WHERE tblOwners.OwnerID IN
(SELECT OwnerID
FROM tblJunction
INNER JOIN tblLots
ON tblJunction.LotID = tblLots.LotID
WHERE Year(tblLots.PurchaseDate) = 2003)
Click to expand...


John, it's interesting that you chose a subquery approach.
Aside from my typo in the nested Join, I believe the nested
joins and the subquery will produce the same results. I may
be on thin ice here, but I always thought a Join would be
significantly faster than a subquery? Or does the query
planner rearrange it all into the same internal operations?
 
J

John Viescas

My query is updatable, yours is not (because of the Group By). Mine will
run faster restated like the following, but it depends on the dorky
DISTINCTROW keyword:

SELECT DISTINCTROW tblOwners.*
FROM tblOwners
INNER JOIN
(SELECT OwnerID
FROM tblJunction
INNER JOIN tblLots
ON tblJunction.LotID = tblLots.LotID
WHERE Year(tblLots.PurchaseDate) = 2003) As T2003
ON tblOwners.OwnerID = T2003.OwnerID

I try to post SQL that will a) be updatable and b) will also work in SQL
Server or Oracle or whathaveyou. (ANSI-standard syntax) The above will work
in Access only (A2000 and later).

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
M

Marshall Barton

I knew there had to be a good reason ;-)
All good things to keep in mind.

Thanks for the followup, John.
 
Top