Can I check wether a ClientId is also in another list and get a true or false 
answer?

preferably I would build a query with columns about clients and their 
issues: clientId, clientissue1, Clientissue2. Other columns would include 
whether or not the client Id in the row is also to be found in another table, 
eg Clientscomplaints Clientsbills. 

This way I want to count "number of clients in the list having a certain 
issue, who are not featuring in one of the other lists" By using a Dcount 
function and setting criteria for featuring in one list to FALSE.

Mafukufuku wrote:
> Can I check wether a ClientId is also in another list and get a true
> or false answer?
>
> preferably I would build a query with columns about clients and their
> issues: clientId, clientissue1, Clientissue2. Other columns would
> include whether or not the client Id in the row is also to be found
> in another table, eg Clientscomplaints Clientsbills.
>
> This way I want to count "number of clients in the list having a
> certain issue, who are not featuring in one of the other lists" By
> using a Dcount function and setting criteria for featuring in one
> list to FALSE.

I'd need more details about the tables (structure and sample data) to be 
sure, but I strongly suspect that dcount is not necessary.

Try using the Unmatched Data query wizard to build a query. If that doesn't 
provide what you need, get back to us with more details.

-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 

If, as your post suggests, you have a table with columns clientissue1 and
clientissue2 then your first step should be to decompose this into a set of
normalized tables.  At present it is not in First Normal Form as it includes
more than one value of the same attribute in each row.  It should be
decompose like this:

Clients
….ClientID
….FirstName
….LastName
etc

Issues
….IssueID
….Issue

ClientIssues
….ClientID
….IssueID
< other columns for non-key attributes such as ClientIssueDate>

You can then count per client per issue and exclude clients in another table
like so:

SELECT Issues.IssueID, Issue, COUNT(*) As ClientCount
FROM ClientIssues INNER JOIN Issues
ON ClientIssues.IssueID = Issues.IssueID
AND NOT EXISTS
    (SELECT *
     FROM ClientsComplaints
     WHERE ClientsComplaints.ClientID = ClientIssues.ClientID)
GROUP BY Issues.IssueID, Issue;

Or you could use an outer join rather than a subquery and test for
ClientsComplaints.ClientID being Null.

Ken Sheridan
Stafford, England

Mafukufuku wrote:
>Can I check wether a ClientId is also in another list and get a true or false 
>answer?
>
>preferably I would build a query with columns about clients and their 
>issues: clientId, clientissue1, Clientissue2. Other columns would include 
>whether or not the client Id in the row is also to be found in another table, 
>eg Clientscomplaints Clientsbills. 
>
>This way I want to count "number of clients in the list having a certain 
>issue, who are not featuring in one of the other lists" By using a Dcount 
>function and setting criteria for featuring in one list to FALSE.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

Correction: AND should be WHERE:

SELECT Issues.IssueID, Issue, COUNT(*) As ClientCount
FROM ClientIssues INNER JOIN Issues
ON ClientIssues.IssueID = Issues.IssueID
WHERE NOT EXISTS
    (SELECT *
     FROM ClientsComplaints
     WHERE ClientsComplaints.ClientID = ClientIssues.ClientID)
GROUP BY Issues.IssueID, Issue;

Ken Sheridan
Stafford, England

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201002/1

I know my normalisation is off, I must correct that but am confronted with a 
done Dbase, as it is.  

Is there an expression/work around where I can have 1 client Id checked 
against a table with client Ids? 

More or less like so:  Clients have issues, selected in query1. Can I get a 
column in query 1 with true/false for whether this client Id is found in 
another selection-query or another table. 

Maybe like this:

Columns:

ClientId: number
Issue1: yes/no
Issue2: yes/no
features in query2: yes/no

Thanks


"Bob Barrows" wrote:

> Mafukufuku wrote:
> > Can I check wether a ClientId is also in another list and get a true
> > or false answer?
> >
> > preferably I would build a query with columns about clients and their
> > issues: clientId, clientissue1, Clientissue2. Other columns would
> > include whether or not the client Id in the row is also to be found
> > in another table, eg Clientscomplaints Clientsbills.
> >
> > This way I want to count "number of clients in the list having a
> > certain issue, who are not featuring in one of the other lists" By
> > using a Dcount function and setting criteria for featuring in one
> > list to FALSE.
> 
> I'd need more details about the tables (structure and sample data) to be 
> sure, but I strongly suspect that dcount is not necessary.
> 
> Try using the Unmatched Data query wizard to build a query. If that doesn't 
> provide what you need, get back to us with more details.
> 
> -- 
> Microsoft MVP - ASP/ASP.NET - 2004-2007
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM" 
> 
> 
> .
> 

Mafukufuku wrote:
> I know my normalisation is off, I must correct that but am confronted
> with a done Dbase, as it is.
>
> Is there an expression/work around where I can have 1 client Id
> checked against a table with client Ids?
>
> More or less like so:  Clients have issues, selected in query1. Can I
> get a column in query 1 with true/false for whether this client Id is
> found in another selection-query or another table.
>
> Maybe like this:
>
Again, I really can't suggest an answer without having some more details: 
table names, the relevant fields in those tables, a few rows of sample data 
from each table, and the results you desire from the query you want to 
build.

Again, I seriously doubt that dcount is necessary.


-- 
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM" 

On Sun, 28 Feb 2010 11:27:01 -0800, Mafukufuku
<Mafukufuku@discussions.microsoft.com> wrote:

>I know my normalisation is off, I must correct that but am confronted with a 
>done Dbase, as it is.  
>
>Is there an expression/work around where I can have 1 client Id checked 
>against a table with client Ids? 
>
>More or less like so:  Clients have issues, selected in query1. Can I get a 
>column in query 1 with true/false for whether this client Id is found in 
>another selection-query or another table. 

A couple of ways. One would be a calculated field:

FoundInBilling: IIF(IsNull(DLookUp("[ClientID]", "[ClientBilling]",
"[ClientID] = " & [ClientID]), False, True)

Another pure SQL solution would be:

FoundInBilling: EXISTS(SELECT ClientID FROM ClientBilling WHERE
ClientBilling.ClientID = Issuestablename.ClientID)

A third solution would be to LEFT JOIN ClientBilling to this table on
ClientID; this is particularly useful if you want to see other fields in the
billing table, not just the existance of a record. IT may interfere with
updatability though.
-- 

             John W. Vinson [MVP]

Actually I think using the pure SQL solution AND if you choose to show the 
FoundInBilling column will mean you cannot update the values in the table/query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John W. Vinson wrote:
> On Sun, 28 Feb 2010 11:27:01 -0800, Mafukufuku
> <Mafukufuku@discussions.microsoft.com> wrote:
> 
>> I know my normalisation is off, I must correct that but am confronted with a 
>> done Dbase, as it is.  
>>
>> Is there an expression/work around where I can have 1 client Id checked 
>> against a table with client Ids? 
>>
>> More or less like so:  Clients have issues, selected in query1. Can I get a 
>> column in query 1 with true/false for whether this client Id is found in 
>> another selection-query or another table. 
> 
> A couple of ways. One would be a calculated field:
> 
> FoundInBilling: IIF(IsNull(DLookUp("[ClientID]", "[ClientBilling]",
> "[ClientID] = " & [ClientID]), False, True)
> 
> Another pure SQL solution would be:
> 
> FoundInBilling: EXISTS(SELECT ClientID FROM ClientBilling WHERE
> ClientBilling.ClientID = Issuestablename.ClientID)
> 
> A third solution would be to LEFT JOIN ClientBilling to this table on
> ClientID; this is particularly useful if you want to see other fields in the
> billing table, not just the existance of a record. IT may interfere with
> updatability though.