Oh boy, we are almost there!
After lots of experimenting and working with what you said (I am a newbie) I
was able to get the query to work without errors and the system stopped
asking me to debug.
I created a report based on the query and it is concatenating the names,
however the report is making labels for every record.
For example: let's say there are three people at an address. I now have
three identical labels for that address with the concatenated names.
Is there a solution for this?
Thanks for your help!
:
The SQL that you posted doesn't include the Concatenate() function. Try this
SQL
SELECT Concatenate("SELECT [Index Name] FROM tblCustomerAddresses
WHERE [Mail Address 1] & [Mail Address 2] & [Mail City] & [Mail State] &
[Mail Zip] = """ & [Mail Address 1] & [Mail Address 2] & [Mail City] & [Mail
State] & [Mail Zip] & """", " and ") as IndexNames ,
[Mail Address 1], [Mail Address 2], [Mail City], [Mail State], [Mail Zip]
FROM tblCustomerAddresses
GROUP BY Concatenate("SELECT [Index Name] FROM tblCustomerAddresses
WHERE [Mail Address 1] & [Mail Address 2] & [Mail City] & [Mail State] &
[Mail Zip] = """ & [Mail Address 1] & [Mail Address 2] & [Mail City] & [Mail
State] & [Mail Zip] & """", " and "), [Mail Address 2], [Mail City], [Mail
State];
Watch out for line wrapping issues.
--
Duane Hookom
Microsoft Access MVP
:
Oh, I should mention that I have been deleting all the code that Access
created and replacing it with your code. I did try pasting it below what
Access created but it gets angry at me for having characters after the end of
the SQL statement.
This is so intriguing! Thank you for your assistance!
:
Please reply with the SQL view of your query as well as the data types of the
significant fields.
--
Duane Hookom
Microsoft Access MVP
:
Thanks Duane, I am learning something new at every turn!
I now have the module from the sample saved within my database as a new
module named "modStringFunctions". However, I am still getting the error
even after closing out of Access and re-opening the program and the database.
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.
I appreciate all your help and I hope you are still interested in helping me
crack this thing...
:
Do you have a standard module in your MDB that contains the Concatenate()
function? If not, import the one from the sample or copy and paste the
function code into a new or existing module. Make sure the name of the module
is not the name of any function. IOW, name your module something like
"modStringFunctions".
--
Duane Hookom
Microsoft Access MVP
:
I copied the code you posted into the SQL view for the query and double
checked that all references to tables/fields/etc were correct...
A box pops up and it says:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'.
Thank you again for your patience and your assistance.
:
Did you copy the module with the concatenate function into your mdb?
--
Duane Hookom
Microsoft Access MVP
:
Hi Duane,
Thank you so much for your help. I have been working with this since you
posted the resolution to my problem because I want to figure this out but I
still can't get it to work. I am getting an error:
"Undefined function 'Concatenate' in expression." When I got this error I
figured if it didn't want "concatenate" then there must be another word it is
looking for but I can't find the answer to that.
I am using Access 2003, could this be an issue?
Thanks for your help and Happy Labor Day!
:
Your concatenate function might look something like:
Concatenate("SELECT [Index Name] FROM tbl1CustomerAddresses WHERE
MailAddress1 & MailAddress2 & MailCity & MailState & MailZip =""" &
MailAddress1 & MailAddress2 & MailCity & MailState & MailZip & """", " And ")
--
Duane Hookom
Microsoft Access MVP
:
Hi Duane, Thank you for your patience and expertise!
Here is the information:
Table Name:
tbl1CustomerAddresses
Field Names:
CustomerID (Auto-generated primary key #)
Index Name
MailAddress1
MailAddress2
MailCity
MailState
MailZip
4 Records with Field Entry Examples (notice repeat addresses):
Index Name: Mr. John Doe
Mail Address 1: The Arte Shoppe
Mail Address 2: 5555 55th St.
MailCity: Chicago
MailState: IL
MailZip: 55555
Index Name: Mrs. Jane Doe
Mail Address 1: The Arte Shoppe
Mail Address 2: 5555 55th St.
MailCity: Chicago
MailState: IL
MailZip: 55555
Index Name: Ms. Amy White
Mail Address 1:
Mail Address 2: 1111 Elm Street
MailCity: Phoenix
MailState: AZ
MailZip: 22222
Index Name: Mary Smith
Mail Address 1:
Mail Address 2: 1111 Elm Street
MailCity: Phoenix
MailState: AZ
MailZip: 22222
Sample Address Label:
Index Name
Mail Address 1
Mail Address 2
Mail City, MailState MailZip
For example:
Ms. Amy White and Mary Smith
1111 Elm Street
Phoenix, AZ 22222
Thanks again for your help, it will keep my head from rolling!!
Mariah
:
The generic concatenate function could be used. The SQL statement in the
concatenate() function would be something like:
Concatenate("SELECT FirstName FROM tblSomeName WHERE Address=""" & Address &
""" AND City = """ & City & """", ",")
If you can't figure this out, come back with signficant table and field
names as well as sample data and how you want these to appear on your labels.
--
Duane Hookom
Microsoft Access MVP
:
Thank you for this example, but I am still having trouble... It could be
that I am simply not understanding the example.
More information:
The members move around so they aren't static like a family would be - some
change addresses and move to different houses and roomates many times a year.
I have each one in a single record and would like to merge based only on the
addresses, that way I only need update the address when it is submitted
rather than trying to refigure subforms, etc.
Is this even possible?
Thanks again for your help, your replies to other users questions have made
my database so much better then it was!
:
There is a generic concatenate function with usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You could
use this to concatenate based on address.
--
Duane Hookom
Microsoft Access MVP
:
Is there a way to tell the database to print a different header for an
address label if there are multiple members at the same address?
I have queried a mailing but in order to conserve funds the members only
want one sent when they are at a single address. At the same time I have to
keep them as unique entries because we keep track of birthdays, etc.
We would like to keep the names on the labels of the members who live alone.
Any help is appreciated.
