By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,225 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

Selective Unmatched Query

rcollins
100+
P: 234
** Edit ** Split from Rows & Columns

...
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 lastnamefirstname. 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
Feb 22 '08 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,768
I am not sure what I am tring to do myself half the time :)
So I took your advice and tried subreports...did 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 lastnamefirstname. 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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. 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.
Feb 23 '08 #2

rcollins
100+
P: 234
OK, Here we go

Client Contact table info

Expand|Select|Wrap|Line Numbers
  1. tblClientContactWithFunding
  2. ID; Number
  3. ClientID; Number
  4. Staff ;Text
  5. Date; Date/Time
  6. Department; Text
  7. Duration; Number
  8. Phase; Text
  9. Method; Text
  10. Support; Text
  11. Comments; Memo
  12. FundingID; Number
  13. FundingClientID; Number
  14. Funding; Text
  15. Current; Text
  16.  
Query for LastNameFirstName

Expand|Select|Wrap|Line Numbers
  1. qryClientName
  2. SELECT Trim([LastName] & ",  " & [FirstName]) AS ClientName, tblClient.ID, tblClient.EmploymentConsultant
  3. FROM tblClient
  4. ORDER BY Trim([LastName] & ",  " & [FirstName]);
  5.  
Table for Funding

Expand|Select|Wrap|Line Numbers
  1. tblFundingCost
  2. Funding; Text
  3. Amount; Currency
  4.  
Here is the query that I use for the report

Expand|Select|Wrap|Line Numbers
  1. qryClientDetail
  2. 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
  3. 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
  4. 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))
  5. ORDER BY qryClientName.ClientName;
  6.  
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.
Feb 25 '08 #3

NeoPa
Expert Mod 15k+
P: 31,768
For the moment I'm just subscribing properly to the thread.
I hope to look at it in more depth over the weekend.
Mar 1 '08 #4

NeoPa
Expert Mod 15k+
P: 31,768
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)?
Mar 1 '08 #5

rcollins
100+
P: 234
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
Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. WHERE (((tblClientContactWithFunding.Date)>=#2/1/2008#))
  4. ORDER BY qryClientName.ClientName;
  5.  
  6.  
Mar 11 '08 #6

NeoPa
Expert Mod 15k+
P: 31,768
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT tCCWF.ID,
  2.        tCCWF.ClientID,
  3.        tCCWF.Department,
  4.        tCCWF.Phase,
  5.        tCCWF.Duration,
  6.        tCCWF.Method,
  7.        tCCWF.Support,
  8.        tCCWF.Comments,
  9.        tCCWF.FundingID,
  10.        tCCWF.FundingClientID,
  11.        tCCWF.Current,
  12.        qCN.ID,
  13.        tC.CaseWorker,
  14.        tFC.Amount,
  15.        tCCWF.Staff,
  16.        tCCWF.Funding,
  17.        qCN.ClientName,
  18.        tCCWF.Date,
  19.        tCCWF.Duration*tFC.Amount AS Expr1
  20. FROM tblClient AS tC INNER JOIN
  21.      ((tblClientContactWithFunding AS tCCWF INNER JOIN
  22.      qryClientName AS qCN
  23.   ON tCCWF.ClientID = qCN.ID) INNER JOIN
  24.      tblFundingCost AS tFC
  25.   ON tCCWF.Funding = tFC.Funding)
  26.   ON tC.ID = tCCWF.ClientID
  27. WHERE (((tCCWF.Date)>=#2/1/2008#))
  28. 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 :
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)
tblClientContactWithFunding (Alias=tCCWF)
tblFundingCost (Alias=tFC)

My guess would be that tblClient is client-side and tblClientContactWithFunding 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.
Expand|Select|Wrap|Line Numbers
  1. ...
  2. FROM ((tblClient AS tC INNER JOIN
  3.      qryClientName AS qCN
  4.   ON tC.ID = qCN.ID) LEFT JOIN
  5.      tblClientContactWithFunding AS tCCWF 
  6.   ON tC.ID = tCCWF.ClientID) LEFT JOIN
  7.      tblFundingCost AS tFC
  8.   ON tCCWF.Funding = tFC.Funding
  9. 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.
Mar 11 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.