Creating a new field from other data

[Guest]

I have a list of registered voters in my area that I need to modify into a
mail merge.

The problem is that each voting member of a household is listed and I only
need to send one card to each household.

While I would prefer to use "Mr and Mrs" there are enough 3 or more person
households that this will not work. So, how can I substitute " {last_name}
Houseold in only one record and ignore the others?

Thanks in advance.

 

[Allen Browne]

First issue is deciding exactly how you define household. For this example,
I will assume you it is defined as identical data in ALL these fields:
- Surname
- Address
- Zip

1. Create a query into this table.

2. Depress the Totals button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the 3 fields above into the grid.
Accept Group By in the total row.

4. In a fresh column in the Field row, type this expression:
Addressee: IIf(Count([ID])=1, Trim([FirstName] & " " & [Surname]),
"The " & [Surname] & " Household"
Replace "ID" with the name of your primary key field.
In the Total row under this field, choose: Expression.

5. Drag other fields you want into the grid, e.g. City, State, ...
In the Total row under these fields, choose: First.

The query returns the 3 fields you used to define "duplicate", then the
Addressee field which is the individual (if only one) or household (if more
than one), then fields like FirstOfCity, FirstOfState for the fields you
want to include that are not part of how "duplicate" is defined.

 

[Guest]

Allen,

Thanks, I'll try it tomorrow and let you know what happens.

But I may decide to be "greedy" could I define "household" as more than two
people with the same last name? And therefore use and IF statement to define
two people as "Mr and Mrs" and 3 or more as the household name?

--
John

First issue is deciding exactly how you define household. For this example,
I will assume you it is defined as identical data in ALL these fields:
- Surname
- Address
- Zip

1. Create a query into this table.

2. Depress the Totals button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the 3 fields above into the grid.
Accept Group By in the total row.

4. In a fresh column in the Field row, type this expression:
Addressee: IIf(Count([ID])=1, Trim([FirstName] & " " & [Surname]),
"The " & [Surname] & " Household"
Replace "ID" with the name of your primary key field.
In the Total row under this field, choose: Expression.

5. Drag other fields you want into the grid, e.g. City, State, ...
In the Total row under these fields, choose: First.

The query returns the 3 fields you used to define "duplicate", then the
Addressee field which is the individual (if only one) or household (if more
than one), then fields like FirstOfCity, FirstOfState for the fields you
want to include that are not part of how "duplicate" is defined.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a list of registered voters in my area that I need to modify into a
mail merge.

The problem is that each voting member of a household is listed and I only
need to send one card to each household.

While I would prefer to use "Mr and Mrs" there are enough 3 or more person
households that this will not work. So, how can I substitute " {last_name}
Houseold in only one record and ignore the others?

Thanks in advance.

 

[Allen Browne]

You can nest another IIf() inside that IIf() if you want to make the
expression more complex. There's lots of possible scenarios though, such as
a mother and son who may not like being addressed as Mr and Mrs.

Realistically, this approach is just a quick'n'dirty solution. If you are
interested in doing it properly, you might define the individual clients or
the household as members of the mailing list(?). For an example of how to
take that approach, download the sample database from this article:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, I'll try it tomorrow and let you know what happens.

But I may decide to be "greedy" could I define "household" as more than
two
people with the same last name? And therefore use and IF statement to
define
two people as "Mr and Mrs" and 3 or more as the household name?

--
John

First issue is deciding exactly how you define household. For this
example,
I will assume you it is defined as identical data in ALL these fields:
- Surname
- Address
- Zip

1. Create a query into this table.

2. Depress the Totals button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the 3 fields above into the grid.
Accept Group By in the total row.

4. In a fresh column in the Field row, type this expression:
Addressee: IIf(Count([ID])=1, Trim([FirstName] & " " & [Surname]),
"The " & [Surname] & " Household"
Replace "ID" with the name of your primary key field.
In the Total row under this field, choose: Expression.

5. Drag other fields you want into the grid, e.g. City, State, ...
In the Total row under these fields, choose: First.

The query returns the 3 fields you used to define "duplicate", then the
Addressee field which is the individual (if only one) or household (if
more
than one), then fields like FirstOfCity, FirstOfState for the fields you
want to include that are not part of how "duplicate" is defined.

I have a list of registered voters in my area that I need to modify into
a
mail merge.

The problem is that each voting member of a household is listed and I
only
need to send one card to each household.

While I would prefer to use "Mr and Mrs" there are enough 3 or more
person
households that this will not work. So, how can I substitute "
{last_name}
Houseold in only one record and ignore the others?

Thanks in advance.

 

[Guest]

Allen,

Here is the SQL you asked for.

SELECT Table1.Last, First(Table1.[Street Number]) AS [FirstOfStreet Number],
First(Table1.Street) AS FirstOfStreet, First(Table1.Apartment) AS
FirstOfApartment, First(Table1.City) AS FirstOfCity, First(Table1.State) AS
FirstOfState, First(Table1.[Zip Code]) AS [FirstOfZip Code], IIf(Count([Last
Name])=1,Trim([First Name] & " " & [Last Name]),"The " & [Last Name] & "
Household") AS Addressee
FROM Table1
GROUP BY Table1.Last;

The "Addressee" Field contains "The Household" as the content. The duplicate
people are gone, though the first name of the one left is there.

Is my spacing off?

Thanks,
--
John

You can nest another IIf() inside that IIf() if you want to make the
expression more complex. There's lots of possible scenarios though, such as
a mother and son who may not like being addressed as Mr and Mrs.

Realistically, this approach is just a quick'n'dirty solution. If you are
interested in doing it properly, you might define the individual clients or
the household as members of the mailing list(?). For an example of how to
take that approach, download the sample database from this article:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, I'll try it tomorrow and let you know what happens.

But I may decide to be "greedy" could I define "household" as more than
two
people with the same last name? And therefore use and IF statement to
define
two people as "Mr and Mrs" and 3 or more as the household name?

--
John

First issue is deciding exactly how you define household. For this
example,
I will assume you it is defined as identical data in ALL these fields:
- Surname
- Address
- Zip

1. Create a query into this table.

2. Depress the Totals button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the 3 fields above into the grid.
Accept Group By in the total row.

4. In a fresh column in the Field row, type this expression:
Addressee: IIf(Count([ID])=1, Trim([FirstName] & " " & [Surname]),
"The " & [Surname] & " Household"
Replace "ID" with the name of your primary key field.
In the Total row under this field, choose: Expression.

5. Drag other fields you want into the grid, e.g. City, State, ...
In the Total row under these fields, choose: First.

The query returns the 3 fields you used to define "duplicate", then the
Addressee field which is the individual (if only one) or household (if
more
than one), then fields like FirstOfCity, FirstOfState for the fields you
want to include that are not part of how "duplicate" is defined.

I have a list of registered voters in my area that I need to modify into
a
mail merge.

The problem is that each voting member of a household is listed and I
only
need to send one card to each household.

While I would prefer to use "Mr and Mrs" there are enough 3 or more
person
households that this will not work. So, how can I substitute "
{last_name}
Houseold in only one record and ignore the others?

Thanks in advance.

 

[Guest]

Allen,

I just noticed that there are missing names. We have two people, unrelated,
named Roberts living in the building.

What I need to include in the query are building number and apartment
number. One Roberts lives in 37B, the other in 39C. Also, a friend's divorced
daughter is using her maiden name but lives in a different apartment. She's
been eliminated.
--
John

Allen,

Here is the SQL you asked for.

SELECT Table1.Last, First(Table1.[Street Number]) AS [FirstOfStreet Number],
First(Table1.Street) AS FirstOfStreet, First(Table1.Apartment) AS
FirstOfApartment, First(Table1.City) AS FirstOfCity, First(Table1.State) AS
FirstOfState, First(Table1.[Zip Code]) AS [FirstOfZip Code], IIf(Count([Last
Name])=1,Trim([First Name] & " " & [Last Name]),"The " & [Last Name] & "
Household") AS Addressee
FROM Table1
GROUP BY Table1.Last;

The "Addressee" Field contains "The Household" as the content. The duplicate
people are gone, though the first name of the one left is there.

Is my spacing off?

Thanks,
--
John

You can nest another IIf() inside that IIf() if you want to make the
expression more complex. There's lots of possible scenarios though, such as
a mother and son who may not like being addressed as Mr and Mrs.

Realistically, this approach is just a quick'n'dirty solution. If you are
interested in doing it properly, you might define the individual clients or
the household as members of the mailing list(?). For an example of how to
take that approach, download the sample database from this article:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, I'll try it tomorrow and let you know what happens.

But I may decide to be "greedy" could I define "household" as more than
two
people with the same last name? And therefore use and IF statement to
define
two people as "Mr and Mrs" and 3 or more as the household name?

--
John


:

First issue is deciding exactly how you define household. For this
example,
I will assume you it is defined as identical data in ALL these fields:
- Surname
- Address
- Zip

1. Create a query into this table.

2. Depress the Totals button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the 3 fields above into the grid.
Accept Group By in the total row.

4. In a fresh column in the Field row, type this expression:
Addressee: IIf(Count([ID])=1, Trim([FirstName] & " " & [Surname]),
"The " & [Surname] & " Household"
Replace "ID" with the name of your primary key field.
In the Total row under this field, choose: Expression.

5. Drag other fields you want into the grid, e.g. City, State, ...
In the Total row under these fields, choose: First.

The query returns the 3 fields you used to define "duplicate", then the
Addressee field which is the individual (if only one) or household (if
more
than one), then fields like FirstOfCity, FirstOfState for the fields you
want to include that are not part of how "duplicate" is defined.

I have a list of registered voters in my area that I need to modify into
a
mail merge.

The problem is that each voting member of a household is listed and I
only
need to send one card to each household.

While I would prefer to use "Mr and Mrs" there are enough 3 or more
person
households that this will not work. So, how can I substitute "
{last_name}
Houseold in only one record and ignore the others?

Thanks in advance.

 

[Allen Browne]

You have used Group By under the Last name only.
You need to use Group By under the other fields that define a duplicate.

For example, use Group By under Apartment, Street, and Zip.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I just noticed that there are missing names. We have two people,
unrelated,
named Roberts living in the building.

What I need to include in the query are building number and apartment
number. One Roberts lives in 37B, the other in 39C. Also, a friend's
divorced
daughter is using her maiden name but lives in a different apartment.
She's
been eliminated.
--
John

Allen,

Here is the SQL you asked for.

SELECT Table1.Last, First(Table1.[Street Number]) AS [FirstOfStreet
Number],
First(Table1.Street) AS FirstOfStreet, First(Table1.Apartment) AS
FirstOfApartment, First(Table1.City) AS FirstOfCity, First(Table1.State)
AS
FirstOfState, First(Table1.[Zip Code]) AS [FirstOfZip Code],
IIf(Count([Last
Name])=1,Trim([First Name] & " " & [Last Name]),"The " & [Last Name] & "
Household") AS Addressee
FROM Table1
GROUP BY Table1.Last;

The "Addressee" Field contains "The Household" as the content. The
duplicate
people are gone, though the first name of the one left is there.

Is my spacing off?

Thanks,
--
John

You can nest another IIf() inside that IIf() if you want to make the
expression more complex. There's lots of possible scenarios though,
such as
a mother and son who may not like being addressed as Mr and Mrs.

Realistically, this approach is just a quick'n'dirty solution. If you
are
interested in doing it properly, you might define the individual
clients or
the household as members of the mailing list(?). For an example of how
to
take that approach, download the sample database from this article:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html


Thanks, I'll try it tomorrow and let you know what happens.

But I may decide to be "greedy" could I define "household" as more
than
two
people with the same last name? And therefore use and IF statement to
define
two people as "Mr and Mrs" and 3 or more as the household name?

--
John


:

First issue is deciding exactly how you define household. For this
example,
I will assume you it is defined as identical data in ALL these
fields:
- Surname
- Address
- Zip

1. Create a query into this table.

2. Depress the Totals button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

3. Drag the 3 fields above into the grid.
Accept Group By in the total row.

4. In a fresh column in the Field row, type this expression:
Addressee: IIf(Count([ID])=1, Trim([FirstName] & " " &
[Surname]),
"The " & [Surname] & " Household"
Replace "ID" with the name of your primary key field.
In the Total row under this field, choose: Expression.

5. Drag other fields you want into the grid, e.g. City, State, ...
In the Total row under these fields, choose: First.

The query returns the 3 fields you used to define "duplicate", then
the
Addressee field which is the individual (if only one) or household
(if
more
than one), then fields like FirstOfCity, FirstOfState for the fields
you
want to include that are not part of how "duplicate" is defined.

I have a list of registered voters in my area that I need to modify
into
a
mail merge.

The problem is that each voting member of a household is listed
and I
only
need to send one card to each household.

While I would prefer to use "Mr and Mrs" there are enough 3 or
more
person
households that this will not work. So, how can I substitute "
{last_name}
Houseold in only one record and ignore the others?

Thanks in advance.

 

[Guest]

I did something similar to this not too long ago. This might help:

Salutation: IIf(IsNull([last2]),[first1] & " " &
[last1],IIf([last2]=[last1],[first1] & " and " & [first2] & " " &
[last1],[first1] & " " & [last1] & " and " & [first2] & " " & [last2]))

My table is set up with fields First1, Last1, First2, and Last2. It's easy
enough to add additional people with the IIf statements, or middle initials,
or whatever. You just have to write the IIfs out on paper first - it tends
to get a little messy. As you can probably tell.

Hope it helps!

Nick