Nested IIf

[Serendipity]

I have a table where each family member is in a separate record (imported
from another program that has it that way). There is a fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head, spouse, child.
I need to make a name tag that gets the dad's name (fldHead) out of one
record and then mom's (fldSpouse) name out of another record and then puts
them together with the fldLastName on the same line. Here is what I have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " & [LastName]," ")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Douglas J. Steele]

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you need to
repeat the field name:

=IIf([FamilyPosition]="head" Or [FamilyPosition]="spouse",[FirstName] & " "
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " & [LastName],"
")

 

[Serendipity]

The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another record and the
Davis is in both records--plus the kids records. Hope that makes it more
clear.

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you need to
repeat the field name:

=IIf([FamilyPosition]="head" Or [FamilyPosition]="spouse",[FirstName] & " "
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " & [LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table where each family member is in a separate record (imported
from another program that has it that way). There is a fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head, spouse,
child.
I need to make a name tag that gets the dad's name (fldHead) out of one
record and then mom's (fldSpouse) name out of another record and then puts
them together with the fldLastName on the same line. Here is what I have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " & [LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Douglas J. Steele]

What do your tables look like? I understand that David would have head (or
spouse) and Sherri would have spouse (or head), but is there any way to know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another record and the
Davis is in both records--plus the kids records. Hope that makes it more
clear.

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you need to
repeat the field name:

=IIf([FamilyPosition]="head" Or [FamilyPosition]="spouse",[FirstName] & "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " &
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table where each family member is in a separate record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head, spouse,
child.
I need to make a name tag that gets the dad's name (fldHead) out of one
record and then mom's (fldSpouse) name out of another record and then
puts
them together with the fldLastName on the same line. Here is what I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " &
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Serendipity]

Yes, they have a common family number.

What do your tables look like? I understand that David would have head (or
spouse) and Sherri would have spouse (or head), but is there any way to know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another record and the
Davis is in both records--plus the kids records. Hope that makes it more
clear.

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you need to
repeat the field name:

=IIf([FamilyPosition]="head" Or [FamilyPosition]="spouse",[FirstName] & "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " &
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a table where each family member is in a separate record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head, spouse,
child.
I need to make a name tag that gets the dad's name (fldHead) out of one
record and then mom's (fldSpouse) name out of another record and then
puts
them together with the fldLastName on the same line. Here is what I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " &
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Douglas J. Steele]

If you want help, you're going to have to provide more information!

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, they have a common family number.

What do your tables look like? I understand that David would have head
(or
spouse) and Sherri would have spouse (or head), but is there any way to
know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another record and
the
Davis is in both records--plus the kids records. Hope that makes it
more
clear.

:

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you need to
repeat the field name:

=IIf([FamilyPosition]="head" Or [FamilyPosition]="spouse",[FirstName]
& "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " &
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a table where each family member is in a separate record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head,
spouse,
child.
I need to make a name tag that gets the dad's name (fldHead) out of
one
record and then mom's (fldSpouse) name out of another record and
then
puts
them together with the fldLastName on the same line. Here is what I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " &
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Serendipity]

There is only one table because that is how it imported. This table has six
fields: fldLastName, fldFirstName, fldFamilyID, fldIndividualID,
fldFamilyPosition, fldSecuityKey (applies to all members in family). Each
family member is a different record. Hope I covered everything without being
wordy.

Serendipity

If you want help, you're going to have to provide more information!

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, they have a common family number.

What do your tables look like? I understand that David would have head
(or
spouse) and Sherri would have spouse (or head), but is there any way to
know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another record and
the
Davis is in both records--plus the kids records. Hope that makes it
more
clear.

:

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you need to
repeat the field name:

=IIf([FamilyPosition]="head" Or [FamilyPosition]="spouse",[FirstName]
& "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " &
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a table where each family member is in a separate record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head,
spouse,
child.
I need to make a name tag that gets the dad's name (fldHead) out of
one
record and then mom's (fldSpouse) name out of another record and
then
puts
them together with the fldLastName on the same line. Here is what I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " &
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Serendipity]

I just read back over the complete thread. In my first post, I said that
there was a fldhead and fldspouse. That was something someone else was
trying. Those fields are no longer in the table. The correct fields are in my
post just before this one.l

If you want help, you're going to have to provide more information!

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, they have a common family number.

What do your tables look like? I understand that David would have head
(or
spouse) and Sherri would have spouse (or head), but is there any way to
know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another record and
the
Davis is in both records--plus the kids records. Hope that makes it
more
clear.

:

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you need to
repeat the field name:

=IIf([FamilyPosition]="head" Or [FamilyPosition]="spouse",[FirstName]
& "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " &
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a table where each family member is in a separate record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head,
spouse,
child.
I need to make a name tag that gets the dad's name (fldHead) out of
one
record and then mom's (fldSpouse) name out of another record and
then
puts
them together with the fldLastName on the same line. Here is what I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " &
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Douglas J. Steele]

Actually, a few more pieces of information.

Are you only trying to get name tags for the adults, or for the kids as
well?

What are the legitimate values for fldFamilyPosition? I'm assuming this is
where head and spouse will be: what else? Also, if you've got a single
adult, what will that value be?

Pasting, say, 10 rows of data (showing a couple with kids, a couple with no
kids, a single parent with kids and an adult with no family) would be
useful. For the data you post, show what name tags you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

There is only one table because that is how it imported. This table has
six
fields: fldLastName, fldFirstName, fldFamilyID, fldIndividualID,
fldFamilyPosition, fldSecuityKey (applies to all members in family). Each
family member is a different record. Hope I covered everything without
being
wordy.

Serendipity

If you want help, you're going to have to provide more information!

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, they have a common family number.

:

What do your tables look like? I understand that David would have head
(or
spouse) and Sherri would have spouse (or head), but is there any way
to
know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another record
and
the
Davis is in both records--plus the kids records. Hope that makes it
more
clear.

:

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you need
to
repeat the field name:

=IIf([FamilyPosition]="head" Or
[FamilyPosition]="spouse",[FirstName]
& "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " &
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I have a table where each family member is in a separate record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head,
spouse,
child.
I need to make a name tag that gets the dad's name (fldHead) out
of
one
record and then mom's (fldSpouse) name out of another record and
then
puts
them together with the fldLastName on the same line. Here is what
I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " &
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Serendipity]

Thanks for hanging in there to help me with this.
This is what an unformatted name tag would look like. Each line accurately
illustrates what would be on each line of the name tab--minus the comment.

Jones (family last name)
Jeremy Julie Amy (they have three kids)
David and Cheryl Jones (that is the parents first names and last name)
506 (Family security number for daycare)

Legitimate value for fldFamilyPosition are: head (dad or single mom), spouse
(mom), and child

Here are the records for a single-mom family:
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Jennifer Berry head 214
(mom)
Jason Berry child 214
(child)
Chloe Berry child 214
(child)
Each line (family member) is a separate record. Additionally, there are
field in each record for family number and individual number.

Here are the records for a two-parent family:
David Jones head 506
(dad)
Cheryl Jones spouse 506
(mom)
Jeremy Jones child 506
(child)
Julie Jones child 506
(child)
Amy Jones child 506
(child)
Each family member is entered in a separate record. (imported)

Only parents with children in daycare will get a name tag. They present the
name tag in order to pick up their children. So only families with kids are
in this table.

The thing I can't figure out is how to make it work because each family
member is in a separate record.

Did my best. Hope it makes sense.

Serendipity

Actually, a few more pieces of information.

Are you only trying to get name tags for the adults, or for the kids as
well?

What are the legitimate values for fldFamilyPosition? I'm assuming this is
where head and spouse will be: what else? Also, if you've got a single
adult, what will that value be?

Pasting, say, 10 rows of data (showing a couple with kids, a couple with no
kids, a single parent with kids and an adult with no family) would be
useful. For the data you post, show what name tags you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

There is only one table because that is how it imported. This table has
six
fields: fldLastName, fldFirstName, fldFamilyID, fldIndividualID,
fldFamilyPosition, fldSecuityKey (applies to all members in family). Each
family member is a different record. Hope I covered everything without
being
wordy.

Serendipity

If you want help, you're going to have to provide more information!

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Yes, they have a common family number.

:

What do your tables look like? I understand that David would have head
(or
spouse) and Sherri would have spouse (or head), but is there any way
to
know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another record
and
the
Davis is in both records--plus the kids records. Hope that makes it
more
clear.

:

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you need
to
repeat the field name:

=IIf([FamilyPosition]="head" Or
[FamilyPosition]="spouse",[FirstName]
& "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " &
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I have a table where each family member is in a separate record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head,
spouse,
child.
I need to make a name tag that gets the dad's name (fldHead) out
of
one
record and then mom's (fldSpouse) name out of another record and
then
puts
them together with the fldLastName on the same line. Here is what
I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " &
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Douglas J. Steele]

I'm still confused as to what has to appear on the name tag.

To get the names of the parents, you can create and save 2 intermediary
queries, one that returns just the family heads (call it qryFamilyHead), and
one that returns just the spouses (call it qryFamilySpouse). Make sure you
include fldSecurityKey in each query.

Given your example, qryFamilyHead should return:
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Jennifer Berry head 214
David Jones head 506

and qryFamilySpouse should return
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Cheryl Jones spouse 506

Now, create a query that joins those two queries together using a Left Join.
Drag qryFamilyHead in the query builder, then drag qryFamilySpouse. Drag
fldSecurityKey from qryFamilyHead to qryFamilySpouse so that a line appears
between the two tables. Select that line, right-click to get the Join
Properties option, and select the 2nd option ("Include ALL records from
'qryFamilyHead' and only those records from 'qryFamilySpouse where the
joined fields are equal.") Drag the fldFirstName, fldLastName and
fldSecurityKey fields from qryFamilyHead into the grid, and fldFirstName and
fldLastName from qryFamilySpouse. Where you see fldFirstName and fldLastName
from qryFamilyHead in the grid, type aliases in front ot the field names so
that you have HeadFirstName: fldFirstName and HeadLastName: fldLastName. Do
the same with the fields from qryFamilySpouse (so that you have
SpouseFirstName: fldFirstName and SpouseLastName: fldLastName) Run the
query. You should get:

HeadFirstName HeadLastName fldSecurityKey SpouseFirstName SpouseLastName
Jennifer Berry 214
David Jones 506 Cheryl
Jones

The row for Jennifer will actually have Null values for SpouseFirstName and
SpouseLastName.

Now you can join the names together.

You can either do this in the query itself, or in the report. To be totally
flexible, you should probably compare HeadLastName to SpouseLastName, so
that you can print Mark Black & Cheryl Smith rather than Mark & Cheryl
Black. The logic would be something like:

IIf(IsNullI([SpouseLastName]), [HeadFirstName] & " " & [HeadLastName],
IIf([SpouseLastName] = [HeadLastName]), [HeadFirstName] & " & " &
[SpouseFirstName] & " " & [HeadLastName], [HeadFirstName] & " " &
[HeadLastName & " & " [SpouseFirstName] & " " & [SpouseLastName]))

Post back if you need the children names to appear on the tags as well.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for hanging in there to help me with this.
This is what an unformatted name tag would look like. Each line accurately
illustrates what would be on each line of the name tab--minus the comment.

Jones (family last name)
Jeremy Julie Amy (they have three kids)
David and Cheryl Jones (that is the parents first names and last name)
506 (Family security number for daycare)

Legitimate value for fldFamilyPosition are: head (dad or single mom),
spouse
(mom), and child

Here are the records for a single-mom family:
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Jennifer Berry head 214
(mom)
Jason Berry child 214
(child)
Chloe Berry child 214
(child)
Each line (family member) is a separate record. Additionally, there are
field in each record for family number and individual number.

Here are the records for a two-parent family:
David Jones head 506
(dad)
Cheryl Jones spouse 506
(mom)
Jeremy Jones child 506
(child)
Julie Jones child 506
(child)
Amy Jones child 506
(child)
Each family member is entered in a separate record. (imported)

Only parents with children in daycare will get a name tag. They present
the
name tag in order to pick up their children. So only families with kids
are
in this table.

The thing I can't figure out is how to make it work because each family
member is in a separate record.

Did my best. Hope it makes sense.

Serendipity

Actually, a few more pieces of information.

Are you only trying to get name tags for the adults, or for the kids as
well?

What are the legitimate values for fldFamilyPosition? I'm assuming this
is
where head and spouse will be: what else? Also, if you've got a single
adult, what will that value be?

Pasting, say, 10 rows of data (showing a couple with kids, a couple with
no
kids, a single parent with kids and an adult with no family) would be
useful. For the data you post, show what name tags you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

There is only one table because that is how it imported. This table has
six
fields: fldLastName, fldFirstName, fldFamilyID, fldIndividualID,
fldFamilyPosition, fldSecuityKey (applies to all members in family).
Each
family member is a different record. Hope I covered everything without
being
wordy.

Serendipity

:

If you want help, you're going to have to provide more information!

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Yes, they have a common family number.

:

What do your tables look like? I understand that David would have
head
(or
spouse) and Sherri would have spouse (or head), but is there any
way
to
know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another
record
and
the
Davis is in both records--plus the kids records. Hope that makes
it
more
clear.

:

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you
need
to
repeat the field name:

=IIf([FamilyPosition]="head" Or
[FamilyPosition]="spouse",[FirstName]
& "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " &
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I have a table where each family member is in a separate record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head,
spouse,
child.
I need to make a name tag that gets the dad's name (fldHead)
out
of
one
record and then mom's (fldSpouse) name out of another record
and
then
puts
them together with the fldLastName on the same line. Here is
what
I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " &
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Serendipity]

Line 1 of the name tag will have Family's last name (big and bold)
Line 2 of the name tag will list the names of the children in the
family--each separated by about 5 spaces
Line 3 of the name tag will have Parent(s)' names (dad's name, mom's name, &
last name)
Line 4 of the name tag will have the Family security number

I followed the process and code you sent; however, I will not be back at
work to try it out until Wed. (8/24). How do I keep this from getting dropped
in case I have another question? It has been my experience that if there has
been no "back and forth" for a couple of days it "dies." How does a newgroup
work in that regard.

Thanks so much.
Serendipity

I'm still confused as to what has to appear on the name tag.

To get the names of the parents, you can create and save 2 intermediary
queries, one that returns just the family heads (call it qryFamilyHead), and
one that returns just the spouses (call it qryFamilySpouse). Make sure you
include fldSecurityKey in each query.

Given your example, qryFamilyHead should return:
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Jennifer Berry head 214
David Jones head 506

and qryFamilySpouse should return
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Cheryl Jones spouse 506

Now, create a query that joins those two queries together using a Left Join.
Drag qryFamilyHead in the query builder, then drag qryFamilySpouse. Drag
fldSecurityKey from qryFamilyHead to qryFamilySpouse so that a line appears
between the two tables. Select that line, right-click to get the Join
Properties option, and select the 2nd option ("Include ALL records from
'qryFamilyHead' and only those records from 'qryFamilySpouse where the
joined fields are equal.") Drag the fldFirstName, fldLastName and
fldSecurityKey fields from qryFamilyHead into the grid, and fldFirstName and
fldLastName from qryFamilySpouse. Where you see fldFirstName and fldLastName
from qryFamilyHead in the grid, type aliases in front ot the field names so
that you have HeadFirstName: fldFirstName and HeadLastName: fldLastName. Do
the same with the fields from qryFamilySpouse (so that you have
SpouseFirstName: fldFirstName and SpouseLastName: fldLastName) Run the
query. You should get:

HeadFirstName HeadLastName fldSecurityKey SpouseFirstName SpouseLastName
Jennifer Berry 214
David Jones 506 Cheryl
Jones

The row for Jennifer will actually have Null values for SpouseFirstName and
SpouseLastName.

Now you can join the names together.

You can either do this in the query itself, or in the report. To be totally
flexible, you should probably compare HeadLastName to SpouseLastName, so
that you can print Mark Black & Cheryl Smith rather than Mark & Cheryl
Black. The logic would be something like:

IIf(IsNullI([SpouseLastName]), [HeadFirstName] & " " & [HeadLastName],
IIf([SpouseLastName] = [HeadLastName]), [HeadFirstName] & " & " &
[SpouseFirstName] & " " & [HeadLastName], [HeadFirstName] & " " &
[HeadLastName & " & " [SpouseFirstName] & " " & [SpouseLastName]))

Post back if you need the children names to appear on the tags as well.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for hanging in there to help me with this.
This is what an unformatted name tag would look like. Each line accurately
illustrates what would be on each line of the name tab--minus the comment.

Jones (family last name)
Jeremy Julie Amy (they have three kids)
David and Cheryl Jones (that is the parents first names and last name)
506 (Family security number for daycare)

Legitimate value for fldFamilyPosition are: head (dad or single mom),
spouse
(mom), and child

Here are the records for a single-mom family:
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Jennifer Berry head 214
(mom)
Jason Berry child 214
(child)
Chloe Berry child 214
(child)
Each line (family member) is a separate record. Additionally, there are
field in each record for family number and individual number.

Here are the records for a two-parent family:
David Jones head 506
(dad)
Cheryl Jones spouse 506
(mom)
Jeremy Jones child 506
(child)
Julie Jones child 506
(child)
Amy Jones child 506
(child)
Each family member is entered in a separate record. (imported)

Only parents with children in daycare will get a name tag. They present
the
name tag in order to pick up their children. So only families with kids
are
in this table.

The thing I can't figure out is how to make it work because each family
member is in a separate record.

Did my best. Hope it makes sense.

Serendipity

Actually, a few more pieces of information.

Are you only trying to get name tags for the adults, or for the kids as
well?

What are the legitimate values for fldFamilyPosition? I'm assuming this
is
where head and spouse will be: what else? Also, if you've got a single
adult, what will that value be?

Pasting, say, 10 rows of data (showing a couple with kids, a couple with
no
kids, a single parent with kids and an adult with no family) would be
useful. For the data you post, show what name tags you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



There is only one table because that is how it imported. This table has
six
fields: fldLastName, fldFirstName, fldFamilyID, fldIndividualID,
fldFamilyPosition, fldSecuityKey (applies to all members in family).
Each
family member is a different record. Hope I covered everything without
being
wordy.

Serendipity

:

If you want help, you're going to have to provide more information!

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Yes, they have a common family number.

:

What do your tables look like? I understand that David would have
head
(or
spouse) and Sherri would have spouse (or head), but is there any
way
to
know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another
record
and
the
Davis is in both records--plus the kids records. Hope that makes
it
more
clear.

:

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect: you
need
to
repeat the field name:

=IIf([FamilyPosition]="head" Or
[FamilyPosition]="spouse",[FirstName]
& "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " " &
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I have a table where each family member is in a separate record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e., head,
spouse,
child.
I need to make a name tag that gets the dad's name (fldHead)
out
of
one
record and then mom's (fldSpouse) name out of another record
and
then
puts
them together with the fldLastName on the same line. Here is
what
I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " " &
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Douglas J. Steele]

I try to check if there are reposts to replies that I've sent, but I don't
always succeed. If you do post a followup and I miss it, just make a brand
new post, with "REPOST" in the subject. (Don't bother putting my name in:
others are just as capable of helping!)

However, to get the names of the children on one line, you're going to have
to use VBA: it's not really possible to simply use a query.

The code in http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" is the
sort of code you need, although you'll have to customize it slightly so as
only to retrieve those records with "child" as the type. Hopefully you can
figure it out: if not, feel free to post back.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Line 1 of the name tag will have Family's last name (big and bold)
Line 2 of the name tag will list the names of the children in the
family--each separated by about 5 spaces
Line 3 of the name tag will have Parent(s)' names (dad's name, mom's name,
&
last name)
Line 4 of the name tag will have the Family security number

I followed the process and code you sent; however, I will not be back at
work to try it out until Wed. (8/24). How do I keep this from getting
dropped
in case I have another question? It has been my experience that if there
has
been no "back and forth" for a couple of days it "dies." How does a
newgroup
work in that regard.

Thanks so much.
Serendipity

I'm still confused as to what has to appear on the name tag.

To get the names of the parents, you can create and save 2 intermediary
queries, one that returns just the family heads (call it qryFamilyHead),
and
one that returns just the spouses (call it qryFamilySpouse). Make sure
you
include fldSecurityKey in each query.

Given your example, qryFamilyHead should return:
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Jennifer Berry head 214
David Jones head 506

and qryFamilySpouse should return
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Cheryl Jones spouse 506

Now, create a query that joins those two queries together using a Left
Join.
Drag qryFamilyHead in the query builder, then drag qryFamilySpouse. Drag
fldSecurityKey from qryFamilyHead to qryFamilySpouse so that a line
appears
between the two tables. Select that line, right-click to get the Join
Properties option, and select the 2nd option ("Include ALL records from
'qryFamilyHead' and only those records from 'qryFamilySpouse where the
joined fields are equal.") Drag the fldFirstName, fldLastName and
fldSecurityKey fields from qryFamilyHead into the grid, and fldFirstName
and
fldLastName from qryFamilySpouse. Where you see fldFirstName and
fldLastName
from qryFamilyHead in the grid, type aliases in front ot the field names
so
that you have HeadFirstName: fldFirstName and HeadLastName: fldLastName.
Do
the same with the fields from qryFamilySpouse (so that you have
SpouseFirstName: fldFirstName and SpouseLastName: fldLastName) Run the
query. You should get:

HeadFirstName HeadLastName fldSecurityKey SpouseFirstName
SpouseLastName
Jennifer Berry 214
David Jones 506 Cheryl
Jones

The row for Jennifer will actually have Null values for SpouseFirstName
and
SpouseLastName.

Now you can join the names together.

You can either do this in the query itself, or in the report. To be
totally
flexible, you should probably compare HeadLastName to SpouseLastName, so
that you can print Mark Black & Cheryl Smith rather than Mark & Cheryl
Black. The logic would be something like:

IIf(IsNullI([SpouseLastName]), [HeadFirstName] & " " & [HeadLastName],
IIf([SpouseLastName] = [HeadLastName]), [HeadFirstName] & " & " &
[SpouseFirstName] & " " & [HeadLastName], [HeadFirstName] & " " &
[HeadLastName & " & " [SpouseFirstName] & " " & [SpouseLastName]))

Post back if you need the children names to appear on the tags as well.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for hanging in there to help me with this.
This is what an unformatted name tag would look like. Each line
accurately
illustrates what would be on each line of the name tab--minus the
comment.

Jones (family last name)
Jeremy Julie Amy (they have three kids)
David and Cheryl Jones (that is the parents first names and last name)
506 (Family security number for daycare)

Legitimate value for fldFamilyPosition are: head (dad or single mom),
spouse
(mom), and child

Here are the records for a single-mom family:
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Jennifer Berry head 214
(mom)
Jason Berry child 214
(child)
Chloe Berry child 214
(child)
Each line (family member) is a separate record. Additionally, there are
field in each record for family number and individual number.

Here are the records for a two-parent family:
David Jones head 506
(dad)
Cheryl Jones spouse 506
(mom)
Jeremy Jones child 506
(child)
Julie Jones child 506
(child)
Amy Jones child 506
(child)
Each family member is entered in a separate record. (imported)

Only parents with children in daycare will get a name tag. They present
the
name tag in order to pick up their children. So only families with kids
are
in this table.

The thing I can't figure out is how to make it work because each family
member is in a separate record.

Did my best. Hope it makes sense.

Serendipity


:

Actually, a few more pieces of information.

Are you only trying to get name tags for the adults, or for the kids
as
well?

What are the legitimate values for fldFamilyPosition? I'm assuming
this
is
where head and spouse will be: what else? Also, if you've got a single
adult, what will that value be?

Pasting, say, 10 rows of data (showing a couple with kids, a couple
with
no
kids, a single parent with kids and an adult with no family) would be
useful. For the data you post, show what name tags you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



There is only one table because that is how it imported. This table
has
six
fields: fldLastName, fldFirstName, fldFamilyID, fldIndividualID,
fldFamilyPosition, fldSecuityKey (applies to all members in family).
Each
family member is a different record. Hope I covered everything
without
being
wordy.

Serendipity

:

If you want help, you're going to have to provide more information!

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Yes, they have a common family number.

:

What do your tables look like? I understand that David would
have
head
(or
spouse) and Sherri would have spouse (or head), but is there any
way
to
know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another
record
and
the
Davis is in both records--plus the kids records. Hope that
makes
it
more
clear.

:

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect:
you
need
to
repeat the field name:

=IIf([FamilyPosition]="head" Or
[FamilyPosition]="spouse",[FirstName]
& "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " "
&
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



in
message
I have a table where each family member is in a separate
record
(imported
from another program that has it that way). There is a
fldFamilyIDNumber
field. Each record has a fldFamilyPosition field, i.e.,
head,
spouse,
child.
I need to make a name tag that gets the dad's name
(fldHead)
out
of
one
record and then mom's (fldSpouse) name out of another
record
and
then
puts
them together with the fldLastName on the same line. Here
is
what
I
have
tried so far
=IIf([FamilyPosition]="head" Or "spouse",[FirstName] & " "
&
[LastName],"
")
This, of course, lists them on separate lines.
Thanks in advance for your help!
Serendipity

 

[Serendipity]

Thank you so much for your help. I have hope of getting this to work. If not,
I will repost after Tuesday.

I try to check if there are reposts to replies that I've sent, but I don't
always succeed. If you do post a followup and I miss it, just make a brand
new post, with "REPOST" in the subject. (Don't bother putting my name in:
others are just as capable of helping!)

However, to get the names of the children on one line, you're going to have
to use VBA: it's not really possible to simply use a query.

The code in http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" is the
sort of code you need, although you'll have to customize it slightly so as
only to retrieve those records with "child" as the type. Hopefully you can
figure it out: if not, feel free to post back.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Line 1 of the name tag will have Family's last name (big and bold)
Line 2 of the name tag will list the names of the children in the
family--each separated by about 5 spaces
Line 3 of the name tag will have Parent(s)' names (dad's name, mom's name,
&
last name)
Line 4 of the name tag will have the Family security number

I followed the process and code you sent; however, I will not be back at
work to try it out until Wed. (8/24). How do I keep this from getting
dropped
in case I have another question? It has been my experience that if there
has
been no "back and forth" for a couple of days it "dies." How does a
newgroup
work in that regard.

Thanks so much.
Serendipity

I'm still confused as to what has to appear on the name tag.

To get the names of the parents, you can create and save 2 intermediary
queries, one that returns just the family heads (call it qryFamilyHead),
and
one that returns just the spouses (call it qryFamilySpouse). Make sure
you
include fldSecurityKey in each query.

Given your example, qryFamilyHead should return:
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Jennifer Berry head 214
David Jones head 506

and qryFamilySpouse should return
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Cheryl Jones spouse 506

Now, create a query that joins those two queries together using a Left
Join.
Drag qryFamilyHead in the query builder, then drag qryFamilySpouse. Drag
fldSecurityKey from qryFamilyHead to qryFamilySpouse so that a line
appears
between the two tables. Select that line, right-click to get the Join
Properties option, and select the 2nd option ("Include ALL records from
'qryFamilyHead' and only those records from 'qryFamilySpouse where the
joined fields are equal.") Drag the fldFirstName, fldLastName and
fldSecurityKey fields from qryFamilyHead into the grid, and fldFirstName
and
fldLastName from qryFamilySpouse. Where you see fldFirstName and
fldLastName
from qryFamilyHead in the grid, type aliases in front ot the field names
so
that you have HeadFirstName: fldFirstName and HeadLastName: fldLastName.
Do
the same with the fields from qryFamilySpouse (so that you have
SpouseFirstName: fldFirstName and SpouseLastName: fldLastName) Run the
query. You should get:

HeadFirstName HeadLastName fldSecurityKey SpouseFirstName
SpouseLastName
Jennifer Berry 214
David Jones 506 Cheryl
Jones

The row for Jennifer will actually have Null values for SpouseFirstName
and
SpouseLastName.

Now you can join the names together.

You can either do this in the query itself, or in the report. To be
totally
flexible, you should probably compare HeadLastName to SpouseLastName, so
that you can print Mark Black & Cheryl Smith rather than Mark & Cheryl
Black. The logic would be something like:

IIf(IsNullI([SpouseLastName]), [HeadFirstName] & " " & [HeadLastName],
IIf([SpouseLastName] = [HeadLastName]), [HeadFirstName] & " & " &
[SpouseFirstName] & " " & [HeadLastName], [HeadFirstName] & " " &
[HeadLastName & " & " [SpouseFirstName] & " " & [SpouseLastName]))

Post back if you need the children names to appear on the tags as well.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Thanks for hanging in there to help me with this.
This is what an unformatted name tag would look like. Each line
accurately
illustrates what would be on each line of the name tab--minus the
comment.

Jones (family last name)
Jeremy Julie Amy (they have three kids)
David and Cheryl Jones (that is the parents first names and last name)
506 (Family security number for daycare)

Legitimate value for fldFamilyPosition are: head (dad or single mom),
spouse
(mom), and child

Here are the records for a single-mom family:
fldFirstName fldLastName fldFamilyPosition fldSecurityKey
Jennifer Berry head 214
(mom)
Jason Berry child 214
(child)
Chloe Berry child 214
(child)
Each line (family member) is a separate record. Additionally, there are
field in each record for family number and individual number.

Here are the records for a two-parent family:
David Jones head 506
(dad)
Cheryl Jones spouse 506
(mom)
Jeremy Jones child 506
(child)
Julie Jones child 506
(child)
Amy Jones child 506
(child)
Each family member is entered in a separate record. (imported)

Only parents with children in daycare will get a name tag. They present
the
name tag in order to pick up their children. So only families with kids
are
in this table.

The thing I can't figure out is how to make it work because each family
member is in a separate record.

Did my best. Hope it makes sense.

Serendipity


:

Actually, a few more pieces of information.

Are you only trying to get name tags for the adults, or for the kids
as
well?

What are the legitimate values for fldFamilyPosition? I'm assuming
this
is
where head and spouse will be: what else? Also, if you've got a single
adult, what will that value be?

Pasting, say, 10 rows of data (showing a couple with kids, a couple
with
no
kids, a single parent with kids and an adult with no family) would be
useful. For the data you post, show what name tags you want.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



There is only one table because that is how it imported. This table
has
six
fields: fldLastName, fldFirstName, fldFamilyID, fldIndividualID,
fldFamilyPosition, fldSecuityKey (applies to all members in family).
Each
family member is a different record. Hope I covered everything
without
being
wordy.

Serendipity

:

If you want help, you're going to have to provide more information!

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
Yes, they have a common family number.

:

What do your tables look like? I understand that David would
have
head
(or
spouse) and Sherri would have spouse (or head), but is there any
way
to
know
that they're married to each other?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
The name should look like this:
David and Sherri Davis
But the David is in one record and the Sherri is in another
record
and
the
Davis is in both records--plus the kids records. Hope that
makes
it
more
clear.

:

I'm sorry: I don't understand what your question is.

However, your use of Or in the IIf statement is incorrect:
you
need
to
repeat the field name:

=IIf([FamilyPosition]="head" Or
[FamilyPosition]="spouse",[FirstName]
& "
"
& [LastName]," ")

Alternatively, you could use In:

=IIf([FamilyPosition] In ("head", "spouse"),[FirstName] & " "
&
[LastName],"
")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



"Serendipity" <[email protected]> wrote