Goup query

  • Thread starter HLCruz via AccessMonster.com
  • Start date
H

HLCruz via AccessMonster.com

I have an extensive mailing list that I'm tyring to normalize somewhat ...
I'd like to create a table with just the addresses and then another table
with the residents' names. We have three kinds of "residents":
1. Married couples or families with the same last name
2. Single individuals
3. Married couples or families WITHOUT the same last name

I've gotten my mailing list query to work so that I can send just ONE item
per address for the first two occurrences but not the last one. Is it
possible in Access to group these residents in a way that would accomodate
those different names - say, Jane Smith and John Doe?

Please let me know if I need to post anymore information to be helpful!!
Thank you ! Heather

SELECT [TEST qryMailingList].MailingID AS GroupID, [TEST qryMailingList].
LastName AS GroupLN, [TEST qryMailingList].Address1 AS GroupAd, [TEST
qryMailingList].Zip AS GroupZip, Count([TEST qryMailingList].LastName) AS
Residents, First([TEST qryMailingList].LastName) AS LastName, First([TEST
qryMailingList].Address1) AS Address1, First([TEST qryMailingList].Address2)
AS Address2, First([TEST qryMailingList].City) AS City, First([TEST
qryMailingList].State) AS State, First([TEST qryMailingList].Zip) AS Zip,
First([TEST qryMailingList].FirstName) AS FirstName
FROM [TEST qryMailingList]
GROUP BY [TEST qryMailingList].MailingID, [TEST qryMailingList].LastName,
[TEST qryMailingList].Address1, [TEST qryMailingList].Zip;

SELECT [TEST qryCountGroup].GroupID, [TEST qryCountGroup].Residents, IIf(
[Residents]>1,"The " & [LastName] & " Family",[FirstName] & " " & [LastName])
AS Label1, [TEST qryCountGroup].Address1, [TEST qryCountGroup].Address2,
[TEST qryCountGroup].City, [TEST qryCountGroup].State, [TEST qryCountGroup].
Zip
FROM [TEST qryCountGroup];
 
J

Jeff Boyce

If you had a table of "FamilyGroup", and another table of
"FamilyGroupMembers", you could associate your addresses with the
FamilyGroupMembers table. You'd need to collect people together into family
groups by putting one record per person-in-a-family-group into that
FamilyGroupMembers.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
H

HLCruz via AccessMonster.com

Jeff,

Thank you for your response, but I think I'm more confused now ... I guess I
thought I was essentially doing this already by having a table of addresses
which identifies the "group" and a table of the people in the "group" or
family members. I'm not sure I understand what that third table of
"FamilyGroup" would allow me to do differently... would you mind explaining a
little further?

Thanks,
Heather

Jeff said:
If you had a table of "FamilyGroup", and another table of
"FamilyGroupMembers", you could associate your addresses with the
FamilyGroupMembers table. You'd need to collect people together into family
groups by putting one record per person-in-a-family-group into that
FamilyGroupMembers.

Regards

Jeff Boyce
Microsoft Office/Access MVP
I have an extensive mailing list that I'm tyring to normalize somewhat ...
I'd like to create a table with just the addresses and then another table
[quoted text clipped - 32 lines]
Zip
FROM [TEST qryCountGroup];
 
J

Jeff Boyce

I may not have understood the data structure you already have...

My idea of a FamilyGroup table would be to provide a unique identifier to
connect to a specific address. The addresses would not be connected to
persons, but to this FamilyGroup.

And who is "in" a particular FamilyGroup? That's what the FamilyGroupMember
table holds, valid pairs of Person and FamilyGroup.

Regards

Jeff Boyce
Microsoft Office/Access MVP


HLCruz via AccessMonster.com said:
Jeff,

Thank you for your response, but I think I'm more confused now ... I guess
I
thought I was essentially doing this already by having a table of
addresses
which identifies the "group" and a table of the people in the "group" or
family members. I'm not sure I understand what that third table of
"FamilyGroup" would allow me to do differently... would you mind
explaining a
little further?

Thanks,
Heather

Jeff said:
If you had a table of "FamilyGroup", and another table of
"FamilyGroupMembers", you could associate your addresses with the
FamilyGroupMembers table. You'd need to collect people together into
family
groups by putting one record per person-in-a-family-group into that
FamilyGroupMembers.

Regards

Jeff Boyce
Microsoft Office/Access MVP
I have an extensive mailing list that I'm tyring to normalize somewhat
...
I'd like to create a table with just the addresses and then another
table
[quoted text clipped - 32 lines]
Zip
FROM [TEST qryCountGroup];
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top