Left Joins and Group By's?

[Bobadopolis]

Hi,

I have two tables:

tblListeners
-----------
ID (PK)
FirstName
LastName
etc

tblReference
------------
ID (PK)
ListenerID
ReferenceTypeID (FK)
ReferenceDate
etc

(Btw I am aware of the unconventional naming of the PKs but I'm running the B/E on SharePoint so I have no choice!)

So listeners have to periodically do a reference. What i want is a query that tracks if listeners either have not done a reference EVER or haven't done one for a while, but broken down by the FK in tblReference.

Here is my reasonably simple SQL so far:

SELECT tblListeners.ID AS ListenerID, tblListeners.FirstName, tblListeners.LastName, tblReference.ReferenceTypeID, tblReference.ReferenceDate
FROM tblListeners LEFT JOIN tblReference ON tblListeners.ID = tblReference.ListenerID
WHERE (((tblReference.ListenerID) Is Null) AND ((tblReference.ReferenceDate) Is Null)) OR (((tblReference.ReferenceDate)<DateAdd("m",-6,Date())))
ORDER BY tblListeners.FirstName, tblListeners.LastName;

In the current query the results ignore the FK so the so a listener will be missing if they have done one type of reference - i want them to be there (or not be there) for each type of reference. Hope that makes sense!

Now i know people may suggest a crosstab for this but: a) I don't get on with them and wouldn't know how to implement it and b) this will need to be in a report and I don't want to venture down the 'dynamic crosstab report' path!

Any ideas?

Cheers,

Bobadopolis

 

[KeithG]

I would try using a right join because you want to include all listeners from tblListens, correct?

 

[Bobadopolis]

Hi,

Thanks for the reply.

Yes that is what I want but I thought the Left Join already achieved that - it says in the query design view "Include ALL records from 'tblListeners' and only those records form 'tblReference' where the joined fields are equal".

What about the grouping though - any thoughts?

Cheers,

Bobadopolis

 

[Bobadopolis]

Any thoughts, anyone?

 

[KeithG]

A left join will include all records from the table in the left side of the join. A right join will include all records from the right side of the join. You have tblListeners on the right side.

 

[Bobadopolis]

Hi KeithG,

I don't mean to be argumentative at all (I appreciate your help!) but tblListeners is definitely on the Left (see attached)!

The main trouble with this is that I want to show data that 'isn't there' for two possibilities in tblReference. A listener can either do a 'Type A' or 'Type B' reference. I want the query to show if they haven't done a particular type or if they haven't done a particular type for a while.

Using the structure currently in place they won't show if they haven't done either 'Type A' or 'Type B'.

Hope that makes sense!

Thanks,

Bobadopolis

 

[Bobadopolis]

Anyone? Any ideas?

 

[Bobadopolis]

Bump......