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.

 

[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.