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)

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]

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.

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]

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.

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]