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
|
|||
|
|||
building a complicated (to me) string
Using Access 2007
I am trying to build a couple of strings from the following tables/fields. Is it possible to do with this table structure, or would it be better to add a couple of fields to the tblFamily like FamTitle (Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to be redundant data though. Your help and guidance is very much appreciated. Kathy R. tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID (foreign key) InTitleID (foreign key) FirstName ContactStatus (primary contact, secondary contact, child, other) tlkpTitle TitleID (primary key) Title (Mr., Mrs., Ms., Dr., etc.) The strings I would like to build: For each tblIndividual.InFamID [Title of primary contact] & & [Title of secondary contact if present] & & [FirstName of primary contact] & & [FamLastName] Example with data: Mr. and Mrs. John Doe (if there is a primary and secondary contact) Mr. Bob Jones (if there is only a primary contact) AND by FirstName instead of Title: [FirstName of primary contact] & & [FirstName of secondary contact if present] & & [FamLastName] Example with data: John and Jane Doe (if there is a primary and secondary contact) Bob Jones (if there is only a primary contact) |
#2
|
|||
|
|||
building a complicated (to me) string
Kathy R. wrote:
Using Access 2007 I am trying to build a couple of strings from the following tables/fields. Is it possible to do with this table structure, or would it be better to add a couple of fields to the tblFamily like FamTitle (Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to be redundant data though. tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID (foreign key) InTitleID (foreign key) FirstName ContactStatus (primary contact, secondary contact, child, other) tlkpTitle TitleID (primary key) Title (Mr., Mrs., Ms., Dr., etc.) The strings I would like to build: For each tblIndividual.InFamID [Title of primary contact] & & [Title of secondary contact if present] & & [FirstName of primary contact] & & [FamLastName] Example with data: Mr. and Mrs. John Doe (if there is a primary and secondary contact) Mr. Bob Jones (if there is only a primary contact) AND by FirstName instead of Title: [FirstName of primary contact] & & [FirstName of secondary contact if present] & & [FamLastName] Example with data: John and Jane Doe (if there is a primary and secondary contact) Bob Jones (if there is only a primary contact) I suggest that you rethink the entities that you are trying to model. It's possible for each member of a household to have a different last name so the individuals table need a last name field. Then you can not algoithimically determine such a thing as a family name do you need some other way to determine the string you want to construct. -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
building a complicated (to me) string
Marshall Barton wrote: Kathy R. wrote: Using Access 2007 I am trying to build a couple of strings from the following tables/fields. Is it possible to do with this table structure, or would it be better to add a couple of fields to the tblFamily like FamTitle (Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to be redundant data though. tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID (foreign key) InTitleID (foreign key) FirstName ContactStatus (primary contact, secondary contact, child, other) tlkpTitle TitleID (primary key) Title (Mr., Mrs., Ms., Dr., etc.) The strings I would like to build: For each tblIndividual.InFamID [Title of primary contact] & & [Title of secondary contact if present] & & [FirstName of primary contact] & & [FamLastName] Example with data: Mr. and Mrs. John Doe (if there is a primary and secondary contact) Mr. Bob Jones (if there is only a primary contact) AND by FirstName instead of Title: [FirstName of primary contact] & & [FirstName of secondary contact if present] & & [FamLastName] Example with data: John and Jane Doe (if there is a primary and secondary contact) Bob Jones (if there is only a primary contact) I suggest that you rethink the entities that you are trying to model. It's possible for each member of a household to have a different last name so the individuals table need a last name field. Then you can not algoithimically determine such a thing as a family name do you need some other way to determine the string you want to construct. Marshall, You are correct, and I already do have LastName in the individual table. If I were addressing a person singly, I would use [tblIndividual.FirstName] & " " & [tblIndividual.LastName]. However, when addressing a couple I use the FamLastName from the family table. This most often is the same as the last name of the primary contact, but I can foresee times when it may not be. Hence the FamLastName field. So John Doe and Jane Doe-Smith becomes John and Jane Doe or Mr. and Mrs. John Doe, which are the strings I am trying to build. Now, I know I'm a bit old-fashioned, and probably out of the loop (been out of school a good many years); is there a more socially acceptable way of addressing a couple? Really, I'm not being facetious here. I tend to have tunnel vision when working on a project so I appreciate any pointers. You, and many others here, have helped me "sort out my thinking" more times than I care to mention. Thank you! Kathy R. |
#4
|
|||
|
|||
building a complicated (to me) string
Kathy R. wrote:
Marshall Barton wrote: Kathy R. wrote: Using Access 2007 I am trying to build a couple of strings from the following tables/fields. Is it possible to do with this table structure, or would it be better to add a couple of fields to the tblFamily like FamTitle (Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to be redundant data though. tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID (foreign key) InTitleID (foreign key) FirstName ContactStatus (primary contact, secondary contact, child, other) tlkpTitle TitleID (primary key) Title (Mr., Mrs., Ms., Dr., etc.) The strings I would like to build: For each tblIndividual.InFamID [Title of primary contact] & & [Title of secondary contact if present] & & [FirstName of primary contact] & & [FamLastName] Example with data: Mr. and Mrs. John Doe (if there is a primary and secondary contact) Mr. Bob Jones (if there is only a primary contact) AND by FirstName instead of Title: [FirstName of primary contact] & & [FirstName of secondary contact if present] & & [FamLastName] Example with data: John and Jane Doe (if there is a primary and secondary contact) Bob Jones (if there is only a primary contact) I suggest that you rethink the entities that you are trying to model. It's possible for each member of a household to have a different last name so the individuals table need a last name field. Then you can not algoithimically determine such a thing as a family name do you need some other way to determine the string you want to construct. You are correct, and I already do have LastName in the individual table. If I were addressing a person singly, I would use [tblIndividual.FirstName] & " " & [tblIndividual.LastName]. However, when addressing a couple I use the FamLastName from the family table. This most often is the same as the last name of the primary contact, but I can foresee times when it may not be. Hence the FamLastName field. So John Doe and Jane Doe-Smith becomes John and Jane Doe or Mr. and Mrs. John Doe, which are the strings I am trying to build. Now, I know I'm a bit old-fashioned, and probably out of the loop (been out of school a good many years); is there a more socially acceptable way of addressing a couple? Well. I've been out of school a lot longer than most people and there is no way I can claim to be an authority on how people want to be addressed. The only person that a priori knows how to address a person in that individual. Short of obtaining that information from the individual and saving it in the database, I have bailed out on the question by either not trying to adress two people or by checking if the primary head of family (whoever that might be) last name is the same as the spouse(???) last name, then use: John and Jane Doe but if the last names are different, use John Doe and Jane Doe-Smith The use of Mr, Mrs, Miss. Ms, Dr, etc. can also ruffle some feathers if you have no information about the individual's preference. For this part of the issue, when the person's preference is not known, I have also bailed out by just not using any honorific. Even with all that issue avoidance, I have seen people toss out mail just because their name was not properly capitalized. The same problem arises if a name is inappropriately shortened, eg. using Tim when he prefers to be called Timothy. -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
building a complicated (to me) string
Marshall Barton wrote:
Kathy R. wrote: Marshall Barton wrote: Kathy R. wrote: Using Access 2007 I am trying to build a couple of strings from the following tables/fields. Is it possible to do with this table structure, or would it be better to add a couple of fields to the tblFamily like FamTitle (Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to be redundant data though. tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID (foreign key) InTitleID (foreign key) FirstName ContactStatus (primary contact, secondary contact, child, other) tlkpTitle TitleID (primary key) Title (Mr., Mrs., Ms., Dr., etc.) The strings I would like to build: For each tblIndividual.InFamID [Title of primary contact] & & [Title of secondary contact if present] & & [FirstName of primary contact] & & [FamLastName] Example with data: Mr. and Mrs. John Doe (if there is a primary and secondary contact) Mr. Bob Jones (if there is only a primary contact) AND by FirstName instead of Title: [FirstName of primary contact] & & [FirstName of secondary contact if present] & & [FamLastName] Example with data: John and Jane Doe (if there is a primary and secondary contact) Bob Jones (if there is only a primary contact) I suggest that you rethink the entities that you are trying to model. It's possible for each member of a household to have a different last name so the individuals table need a last name field. Then you can not algoithimically determine such a thing as a family name do you need some other way to determine the string you want to construct. You are correct, and I already do have LastName in the individual table. If I were addressing a person singly, I would use [tblIndividual.FirstName] & " " & [tblIndividual.LastName]. However, when addressing a couple I use the FamLastName from the family table. This most often is the same as the last name of the primary contact, but I can foresee times when it may not be. Hence the FamLastName field. So John Doe and Jane Doe-Smith becomes John and Jane Doe or Mr. and Mrs. John Doe, which are the strings I am trying to build. Now, I know I'm a bit old-fashioned, and probably out of the loop (been out of school a good many years); is there a more socially acceptable way of addressing a couple? Well. I've been out of school a lot longer than most people and there is no way I can claim to be an authority on how people want to be addressed. The only person that a priori knows how to address a person in that individual. Short of obtaining that information from the individual and saving it in the database, I have bailed out on the question by either not trying to adress two people or by checking if the primary head of family (whoever that might be) last name is the same as the spouse(???) last name, then use: John and Jane Doe but if the last names are different, use John Doe and Jane Doe-Smith The use of Mr, Mrs, Miss. Ms, Dr, etc. can also ruffle some feathers if you have no information about the individual's preference. For this part of the issue, when the person's preference is not known, I have also bailed out by just not using any honorific. Even with all that issue avoidance, I have seen people toss out mail just because their name was not properly capitalized. The same problem arises if a name is inappropriately shortened, eg. using Tim when he prefers to be called Timothy. All good points! But it still leaves me with building a string and the bane of my existence - the "Get the syntax right!" error. So, if I were to use John Doe and Jane Doe-Smith it needs to be something like this... (([FirstName] & " " & [LastName] Where [ContactStatus] = "primary contact") & "and" & ([FirstName] & " " & [LastName]) Where [ContactStatus] = "secondary contact")) Where [FamID] is the same My two questions are 1) How do I tell it to use the primary and secondary contacts from the same family (FamID), and 2) How do I not include the "and" between the names if there is no secondary contact? Thank you so much for taking the time to respond! Kathy R. |
#6
|
|||
|
|||
building a complicated (to me) string
Kathy R. wrote:
Marshall Barton wrote: Kathy R. wrote: Marshall Barton wrote: Kathy R. wrote: Using Access 2007 I am trying to build a couple of strings from the following tables/fields. Is it possible to do with this table structure, or would it be better to add a couple of fields to the tblFamily like FamTitle (Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to be redundant data though. tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID (foreign key) InTitleID (foreign key) FirstName ContactStatus (primary contact, secondary contact, child, other) tlkpTitle TitleID (primary key) Title (Mr., Mrs., Ms., Dr., etc.) The strings I would like to build: For each tblIndividual.InFamID [Title of primary contact] & & [Title of secondary contact if present] & & [FirstName of primary contact] & & [FamLastName] Example with data: Mr. and Mrs. John Doe (if there is a primary and secondary contact) Mr. Bob Jones (if there is only a primary contact) AND by FirstName instead of Title: [FirstName of primary contact] & & [FirstName of secondary contact if present] & & [FamLastName] Example with data: John and Jane Doe (if there is a primary and secondary contact) Bob Jones (if there is only a primary contact) I suggest that you rethink the entities that you are trying to model. It's possible for each member of a household to have a different last name so the individuals table need a last name field. Then you can not algoithimically determine such a thing as a family name do you need some other way to determine the string you want to construct. You are correct, and I already do have LastName in the individual table. If I were addressing a person singly, I would use [tblIndividual.FirstName] & " " & [tblIndividual.LastName]. However, when addressing a couple I use the FamLastName from the family table. This most often is the same as the last name of the primary contact, but I can foresee times when it may not be. Hence the FamLastName field. So John Doe and Jane Doe-Smith becomes John and Jane Doe or Mr. and Mrs. John Doe, which are the strings I am trying to build. Now, I know I'm a bit old-fashioned, and probably out of the loop (been out of school a good many years); is there a more socially acceptable way of addressing a couple? Well. I've been out of school a lot longer than most people and there is no way I can claim to be an authority on how people want to be addressed. The only person that a priori knows how to address a person in that individual. Short of obtaining that information from the individual and saving it in the database, I have bailed out on the question by either not trying to adress two people or by checking if the primary head of family (whoever that might be) last name is the same as the spouse(???) last name, then use: John and Jane Doe but if the last names are different, use John Doe and Jane Doe-Smith The use of Mr, Mrs, Miss. Ms, Dr, etc. can also ruffle some feathers if you have no information about the individual's preference. For this part of the issue, when the person's preference is not known, I have also bailed out by just not using any honorific. Even with all that issue avoidance, I have seen people toss out mail just because their name was not properly capitalized. The same problem arises if a name is inappropriately shortened, eg. using Tim when he prefers to be called Timothy. All good points! But it still leaves me with building a string and the bane of my existence - the "Get the syntax right!" error. So, if I were to use John Doe and Jane Doe-Smith it needs to be something like this... (([FirstName] & " " & [LastName] Where [ContactStatus] = "primary contact") & "and" & ([FirstName] & " " & [LastName]) Where [ContactStatus] = "secondary contact")) Where [FamID] is the same My two questions are 1) How do I tell it to use the primary and secondary contacts from the same family (FamID), and 2) How do I not include the "and" between the names if there is no secondary contact? I think the report's record source query could look something like: SELECT P.FirstName As PriFirst, P.Lastname As PriLast, S.FirstName As SecFirst, S.Lastname As SecLast, . . . FROM tblIndividual As P LEFT JOIN tblIndividual As S ON P.InFamID = S.InFamID WHERE P.ContactStatus = "primary" AND (S.ContactStatus = "secondary" OR S.ContactStatus Is Null) Then then text box expression in the report might be like: =PriFirst & IIf(PriLast = SecLast, " and " & SecFirst &" " & PriLast, " " & PriLast & (" and " + SecFirst + " " + SecLast)) -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
building a complicated (to me) string
Marshall Barton wrote:
Kathy R. wrote: Marshall Barton wrote: Kathy R. wrote: Marshall Barton wrote: Kathy R. wrote: Using Access 2007 I am trying to build a couple of strings from the following tables/fields. Is it possible to do with this table structure, or would it be better to add a couple of fields to the tblFamily like FamTitle (Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to be redundant data though. tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID (foreign key) InTitleID (foreign key) FirstName ContactStatus (primary contact, secondary contact, child, other) tlkpTitle TitleID (primary key) Title (Mr., Mrs., Ms., Dr., etc.) The strings I would like to build: For each tblIndividual.InFamID [Title of primary contact] & & [Title of secondary contact if present] & & [FirstName of primary contact] & & [FamLastName] Example with data: Mr. and Mrs. John Doe (if there is a primary and secondary contact) Mr. Bob Jones (if there is only a primary contact) AND by FirstName instead of Title: [FirstName of primary contact] & & [FirstName of secondary contact if present] & & [FamLastName] Example with data: John and Jane Doe (if there is a primary and secondary contact) Bob Jones (if there is only a primary contact) I suggest that you rethink the entities that you are trying to model. It's possible for each member of a household to have a different last name so the individuals table need a last name field. Then you can not algoithimically determine such a thing as a family name do you need some other way to determine the string you want to construct. You are correct, and I already do have LastName in the individual table. If I were addressing a person singly, I would use [tblIndividual.FirstName] & " " & [tblIndividual.LastName]. However, when addressing a couple I use the FamLastName from the family table. This most often is the same as the last name of the primary contact, but I can foresee times when it may not be. Hence the FamLastName field. So John Doe and Jane Doe-Smith becomes John and Jane Doe or Mr. and Mrs. John Doe, which are the strings I am trying to build. Now, I know I'm a bit old-fashioned, and probably out of the loop (been out of school a good many years); is there a more socially acceptable way of addressing a couple? Well. I've been out of school a lot longer than most people and there is no way I can claim to be an authority on how people want to be addressed. The only person that a priori knows how to address a person in that individual. Short of obtaining that information from the individual and saving it in the database, I have bailed out on the question by either not trying to adress two people or by checking if the primary head of family (whoever that might be) last name is the same as the spouse(???) last name, then use: John and Jane Doe but if the last names are different, use John Doe and Jane Doe-Smith The use of Mr, Mrs, Miss. Ms, Dr, etc. can also ruffle some feathers if you have no information about the individual's preference. For this part of the issue, when the person's preference is not known, I have also bailed out by just not using any honorific. Even with all that issue avoidance, I have seen people toss out mail just because their name was not properly capitalized. The same problem arises if a name is inappropriately shortened, eg. using Tim when he prefers to be called Timothy. All good points! But it still leaves me with building a string and the bane of my existence - the "Get the syntax right!" error. So, if I were to use John Doe and Jane Doe-Smith it needs to be something like this... (([FirstName] & " " & [LastName] Where [ContactStatus] = "primary contact") & "and" & ([FirstName] & " " & [LastName]) Where [ContactStatus] = "secondary contact")) Where [FamID] is the same My two questions are 1) How do I tell it to use the primary and secondary contacts from the same family (FamID), and 2) How do I not include the "and" between the names if there is no secondary contact? I think the report's record source query could look something like: SELECT P.FirstName As PriFirst, P.Lastname As PriLast, S.FirstName As SecFirst, S.Lastname As SecLast, . . . FROM tblIndividual As P LEFT JOIN tblIndividual As S ON P.InFamID = S.InFamID WHERE P.ContactStatus = "primary" AND (S.ContactStatus = "secondary" OR S.ContactStatus Is Null) Then then text box expression in the report might be like: =PriFirst & IIf(PriLast = SecLast, " and " & SecFirst &" " & PriLast, " " & PriLast & (" and " + SecFirst + " " + SecLast)) I hadn't thought of using aliases. I haven't worked with them much. This works super for all of my couples. Thank you Marshall! Unfortunately it's leaving all of my singles off the list. The problem is probably the WHERE clause. AND (S.ContactStatus = "secondary" OR S.ContactStatus Is Null) ContactStatus is a required field and should never be null. There are three other choices (child, other, inactive) that I didn't mention for the sake of simplicity. But wouldn't the aliased table "S" only contain those records whose ContactStatus is "secondary" anyways? My scenario needs to include names where the family has no Secondary contact. There could, however, be "child," "other" or "inactive." I don't want those three to print though. In the case where there is no Secondary contact, and regardless of whether or not there are other contacts I just need PriFirst PriLast, i.e. "Bob Jones". I tried a RIGHT JOIN (FROM tblIndividual As S RIGHT JOIN tblIndividual As P) thinking that if I saw all the records from "P" and only those records from "S" that were equal it would work, but it gave me the same results. |
#8
|
|||
|
|||
building a complicated (to me) string
Kathy R. wrote:
Marshall Barton wrote: Kathy R. wrote: Marshall Barton wrote: Kathy R. wrote: Marshall Barton wrote: Kathy R. wrote: Using Access 2007 I am trying to build a couple of strings from the following tables/fields. Is it possible to do with this table structure, or would it be better to add a couple of fields to the tblFamily like FamTitle (Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to be redundant data though. tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID (foreign key) InTitleID (foreign key) FirstName ContactStatus (primary contact, secondary contact, child, other) tlkpTitle TitleID (primary key) Title (Mr., Mrs., Ms., Dr., etc.) The strings I would like to build: For each tblIndividual.InFamID [Title of primary contact] & & [Title of secondary contact if present] & & [FirstName of primary contact] & & [FamLastName] Example with data: Mr. and Mrs. John Doe (if there is a primary and secondary contact) Mr. Bob Jones (if there is only a primary contact) AND by FirstName instead of Title: [FirstName of primary contact] & & [FirstName of secondary contact if present] & & [FamLastName] Example with data: John and Jane Doe (if there is a primary and secondary contact) Bob Jones (if there is only a primary contact) I suggest that you rethink the entities that you are trying to model. It's possible for each member of a household to have a different last name so the individuals table need a last name field. Then you can not algoithimically determine such a thing as a family name do you need some other way to determine the string you want to construct. You are correct, and I already do have LastName in the individual table. If I were addressing a person singly, I would use [tblIndividual.FirstName] & " " & [tblIndividual.LastName]. However, when addressing a couple I use the FamLastName from the family table. This most often is the same as the last name of the primary contact, but I can foresee times when it may not be. Hence the FamLastName field. So John Doe and Jane Doe-Smith becomes John and Jane Doe or Mr. and Mrs. John Doe, which are the strings I am trying to build. Now, I know I'm a bit old-fashioned, and probably out of the loop (been out of school a good many years); is there a more socially acceptable way of addressing a couple? Well. I've been out of school a lot longer than most people and there is no way I can claim to be an authority on how people want to be addressed. The only person that a priori knows how to address a person in that individual. Short of obtaining that information from the individual and saving it in the database, I have bailed out on the question by either not trying to adress two people or by checking if the primary head of family (whoever that might be) last name is the same as the spouse(???) last name, then use: John and Jane Doe but if the last names are different, use John Doe and Jane Doe-Smith The use of Mr, Mrs, Miss. Ms, Dr, etc. can also ruffle some feathers if you have no information about the individual's preference. For this part of the issue, when the person's preference is not known, I have also bailed out by just not using any honorific. Even with all that issue avoidance, I have seen people toss out mail just because their name was not properly capitalized. The same problem arises if a name is inappropriately shortened, eg. using Tim when he prefers to be called Timothy. All good points! But it still leaves me with building a string and the bane of my existence - the "Get the syntax right!" error. So, if I were to use John Doe and Jane Doe-Smith it needs to be something like this... (([FirstName] & " " & [LastName] Where [ContactStatus] = "primary contact") & "and" & ([FirstName] & " " & [LastName]) Where [ContactStatus] = "secondary contact")) Where [FamID] is the same My two questions are 1) How do I tell it to use the primary and secondary contacts from the same family (FamID), and 2) How do I not include the "and" between the names if there is no secondary contact? I think the report's record source query could look something like: SELECT P.FirstName As PriFirst, P.Lastname As PriLast, S.FirstName As SecFirst, S.Lastname As SecLast, . . . FROM tblIndividual As P LEFT JOIN tblIndividual As S ON P.InFamID = S.InFamID WHERE P.ContactStatus = "primary" AND (S.ContactStatus = "secondary" OR S.ContactStatus Is Null) Then then text box expression in the report might be like: =PriFirst & IIf(PriLast = SecLast, " and " & SecFirst &" " & PriLast, " " & PriLast & (" and " + SecFirst + " " + SecLast)) I hadn't thought of using aliases. I haven't worked with them much. This works super for all of my couples. Thank you Marshall! Unfortunately it's leaving all of my singles off the list. The problem is probably the WHERE clause. AND (S.ContactStatus = "secondary" OR S.ContactStatus Is Null) ContactStatus is a required field and should never be null. There are three other choices (child, other, inactive) that I didn't mention for the sake of simplicity. But wouldn't the aliased table "S" only contain those records whose ContactStatus is "secondary" anyways? My scenario needs to include names where the family has no Secondary contact. There could, however, be "child," "other" or "inactive." I don't want those three to print though. In the case where there is no Secondary contact, and regardless of whether or not there are other contacts I just need PriFirst PriLast, i.e. "Bob Jones". I tried a RIGHT JOIN (FROM tblIndividual As S RIGHT JOIN tblIndividual As P) thinking that if I saw all the records from "P" and only those records from "S" that were equal it would work, but it gave me the same results. Because of the Left Join, the secondary contact will be Null when there is no other record with the same InFamID. I didn't think of the case where there might be an individual with no secondary, but with some other kind of contact (child, etc). So the Where clause is being applied to the wrong set of records. It should be used to only select primary or secondary contacts. I think this should sort that out: SELECT P.FirstName As PriFirst, P.Lastname As PriLast, S.FirstName As SecFirst, S.Lastname As SecLast, . . . FROM (SELECT X.InFamID, X.FirstName As PriFirst, X.Lastname As PriLastt . . . FROM tblIndividual As X WHERE P.ContactStatus = "primary") As P LEFT JOIN (SELECT X.InFamID X.FirstName As SecFirst, X.Lastname As SecLast FROM tblIndividual As X WHERE X.ContactStatus = "secondary") As S ON P.InFamID = S.InFamID -- Marsh MVP [MS Access] |
#9
|
|||
|
|||
building a complicated (to me) string
Marshall Barton wrote:
Kathy R. wrote: Marshall Barton wrote: Kathy R. wrote: Marshall Barton wrote: Kathy R. wrote: Marshall Barton wrote: Kathy R. wrote: Using Access 2007 I am trying to build a couple of strings from the following tables/fields. Is it possible to do with this table structure, or would it be better to add a couple of fields to the tblFamily like FamTitle (Mr. & Mrs.) and FamFirstName (John and Jane). This would seem to me to be redundant data though. tblFamily FamID (primary key) FamLastName tblIndividual IndID (primary key) InFamID (foreign key) InTitleID (foreign key) FirstName ContactStatus (primary contact, secondary contact, child, other) tlkpTitle TitleID (primary key) Title (Mr., Mrs., Ms., Dr., etc.) The strings I would like to build: For each tblIndividual.InFamID [Title of primary contact] & & [Title of secondary contact if present] & & [FirstName of primary contact] & & [FamLastName] Example with data: Mr. and Mrs. John Doe (if there is a primary and secondary contact) Mr. Bob Jones (if there is only a primary contact) AND by FirstName instead of Title: [FirstName of primary contact] & & [FirstName of secondary contact if present] & & [FamLastName] Example with data: John and Jane Doe (if there is a primary and secondary contact) Bob Jones (if there is only a primary contact) I suggest that you rethink the entities that you are trying to model. It's possible for each member of a household to have a different last name so the individuals table need a last name field. Then you can not algoithimically determine such a thing as a family name do you need some other way to determine the string you want to construct. You are correct, and I already do have LastName in the individual table. If I were addressing a person singly, I would use [tblIndividual.FirstName] & " " & [tblIndividual.LastName]. However, when addressing a couple I use the FamLastName from the family table. This most often is the same as the last name of the primary contact, but I can foresee times when it may not be. Hence the FamLastName field. So John Doe and Jane Doe-Smith becomes John and Jane Doe or Mr. and Mrs. John Doe, which are the strings I am trying to build. Now, I know I'm a bit old-fashioned, and probably out of the loop (been out of school a good many years); is there a more socially acceptable way of addressing a couple? Well. I've been out of school a lot longer than most people and there is no way I can claim to be an authority on how people want to be addressed. The only person that a priori knows how to address a person in that individual. Short of obtaining that information from the individual and saving it in the database, I have bailed out on the question by either not trying to adress two people or by checking if the primary head of family (whoever that might be) last name is the same as the spouse(???) last name, then use: John and Jane Doe but if the last names are different, use John Doe and Jane Doe-Smith The use of Mr, Mrs, Miss. Ms, Dr, etc. can also ruffle some feathers if you have no information about the individual's preference. For this part of the issue, when the person's preference is not known, I have also bailed out by just not using any honorific. Even with all that issue avoidance, I have seen people toss out mail just because their name was not properly capitalized. The same problem arises if a name is inappropriately shortened, eg. using Tim when he prefers to be called Timothy. All good points! But it still leaves me with building a string and the bane of my existence - the "Get the syntax right!" error. So, if I were to use John Doe and Jane Doe-Smith it needs to be something like this... (([FirstName] & " " & [LastName] Where [ContactStatus] = "primary contact") & "and" & ([FirstName] & " " & [LastName]) Where [ContactStatus] = "secondary contact")) Where [FamID] is the same My two questions are 1) How do I tell it to use the primary and secondary contacts from the same family (FamID), and 2) How do I not include the "and" between the names if there is no secondary contact? I think the report's record source query could look something like: SELECT P.FirstName As PriFirst, P.Lastname As PriLast, S.FirstName As SecFirst, S.Lastname As SecLast, . . . FROM tblIndividual As P LEFT JOIN tblIndividual As S ON P.InFamID = S.InFamID WHERE P.ContactStatus = "primary" AND (S.ContactStatus = "secondary" OR S.ContactStatus Is Null) Then then text box expression in the report might be like: =PriFirst & IIf(PriLast = SecLast, " and " & SecFirst &" " & PriLast, " " & PriLast & (" and " + SecFirst + " " + SecLast)) I hadn't thought of using aliases. I haven't worked with them much. This works super for all of my couples. Thank you Marshall! Unfortunately it's leaving all of my singles off the list. The problem is probably the WHERE clause. AND (S.ContactStatus = "secondary" OR S.ContactStatus Is Null) ContactStatus is a required field and should never be null. There are three other choices (child, other, inactive) that I didn't mention for the sake of simplicity. But wouldn't the aliased table "S" only contain those records whose ContactStatus is "secondary" anyways? My scenario needs to include names where the family has no Secondary contact. There could, however, be "child," "other" or "inactive." I don't want those three to print though. In the case where there is no Secondary contact, and regardless of whether or not there are other contacts I just need PriFirst PriLast, i.e. "Bob Jones". I tried a RIGHT JOIN (FROM tblIndividual As S RIGHT JOIN tblIndividual As P) thinking that if I saw all the records from "P" and only those records from "S" that were equal it would work, but it gave me the same results. Because of the Left Join, the secondary contact will be Null when there is no other record with the same InFamID. I didn't think of the case where there might be an individual with no secondary, but with some other kind of contact (child, etc). So the Where clause is being applied to the wrong set of records. It should be used to only select primary or secondary contacts. I think this should sort that out: SELECT P.FirstName As PriFirst, P.Lastname As PriLast, S.FirstName As SecFirst, S.Lastname As SecLast, . . . FROM (SELECT X.InFamID, X.FirstName As PriFirst, X.Lastname As PriLastt . . . FROM tblIndividual As X WHERE P.ContactStatus = "primary") As P LEFT JOIN (SELECT X.InFamID X.FirstName As SecFirst, X.Lastname As SecLast FROM tblIndividual As X WHERE X.ContactStatus = "secondary") As S ON P.InFamID = S.InFamID First, my apologies for taking so long to get back to this. I was waylaid by a nasty cold and a ton of work last week. With a few changes/corrections, your SQL works quite well. Thank you for taking so much time to work through it with me! I've noted the changes below in case anyone else is interested. removed comma at end of line, changed P.FirstName to P.PriFirst, etc. SELECT P.PriFirst As PriFirst, P.PriLast As PriLast, S.SecFirst As SecFirst, S.SecLast As SecLast removed extra t at end of line FROM (SELECT X.InFamID, X.FirstName As PriFirst, X.Lastname As PriLast FROM tblIndividual As X changed P T0 X in the WHERE clause WHERE X.ContactStatus = "Primary") As P LEFT JOIN (SELECT X.InFamID, X.FirstName As SecFirst, X.Lastname As SecLast FROM tblIndividual As X WHERE X.ContactStatus = "Secondary") As S ON P.InFamID = S.InFamID On a side note, as I was searching for alternate solutions I came across Duane Hookom's concatenation function, which also works. Both of these solutions will go into my "keep these in mind the next time you run into this type of problem" folder. Again, thank you for your help! Kathy R. |
#10
|
|||
|
|||
building a complicated (to me) string
Kathy R. wrote:
With a few changes/corrections, your SQL works quite well. Thank you for taking so much time to work through it with me! SELECT P.PriFirst As PriFirst, P.PriLast As PriLast, S.SecFirst As SecFirst, S.SecLast As SecLast FROM (SELECT X.InFamID, X.FirstName As PriFirst, X.Lastname As PriLast FROM tblIndividual As X WHERE X.ContactStatus = "Primary") As P LEFT JOIN (SELECT X.InFamID, X.FirstName As SecFirst, X.Lastname As SecLast FROM tblIndividual As X WHERE X.ContactStatus = "Secondary") As S ON P.InFamID = S.InFamID On a side note, as I was searching for alternate solutions I came across Duane Hookom's concatenation function, which also works. Both of these solutions will go into my "keep these in mind the next time you run into this type of problem" folder. Again, thank you for your help! Sorry for all the typos. You did a nice job of finding and fixing them. We both missed one though. There is no reason to alias a field name to itself. Here's my cleaned up version: SELECT P.PriFirst, P.PriLast, S.SecFirst, S.SecLast FROM (SELECT X.InFamID, X.FirstName As PriFirst, X.Lastname As PriLast FROM tblIndividual As X WHERE X.ContactStatus = "Primary") As P LEFT JOIN (SELECT X.InFamID, X.FirstName As SecFirst, X.Lastname As SecLast FROM tblIndividual As X WHERE X.ContactStatus = "Secondary") As S ON P.InFamID = S.InFamID The Concatenate function (and many variations on that theme) are very useful in some situations and well worth remembering. At this point, however, it is not part of any solution to the current question. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|