If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Checking if a record is also in another list
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. |
#2
|
|||
|
|||
Checking if a record is also in another list
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" |
#3
|
|||
|
|||
Checking if a record is also in another list
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/For...eries/201002/1 |
#4
|
|||
|
|||
Checking if a record is also in another list
Correction: AND should be WHE
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/For...eries/201002/1 |
#5
|
|||
|
|||
Checking if a record is also in another list
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" . |
#6
|
|||
|
|||
Checking if a record is also in another list
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" |
#7
|
|||
|
|||
Checking if a record is also in another list
On Sun, 28 Feb 2010 11:27:01 -0800, 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. 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] |
#8
|
|||
|
|||
Checking if a record is also in another list
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 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. |
Thread Tools | |
Display Modes | |
|
|