Query one-to-many incorrect

  • Thread starter Thread starter Larry Marshall
  • Start date Start date
L

Larry Marshall

I can't figure out what I'm doing wrong with this query. I have a
property table joined to a mailings table, one-to-many. The
primary/foreign key is PropKey. The mailings table has a field,
MailDate.

I want to generate a record for each property which has its last (max)
MailDate greater than 21 days from today's date. With the query below,
it is not selecting the max MailDate, but the first.

SELECT Property.Propkey, Max(Mailings.MailDate) AS MaxOfMailDate
FROM Property LEFT JOIN Mailings ON Property.PropKey=Mailings.PropKey
WHERE (DateAdd("d",21,Mailings.MailDate)<Date())
GROUP BY Property.Propkey;

HELP! Larry
 
Larry-

You're testing each record's MailDate value instead of the Max value. Also,
I'm not sure why you're using a LEFT JOIN. A LEFT JOIN won't work if you
apply a predicate to the RIGHT table in the WHERE clause. Try this:

SELECT Property.Propkey, Max(Mailings.MailDate) AS MaxOfMailDate
FROM Property INNER JOIN Mailings ON Property.PropKey=Mailings.PropKey
GROUP BY Property.Propkey;
HAVING (DateAdd("d",21,Max(Mailings.MailDate))<Date())


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Try it like:

SELECT P.PropKey, M1.MostRecentMailing
FROM Property P
LEFT JOIN
(SELECT M.PropKey, MAX(M.MailDate) as MostRecentMailing
FROM Mailings M
GROUP BY M.PropKey) M1
ON P.PropKey=M1.PropKey
Where M1.MostRecentMailing IS NULL OR
DateAdd("d",21,M1.MostRecentMailing)<Date()

The subquery on this one gets the most recent mailing for each
property, then joins it to the Property table using a left join. I
used a left join here because you may have a new property that has
never received a mailing.

The where clause includes any properties that have never received a
mailing or where the most recent mailing they received is more than 21
days ago.

--
HTH

Dale Fye


I can't figure out what I'm doing wrong with this query. I have a
property table joined to a mailings table, one-to-many. The
primary/foreign key is PropKey. The mailings table has a field,
MailDate.

I want to generate a record for each property which has its last (max)
MailDate greater than 21 days from today's date. With the query below,
it is not selecting the max MailDate, but the first.

SELECT Property.Propkey, Max(Mailings.MailDate) AS MaxOfMailDate
FROM Property LEFT JOIN Mailings ON Property.PropKey=Mailings.PropKey
WHERE (DateAdd("d",21,Mailings.MailDate)<Date())
GROUP BY Property.Propkey;

HELP! Larry
 
Thank you both so much. It's been a struggle. I am amazed at the
wonderful help I've gotten here.
Larry
 
Back
Top