Combining records

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

I am combining a husband and wife's record by grouping on
a family number(which is my primary key). How do I combine
their email addresses into one field?
 
Dear Rebecca:

It seems to me there's a bit to this problem.

First, is it the case that not everyone in the table is married? Will
you be showing only those who are married in this query?

Next, it seems likely that not everyone will have an email address.
One or both may be missing, right? Perhaps you would want (H) or (W)
with the email addresses so you can tell which is which, and so you
can tell wether it is the husband's or the wife's email address when
only one is present.

Anyway, you can start out with a calculated field that concatenates
the two. I'll include adding (H) and (W) to this to identify them.

I'm assuming there are two instances of your table in this query, and
that the Husband instance is aliased H and the Wife instance W.

CombinedEMail: Nz("(H)" & H.EMail, "") & Nz("(W)" & W.EMail, "")

If you have difficulty implementing this, please post your SQL here
and I'll modify it for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

You're right. There is a bit to the problem, but I figured
I would give more details if someone responded. You were
correct that not everyone is married, not everyone has an
email address, and one or both may be missing.

Thank you so much for your assistance. Your query gave me
the headstart I needed and was able to fine tune it to
account for identical email addresses, different email
addresses, only one spouse having an email, or no emails.

As far as assuming there were two instances of the table,
there was only one instance. The original table had two
primary key fields (the family number and the individual
number) and one email field. However, because of your
assumption, I created two queries: one for "husband"
emails (or head of household if single)and one for wife
emails. I then created the combining query using the two
email queries and a query containing the grouped family
numbers.

I was able to combine the emails using the following
code:

CombinedEMail: IIf(Nz([HEMail])<>"",IIf(Nz([SEMail])
<>"",IIf([HEMail]=[SEMail],[HEMail],[HEMail] & ", " &
[SEMail]),[HEMail]),IIf(Nz([SEMail])<>"",[SEMail],""))

Thanks again for your assistance!
 
Dear Rebecca:

I'm glad you got some help from my response.

As far as there being "two instances of the table" I was referring to
the query. The query references the table twice, once for husband and
once for wife. That's the way I built the query. I really did not
mean you had two tables.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

You're right. There is a bit to the problem, but I figured
I would give more details if someone responded. You were
correct that not everyone is married, not everyone has an
email address, and one or both may be missing.

Thank you so much for your assistance. Your query gave me
the headstart I needed and was able to fine tune it to
account for identical email addresses, different email
addresses, only one spouse having an email, or no emails.

As far as assuming there were two instances of the table,
there was only one instance. The original table had two
primary key fields (the family number and the individual
number) and one email field. However, because of your
assumption, I created two queries: one for "husband"
emails (or head of household if single)and one for wife
emails. I then created the combining query using the two
email queries and a query containing the grouped family
numbers.

I was able to combine the emails using the following
code:

CombinedEMail: IIf(Nz([HEMail])<>"",IIf(Nz([SEMail])
<>"",IIf([HEMail]=[SEMail],[HEMail],[HEMail] & ", " &
[SEMail]),[HEMail]),IIf(Nz([SEMail])<>"",[SEMail],""))

Thanks again for your assistance!
-----Original Message-----
Dear Rebecca:

It seems to me there's a bit to this problem.

First, is it the case that not everyone in the table is married? Will
you be showing only those who are married in this query?

Next, it seems likely that not everyone will have an email address.
One or both may be missing, right? Perhaps you would want (H) or (W)
with the email addresses so you can tell which is which, and so you
can tell wether it is the husband's or the wife's email address when
only one is present.

Anyway, you can start out with a calculated field that concatenates
the two. I'll include adding (H) and (W) to this to identify them.

I'm assuming there are two instances of your table in this query, and
that the Husband instance is aliased H and the Wife instance W.

CombinedEMail: Nz("(H)" & H.EMail, "") & Nz("(W)" & W.EMail, "")

If you have difficulty implementing this, please post your SQL here
and I'll modify it for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.
Click to expand...
Click to expand...
 
Tom,

I kind of thought that is what you probably meant about
the two instances, and that was exactly what I needed.

Thanks again.
-----Original Message-----
Dear Rebecca:

I'm glad you got some help from my response.

As far as there being "two instances of the table" I was referring to
the query. The query references the table twice, once for husband and
once for wife. That's the way I built the query. I really did not
mean you had two tables.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

You're right. There is a bit to the problem, but I figured
I would give more details if someone responded. You were
correct that not everyone is married, not everyone has an
email address, and one or both may be missing.

Thank you so much for your assistance. Your query gave me
the headstart I needed and was able to fine tune it to
account for identical email addresses, different email
addresses, only one spouse having an email, or no emails.

As far as assuming there were two instances of the table,
there was only one instance. The original table had two
primary key fields (the family number and the individual
number) and one email field. However, because of your
assumption, I created two queries: one for "husband"
emails (or head of household if single)and one for wife
emails. I then created the combining query using the two
email queries and a query containing the grouped family
numbers.

I was able to combine the emails using the following
code:

CombinedEMail: IIf(Nz([HEMail])<>"",IIf(Nz([SEMail])
<>"",IIf([HEMail]=[SEMail],[HEMail],[HEMail] & ", " &
[SEMail]),[HEMail]),IIf(Nz([SEMail])<>"",[SEMail],""))

Thanks again for your assistance!
-----Original Message-----
Dear Rebecca:

It seems to me there's a bit to this problem.

First, is it the case that not everyone in the table is married? Will
you be showing only those who are married in this query?

Next, it seems likely that not everyone will have an email address.
One or both may be missing, right? Perhaps you would want (H) or (W)
with the email addresses so you can tell which is
Click to expand...
which,
and so you
can tell wether it is the husband's or the wife's email address when
only one is present.

Anyway, you can start out with a calculated field that concatenates
the two. I'll include adding (H) and (W) to this to identify them.

I'm assuming there are two instances of your table in this query, and
that the Husband instance is aliased H and the Wife instance W.

CombinedEMail: Nz("(H)" & H.EMail, "") & Nz("(W)" & W.EMail, "")

If you have difficulty implementing this, please post your SQL here
and I'll modify it for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Wed, 28 Jul 2004 08:55:17 -0700, "Rebecca"

I am combining a husband and wife's record by grouping on
a family number(which is my primary key). How do I combine
their email addresses into one field?

.
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top