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];
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];