Combining Voters from the same household.

[Mo Man]

Hi,

I have a database of all registered voters in my town. Each voter is
represented in the database as a single record. The database is design
in the following way:

Fields Description
ID Unique ID
FirstName First Name
LastName Last Name
ResAddr Address
ResCity City
ResState State
ResZip10 Zipcode
MailAddr Mailing Address
MailCity Mailing City
MailState Mailing State
MailZip10 Mailing Zipcode
Phone1 Phone Number
Precinct Precinct Number
RegPartyID Registrant Party Code = d democratic r =
republican etc

RegPartyDesc Registrant Party Description
FamilyPartyID Family Party Code 1 democratic 2 democratic &
independent 3 = democratic & republican ... 7 etc
FamilyPartyDesc Family Party Description

What I am trying to do is to create labels for each household based on
Party affiliation. Example; If every voter in the household is "d"
than create a label "The {LastName} Household" without creating a
duplicate label for the household. If not, than create an individual
label based on the party affiliation.

I have been trying to figure this out for the past few days without any
luck. I know enough about access to do some basic stuff and when to
ask for expert help. And this is the time for some expert help!

Any help would be appreciated.

Many thanks

Mo

 

[Rick B]

One post per issue please. You don't need three people spending their time
to answer your question only to find it already answered in another
newsgroup.

 

[Mo Man]

I apologize for this. I wasn't sure which group to ask for help and
my intent wasn't to create unnecessary work. I hope that my blunder
doesn't warrant a non-reply.

Mo

 

[Maury Markowitz]

I have a database of all registered voters in my town. Each voter is
represented in the database as a single record.

Suggestion: in every single database I have ever seen like this, there are
duplicated records and/or problems with minor spelling that will break
sorting and grouping. I recommend making a report that prints out
lastname,firstname in sorted order so you can check for these problems.

What I am trying to do is to create labels for each household based on
Party affiliation. Example; If every voter in the household is "d"
than create a label "The {LastName} Household" without creating a
duplicate label for the household. If not, than create an individual
label based on the party affiliation.

try this...

SELECT LastName, count(FirstName) FROM theTable GROUP BY LastName, RegPartyID

I think that's the syntax anyway. See if this gets you closer to what you
want. If it does, then you can use MAX or MIN to get the address, or
alternately (and likely much safer) GROUP BY on the address fields too,
like...

SELECT LastName, ResAddr, ResCity, ... GROUP BY LastName, ResAddr, ResCity,...

Maury

 

[Mo Man]

Maury,

Thanks - But what I am trying to do is to concatenate the voters at the
same address onto one label if they are all in the same party. And
without cause duplicate labels. So if Address 123 Main Street has 3
voters that are all 'd' then I would address the label to: The
[Lastname] Household otherwise I would address the label to the voter..
I hope this helps.

Mo