Checking if a record is also in another list

[Mafukufuku]

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.

 

[Bob Barrows]

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.

 

[KenSheridan via AccessMonster.com]

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

 

[KenSheridan via AccessMonster.com]

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

 

[Mafukufuku]

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]

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.

 

[John W. Vinson]

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 Spencer]

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

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.