Selective Unmatched Query

**NeoPa** · Feb 23 '08, 12:43 PM

Originally posted by rcollins

I am not sure what I am tring to do myself half the time :)
So I took your advice and tried subreports...di d the trick...the boss likes the way it turned out. Now for a more challenging one...
What I have is a table for all of the contacts with clients. I run reports weekly and monthly. For this report I have the client ID in the contact table which looks to a query where I have clientid and lastnamefirstna me. The report, of course, only shows names for those who have contacts for the duration of the report. What boss wants to see is the clients who are not having any contact for that period of time also. I have tried the unmatched query wizard which only gives me the clientids for clients who have never had any contacts. Is there a way for me to get all of the client names into the report, or even run a different report that gives the clientids of the no contact clients for a certain period? Let me know if you need more info, I will do my best

There is - and I do need more info.
I need the table meta-data as well as names and SQL of any queries you will need involved. Here is an example of how to post table MetaData :
Table Name=tblStudent

Code:

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Please use the coding I've used (It can be copied in the edit box of a reply to this post).
We may need to use subqueries here, but I'll know better when I have all the information.

**rcollins** · Feb 25 '08, 05:22 PM

OK, Here we go

Client Contact table info

Code:

tblClientContactWithFunding
ID; Number
ClientID; Number
Staff ;Text
Date; Date/Time
Department; Text
Duration; Number
Phase; Text
Method; Text
Support; Text
Comments; Memo
FundingID; Number
FundingClientID; Number
Funding; Text
Current; Text

Query for LastNameFirstNa me

Code:

qryClientName
SELECT Trim([LastName] & ",  " & [FirstName]) AS ClientName, tblClient.ID, tblClient.EmploymentConsultant
FROM tblClient
ORDER BY Trim([LastName] & ",  " & [FirstName]);

Table for Funding

Code:

tblFundingCost
Funding; Text
Amount; Currency

Here is the query that I use for the report

Code:

qryClientDetail
SELECT tblClientContactWithFunding.ID, tblClientContactWithFunding.ClientID, tblClientContactWithFunding.Department, tblClientContactWithFunding.Phase, tblClientContactWithFunding.Duration, tblClientContactWithFunding.Method, tblClientContactWithFunding.Support, tblClientContactWithFunding.Comments, tblClientContactWithFunding.FundingID, tblClientContactWithFunding.FundingClientID, tblClientContactWithFunding.Current, qryClientName.ID, tblClient.CaseWorker, tblFundingCost.Amount, tblClientContactWithFunding.Staff, tblClientContactWithFunding.Funding, qryClientName.ClientName, tblClientContactWithFunding.Date, [Duration]*[Amount] AS Expr1
FROM (tblClient INNER JOIN (tblClientContactWithFunding INNER JOIN qryClientName ON tblClientContactWithFunding.ClientID = qryClientName.ID) ON tblClient.ID = tblClientContactWithFunding.ClientID) INNER JOIN tblFundingCost ON tblClientContactWithFunding.Funding = tblFundingCost.Funding
WHERE (((tblClientContactWithFunding.Funding)=[Forms]![frmClientContactRptInfo]![Combo5]) AND ((qryClientName.ClientName)=[Forms]![frmClientContactRptInfo]![Combo1]) AND ((tblClientContactWithFunding.Date)>=[Forms]![frmClientContactRptInfo]![Text7] And (tblClientContactWithFunding.Date)<=[Forms]![frmClientContactRptInfo]![Text11])) OR ((([Forms]![frmClientContactRptInfo]![Combo5]) Is Null) AND (([Forms]![frmClientContactRptInfo]![Combo1]) Is Null) AND ((([tblClientContactWithFunding].[Date])>=[Forms]![frmClientContactRptInfo]![Text7]) Is Null) AND ((([tblClientContactWithFunding].[Date])<=[Forms]![frmClientContactRptInfo]![Text11]) Is Null)) OR (((qryClientName.ClientName)=[Forms]![frmClientContactRptInfo]![Combo1]) AND (([Forms]![frmClientContactRptInfo]![Combo5]) Is Null) AND ((([tblClientContactWithFunding].[Date])>=[Forms]![frmClientContactRptInfo]![Text7]) Is Null) AND ((([tblClientContactWithFunding].[Date])<=[Forms]![frmClientContactRptInfo]![Text11]) Is Null)) OR (((tblClientContactWithFunding.Funding)=[Forms]![frmClientContactRptInfo]![Combo5]) AND (([Forms]![frmClientContactRptInfo]![Combo1]) Is Null) AND ((([tblClientContactWithFunding].[Date])>=[Forms]![frmClientContactRptInfo]![Text7]) Is Null) AND ((([tblClientContactWithFunding].[Date])<=[Forms]![frmClientContactRptInfo]![Text11]) Is Null)) OR (((tblClientContactWithFunding.Date)>=[Forms]![frmClientContactRptInfo]![Text7] And (tblClientContactWithFunding.Date)<=[Forms]![frmClientContactRptInfo]![Text11]) AND (([Forms]![frmClientContactRptInfo]![Combo5]) Is Null) AND (([Forms]![frmClientContactRptInfo]![Combo1]) Is Null)) OR (((tblClientContactWithFunding.Funding)=[Forms]![frmClientContactRptInfo]![Combo5]) AND ((tblClientContactWithFunding.Date)>=[Forms]![frmClientContactRptInfo]![Text7] And (tblClientContactWithFunding.Date)<=[Forms]![frmClientContactRptInfo]![Text11]) AND (([Forms]![frmClientContactRptInfo]![Combo1]) Is Null)) OR (((qryClientName.ClientName)=[Forms]![frmClientContactRptInfo]![Combo1]) AND ((tblClientContactWithFunding.Date)>=[Forms]![frmClientContactRptInfo]![Text7] And (tblClientContactWithFunding.Date)<=[Forms]![frmClientContactRptInfo]![Text11]) AND (([Forms]![frmClientContactRptInfo]![Combo5]) Is Null)) OR (((tblClientContactWithFunding.Funding)=[Forms]![frmClientContactRptInfo]![Combo5]) AND ((qryClientName.ClientName)=[Forms]![frmClientContactRptInfo]![Combo1]) AND ((([tblClientContactWithFunding].[Date])>=[Forms]![frmClientContactRptInfo]![Text7]) Is Null) AND ((([tblClientContactWithFunding].[Date])<=[Forms]![frmClientContactRptInfo]![Text11]) Is Null))
ORDER BY qryClientName.ClientName;

Since I run multiple reports at the same time I have a form where we input the dates Staff Names and Funding.

I hope this is all you need, let me know if I missed something.

**NeoPa** · Mar 1 '08, 12:33 AM

For the moment I'm just subscribing properly to the thread.
I hope to look at it in more depth over the weekend.

**NeoPa** · Mar 1 '08, 10:52 PM

This looks as if it might be really complicated, but then again it may be deceptively simple. I can't really go through the SQL for the query as there's just too much there (especially in Access mode SQL - non-tidied version). When it gets that involved it's easier to see in the design view, and even then it can get very hard to follow unless you understand what it's about already.

However, if I understand you aright, you have basically some clients and some contacts. The clients link to the contacts via a client ID (of whatever form).

Currently, you have a LEFT JOIN from the contacts to the clients (All contacts - only matching clients) and you want to show ALL clients, regardless of which have any contacts, but include the contact info for those with any. If that's so then you simply build the query with a LEFT JOIN from the Clients to the Contacts instead of the current version which is basically vice-versa.

Does that help (even make sense)?

**rcollins** · Mar 11 '08, 02:51 PM

I went through the sql on the query and see that all I have is inner join. Do I need to change one of these to left join? Here is a smaller version of the query I am working with

Code:

SELECT tblClientContactWithFunding.ID, tblClientContactWithFunding.ClientID, tblClientContactWithFunding.Department, tblClientContactWithFunding.Phase, tblClientContactWithFunding.Duration, tblClientContactWithFunding.Method, tblClientContactWithFunding.Support, tblClientContactWithFunding.Comments, tblClientContactWithFunding.FundingID, tblClientContactWithFunding.FundingClientID, tblClientContactWithFunding.Current, qryClientName.ID, tblClient.CaseWorker, tblFundingCost.Amount, tblClientContactWithFunding.Staff, tblClientContactWithFunding.Funding, qryClientName.ClientName, tblClientContactWithFunding.Date, [Duration]*[Amount] AS Expr1
FROM tblClient INNER JOIN ((tblClientContactWithFunding INNER JOIN qryClientName ON tblClientContactWithFunding.ClientID = qryClientName.ID) INNER JOIN tblFundingCost ON tblClientContactWithFunding.Funding = tblFundingCost.Funding) ON tblClient.ID = tblClientContactWithFunding.ClientID
WHERE (((tblClientContactWithFunding.Date)>=#2/1/2008#))
ORDER BY qryClientName.ClientName;

**NeoPa** · Mar 11 '08, 08:44 PM

This is still quite involved, and the more involved it is, the more important that the SQL is displayed in a structured manner. I've reformatted the SQL you posted in such a way that it will work in exactly the same way, but tabulated it in such a way that it is much easier to read and understand.

Code:

SELECT tCCWF.ID,
       tCCWF.ClientID,
       tCCWF.Department,
       tCCWF.Phase,
       tCCWF.Duration,
       tCCWF.Method,
       tCCWF.Support,
       tCCWF.Comments,
       tCCWF.FundingID,
       tCCWF.FundingClientID,
       tCCWF.Current,
       qCN.ID,
       tC.CaseWorker,
       tFC.Amount,
       tCCWF.Staff,
       tCCWF.Funding,
       qCN.ClientName,
       tCCWF.Date,
       tCCWF.Duration*tFC.Amount AS Expr1
FROM tblClient AS tC INNER JOIN
     ((tblClientContactWithFunding AS tCCWF INNER JOIN
     qryClientName AS qCN
  ON tCCWF.ClientID = qCN.ID) INNER JOIN
     tblFundingCost AS tFC
  ON tCCWF.Funding = tFC.Funding)
  ON tC.ID = tCCWF.ClientID
WHERE (((tCCWF.Date)>=#2/1/2008#))
ORDER BY qCN.ClientName

One can now see quite easily (nothing's ever really easy when working with SQL except "SELECT * FROM [Table]") that all the JOINs are INNER JOINs.
Assuming that my assertion in post #5 is correct :

Originally posted by NeoPa

Currently, you have a LEFT JOIN from the contacts to the clients (All contacts - only matching clients) and you want to show ALL clients, regardless of which have any contacts, but include the contact info for those with any. If that's so then you simply build the query with a LEFT JOIN from the Clients to the Contacts instead of the current version which is basically vice-versa.

...then this still needs a bit of fiddling with.
We have Record Sources in the FROM clause :
tblClient (Alias=tC)
qryClientName (Alias=qCN)
tblClientContac tWithFunding (Alias=tCCWF)
tblFundingCost (Alias=tFC)

My guess would be that tblClient is client-side and tblClientContac tWithFunding is contact side. The others are less obvious. qryClientName is probably client-side too, and tblFundingCost appears to be contact-side.

On the assumption above, I will set out your FROM clause (from FROM until just before WHERE) for you.

Code:

...
FROM ((tblClient AS tC INNER JOIN
     qryClientName AS qCN
  ON tC.ID = qCN.ID) LEFT JOIN
     tblClientContactWithFunding AS tCCWF 
  ON tC.ID = tCCWF.ClientID) LEFT JOIN
     tblFundingCost AS tFC
  ON tCCWF.Funding = tFC.Funding
WHERE ...

Ideally we would want to group the two client-side tables together with an INNER JOIN as well as (separately) the two contact-side tables and then join the two groupings together with a LEFT JOIN. Unfortunately Access can't handle INNER JOINs to the logical right of a LEFT JOIN.