I have two tables in my law office db.(for purposes of this question). The first is a clientinfo table which is populated by a clientlinfo form. Then later on I have a table which stores the info on the defendants in each legal case again populated by a form populated by a form. I need to be able to run a search to see if the name being entered as a defendant already exists in the clientinfo table and ifso to popup a msgbox with a warning that there is a conflict because we can't sue someone who is already a client in another case. Part of the problem is that client name in the clientinfo table consists of 3 fields: FName, MI, and LName whereas Defendants name in defendants table consists of only one field: defname. I suspect I will have to create fields as well in my defendants table as well but I'm not sure. Can someone tell me if I need to add the additional 2 fields to my second table and how I write a a search string to tell me if the combination of each of the 3 fields = each other?
A search Question
- Thread starter Gilrucht
- Start date
You do need to add those 2 additional fields into the defendants table. Just checking the defendants against clients is not foolproof as 2 people can have the same name. You really need to be checking clients against defendants on a 'case' basis.
I did a demo for you.
- Dave
I did a demo for you.
- Dave
Last edited:
Meltdown, 2 questions. First, why are you using unbound textboxes in the defendants form instead of the fields? Won't this prevent me from saving saving me the defendants in the underlying table?
More importantly, I can't seem to change the names you used in your code to the names of my tables. My table names are tbl_clientinfo and tbl_defendants. I fixed the query but can't get the table names right.
I know that not all the "clients" and "defendants" refer to your tables (at least I don't think they do) but I haven't been able to get the right combination. Can you tell me which of the "clients" and "defendants" in your code below have to be changed to "tbl_clientinfo" and "tbl_defendants"
Private Sub Command8_Click()
Dim tbl_Defendant As String
Dim FirstName As String
Dim MiddleName As String
Dim LastName As String
Dim tbl_Client As Variant
FirstName = Trim(Me!txtFirstName)
MiddleName = Trim(Me!txtMiddleName)
LastName = Trim(Me!txtLastName)
Defendant = FirstName & " " & MiddleName & " " & LastName
Client = DLookup("[Client]", "qryClients", "[Client] = '" & Defendant & "'")
If Defendant = Client Then
MsgBox "That Defendant already exists as a Client"
Else
MsgBox "Defendant is not a Client"
End If
End Sub
More importantly, I can't seem to change the names you used in your code to the names of my tables. My table names are tbl_clientinfo and tbl_defendants. I fixed the query but can't get the table names right.
I know that not all the "clients" and "defendants" refer to your tables (at least I don't think they do) but I haven't been able to get the right combination. Can you tell me which of the "clients" and "defendants" in your code below have to be changed to "tbl_clientinfo" and "tbl_defendants"
Private Sub Command8_Click()
Dim tbl_Defendant As String
Dim FirstName As String
Dim MiddleName As String
Dim LastName As String
Dim tbl_Client As Variant
FirstName = Trim(Me!txtFirstName)
MiddleName = Trim(Me!txtMiddleName)
LastName = Trim(Me!txtLastName)
Defendant = FirstName & " " & MiddleName & " " & LastName
Client = DLookup("[Client]", "qryClients", "[Client] = '" & Defendant & "'")
If Defendant = Client Then
MsgBox "That Defendant already exists as a Client"
Else
MsgBox "Defendant is not a Client"
End If
End Sub
Hi Gilrucht, I took a few shortcuts that's why the form was unbound. It doesn't make any difference to my example,...for the sake of 'completeness' I've now bound the form.
The tables names are irrelevent to my code as I don't reference them directly, just the query 'qryClients'.
Anyway, I've changed the names of the tables as requested.
- Dave
The tables names are irrelevent to my code as I don't reference them directly, just the query 'qryClients'.
Anyway, I've changed the names of the tables as requested.
- Dave
I just noticed you changed
Dim Defendant As String
Dim Client As Variant
to....
Dim tbl_Defendant As String
Dim tbl_Client As Variant
there is no need to do that, you've just made up two new variables that I don't reference, you should undo those changes
Dim Defendant As String
Dim Client As Variant
to....
Dim tbl_Defendant As String
Dim tbl_Client As Variant
there is no need to do that, you've just made up two new variables that I don't reference, you should undo those changes
I did. I used your code as you wrote it in the new example you sent me. Its working perfectly. Thank you very much. I noticed one difference betwween the two that I'm wondering might explain why I couldn't get it working. In the first one you included 1 query. In the second, you included 2 queries. Maybe thats the difference. Maybe I needed the 2 queries.
mresann
Registered User.
- Local time
- Today, 08:47
- Joined
- Jan 11, 2005
- Messages
- 357
Gilrucht said:
Two points:
1. Defendant = FirstName & " " & MiddleName & " " & LastName
If the record does not include a middle name, then the phrase will include an extra space between the first and the last names. Better would be:
.
Code:
Defendant = FirstName & " " & MiddleName & _
Iif (Len(MiddleName > 0, " ", "") & LastNameThis will prevent the second space from occuring if the middle name is missing.
2. Client = DLookup("[Client]", "qryClients", "[Client] = '" & Defendant & "'")
I am not at all comfortable comparing cocatenated fields with other cocatenated fields, because you have to account for spaces and other possible unforseen text errors. In addition, a client may have given his name as "John M. Doe", while as a defendent he may have given his name as "John Michael Doe", which of course don't match.
I had not opened up the queries, but thought I'd give you a heads up to potential problems. The best solution, of course, is to create a name table with unique name ID's, which can be related with Name ID's in the Client and Defendent tables, and avoid any possible cocatenated name field confusion.
I see the problem. I don't quite follow your solution. How would the names table with a unique id work. One thing you should know is that I am not using the traditional autonumber PK as my unique id in each table. I reset the autonumber in my clientinfo table to begin with 200501. The next table is a case table which assigns a case number manually . These 2 two fields(clientid andid then become the unique id for every other table throut the rest of the db. so the unique id for clientname is clientid whereas the unique id for defendants is the clientid field AND the caseid field. I'm not sure if that makes a difference to what you are proposing as a solution. I'm thinking an even better solution might be to just make the comparisons between first and last names.
I'm going to pop in here with a couple of points.
1) When designing tables its best to create fields that hold each individual bit of info you want to capture. its a lot easier to combine fields then it is to parse them out. Generally I use 5 fields for names: Salutation (Mr, Mrs, etc.) First, Middle (either name or initial), Last and Suffix (Sr, Jr, etc.)
2) There is no way that any comparison of names will be 100% accurate. The best you can do is find like or similar names that will then need to be researched further to confirm whether they were a client or not. Because of that you are better off doing a broader search to get a list for checking. The way I would do it is to create a query (qryClientSearch) of your clients like this:
SELECT ClientID, [Lastname] & Left([Firstname],1) AS Searchname FROM tblClients ORDER BY Lastname, Firstname;
I would then use a button on your Defendent entry form to populate a hidden control and run another query using qryClientSearch.
Me!txtDefSearchName = Me!txtLastname] & Left(Me!Firstname,1)
You would then have qryDefClientSearch like this:
SELECT ClientID, Searchname FROM qryClientSearch WHERE [Searchname] = Forms!formname!txtDefSearchname;
This would give a list of clients to check. You can even open a form that uses a join between the client table and qryDefClientSearch so the user can research the potential matches.
3) We have discussed before your using a compound primary key. You will continue to run up against problems that you will have to jury rig workarounds as long as you continue to use it. Its just not necessary and is not good database design, IMHO.
1) When designing tables its best to create fields that hold each individual bit of info you want to capture. its a lot easier to combine fields then it is to parse them out. Generally I use 5 fields for names: Salutation (Mr, Mrs, etc.) First, Middle (either name or initial), Last and Suffix (Sr, Jr, etc.)
2) There is no way that any comparison of names will be 100% accurate. The best you can do is find like or similar names that will then need to be researched further to confirm whether they were a client or not. Because of that you are better off doing a broader search to get a list for checking. The way I would do it is to create a query (qryClientSearch) of your clients like this:
SELECT ClientID, [Lastname] & Left([Firstname],1) AS Searchname FROM tblClients ORDER BY Lastname, Firstname;
I would then use a button on your Defendent entry form to populate a hidden control and run another query using qryClientSearch.
Me!txtDefSearchName = Me!txtLastname] & Left(Me!Firstname,1)
You would then have qryDefClientSearch like this:
SELECT ClientID, Searchname FROM qryClientSearch WHERE [Searchname] = Forms!formname!txtDefSearchname;
This would give a list of clients to check. You can even open a form that uses a join between the client table and qryDefClientSearch so the user can research the potential matches.
3) We have discussed before your using a compound primary key. You will continue to run up against problems that you will have to jury rig workarounds as long as you continue to use it. Its just not necessary and is not good database design, IMHO.
Scott, I know your position on composite keys. I just have a hard time accepting it. Not another person on the forum has told me it is a mistake. There are threads and articles on the forum on how to do it. When I searched the web there are numerous tutorials and sites explaining how to use composite keys. I realize I am inexperienced but as far as I can tell it seems to be a commonly accepted practice. I am just too far along to redo a couple of hundred tables. As far as the specific issue goes aren't we saying the same thing? Meltdown gave me a solution which is working. Its been pointed out there is a potential problem with middle names because some may use initial one time and full name the next resulting in no match when there should be. Isn't it easier to take the middle name out of a solution I have already written and have working than to start over from scratch with a completly different solution. I am going to end up with a "more general list" because of the lack of middle names which is exactly what you reccomend and I agree you I am better off. Even with a specific list we would cross-check to confirm. Unless Im missing something, it just seems to me that sticking with Meltdown's solution minus the reference to middle names is the easiest solution since I already have his code written and working.
I've seen Pat Hartman speak out against composite keys. I know lots of other developers that I respect that would not use composite keys except as a last resort. In the argument of surrogate vs natural keys, the vast majority that I have seen goes for surrogate keys especially for Access. Since a composite key would, by its nature, be a natural key, that is another strike against composite keys. Just because there are instructions to do something doesn't mean that its the best practice. Whether using them is a "commonly accepted practice" is open to debate.
But the real key is the what you need to go thru to make use of a composite key. It requires more work to use a composite key and more of a hassle. You will often come up with issues where you have to create a work around to deal with the composite key. Its just not necessary, especially in this case. Case and client are separate entities. Client is a FOREIGN KEY to a case. You can use client to create a composite identifier and that's no problem but its unecessary to create a composite key.
As for the specific situation here. The problem with Meltdown's code is that it will only return the first match of a name if any. The method I gave you will return ALL potential matches for research. And that's what you need, since name is not the best way to make sure of a match. if you look at my solution not only do I take the middle name/initial out of the equation, but I take the first name out as well. I'm only using the first initial and the last name to match up on. In your situation, you want to see any possible match, you want to see more potential conflicts to insure you don't enter into a conflict.
As I said, the way I would do this is join the query that lists potential conflicts with the client table and use that as the recordsource of a form where the user could cycle thru the potential matches to see if they could be eliminated.
But the real key is the what you need to go thru to make use of a composite key. It requires more work to use a composite key and more of a hassle. You will often come up with issues where you have to create a work around to deal with the composite key. Its just not necessary, especially in this case. Case and client are separate entities. Client is a FOREIGN KEY to a case. You can use client to create a composite identifier and that's no problem but its unecessary to create a composite key.
As for the specific situation here. The problem with Meltdown's code is that it will only return the first match of a name if any. The method I gave you will return ALL potential matches for research. And that's what you need, since name is not the best way to make sure of a match. if you look at my solution not only do I take the middle name/initial out of the equation, but I take the first name out as well. I'm only using the first initial and the last name to match up on. In your situation, you want to see any possible match, you want to see more potential conflicts to insure you don't enter into a conflict.
As I said, the way I would do this is join the query that lists potential conflicts with the client table and use that as the recordsource of a form where the user could cycle thru the potential matches to see if they could be eliminated.
Not to beat a dead horse but here is what I am talking about.
http://www.access-programmers.co.uk/forums/showthread.php?t=30370&highlight=composite+keys
This is my situation. I had a natural number system already in existence outside of the db. This thread reccomends that it is preferable to use an existing natural number system to an autonumber. The exact examples of good natural ids, "student ids", employee ids" are exactly what I have, "client ids". Pat says she only uses autonumbers if she doesnt already have a unique id or have to combine too many fields to make one. In my case, I have a unique id already, the composite client and case id. One poster even reccomended going a little out of your way to use maningful PKs where reasonable to do so. Thats the problem I'm having. Everythimg I'm reading other than what you are posting seems to reccomend natural keys over autonumbers in my situation.
http://www.access-programmers.co.uk/forums/showthread.php?t=30370&highlight=composite+keys
This is my situation. I had a natural number system already in existence outside of the db. This thread reccomends that it is preferable to use an existing natural number system to an autonumber. The exact examples of good natural ids, "student ids", employee ids" are exactly what I have, "client ids". Pat says she only uses autonumbers if she doesnt already have a unique id or have to combine too many fields to make one. In my case, I have a unique id already, the composite client and case id. One poster even reccomended going a little out of your way to use maningful PKs where reasonable to do so. Thats the problem I'm having. Everythimg I'm reading other than what you are posting seems to reccomend natural keys over autonumbers in my situation.
Gil,
Ok, I looked at that thread and it doesn't conflict with what I've been saying. I'll give you another discussion on natural vs surrogate keys:
http://www.utteraccess.com/forums/showflat.php?&Number=637301
The key is the definition of a primary key: A unique, unchanging value that identifies a single record. The problem with certain or natural keys is that they can be subject to change. SSNs are reused for example. Many people subscribe to the idea that a PK should be hidden from the user and only used internally by the system. I don't go quite that far. But I do fall into the camp that favors using a surrogate key rather than a natural one. The only time I would use a natural key is if it were supplied by an external system that controlled the uniqueness.
There is also another factor here that I don't think has been mentioned and that's an issue of performance. Using a numeric key will provide better performance then a text key. And using a single key will provide better performance then a composite one.
I can't find the specific quote, but what I recall Pat saying was not to use composite keys if you have child tables. And that IS your situation. Another point, while client ID may seem like a natural key it depends on how its generated. If its generated by the system then its not a natural key. You also keep missing my point that Case is a separate entity from client. You are generating a a composite key by concatenating the client ID with a sequential number that is sequential only within the client. This is just the type of situation that Pat decries in that thread. You keep looking only at points that you think support your position and not those that talk against it.
You want to see other people support my position? Post the question over at utteraccess.com. I doubt if anyone there will support using a composite key for your application.
Ok, I looked at that thread and it doesn't conflict with what I've been saying. I'll give you another discussion on natural vs surrogate keys:
http://www.utteraccess.com/forums/showflat.php?&Number=637301
The key is the definition of a primary key: A unique, unchanging value that identifies a single record. The problem with certain or natural keys is that they can be subject to change. SSNs are reused for example. Many people subscribe to the idea that a PK should be hidden from the user and only used internally by the system. I don't go quite that far. But I do fall into the camp that favors using a surrogate key rather than a natural one. The only time I would use a natural key is if it were supplied by an external system that controlled the uniqueness.
There is also another factor here that I don't think has been mentioned and that's an issue of performance. Using a numeric key will provide better performance then a text key. And using a single key will provide better performance then a composite one.
I can't find the specific quote, but what I recall Pat saying was not to use composite keys if you have child tables. And that IS your situation. Another point, while client ID may seem like a natural key it depends on how its generated. If its generated by the system then its not a natural key. You also keep missing my point that Case is a separate entity from client. You are generating a a composite key by concatenating the client ID with a sequential number that is sequential only within the client. This is just the type of situation that Pat decries in that thread. You keep looking only at points that you think support your position and not those that talk against it.
You want to see other people support my position? Post the question over at utteraccess.com. I doubt if anyone there will support using a composite key for your application.
