create query to show all items on a customers order

i have the following table and data:

tblname:   salesorderline
FullName       RefNumber      PONumber         SalesOrderLineItemRefFullName  SOLineQty
ABC Co         S0001              P00034              Widget1                                        15
ABC Co         S0001              P00034              Widget2                                        2
ABC Co         S0001              P00034              Widget3                                        1
DEF Inc          S0134              Verbal               Widget114                                     9
GHIJ LLC       S1234              Cash                  Widget532                                     2
GHIJ LLC       S1234              Cash                  Widget599                                     1

My goal is to send out an e-mail to each FULLNAME (our customer) as follows:

Thank you for your order, our reference #S0001 and your purchase order P00034
Your order consists of:
   15 units Widget1
     9 units Widget2
     1 unit   Widget3

etc. etc. etc.

unfortunately, I can't get a query to work this way with all items in a row to easily put into a merge field.  and a report simply gives me ALL customers and their order.

my hope is to be able to do this in a query but if access doesn't allow that then any other way that resolves my question would be welcome.

your input is appreciated!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
Would a report give you what you need if you could do it for individual customers?

If it would then you could try 2 things:

1 Add grouping on the customer to the report and set it's property to keep the data for each customer together.

2 Set up a form with a dropdown of customer names and then use the value selected in the dropdown as a parameter for the report.
The first option will still return all customers but they'll be separated out.

The second will produce individaul reports for each customer.

Here's a link that shows you how to do that

It's actually a very good explanation - you'll find what you want quite near the bottom.

It's also a topic in Access Help.
Assuming that you are somehow looping through a list of "FullNames" to send this report out, you could define global variable for FullName in a public module like this:

Global gFullName as string

Then define the reports recordsource in the Open Event of your report to include only records for that fullname:

Me.Recordsource = "SELECT * FROM YourTableOrQuery WHERE FullName = " & chr(34) & gFullName & chr(34)

And in the loop that sends out the emails, something like this to send the report, assuming you are iterating through a recordset of people to be emailed :

gFullName = rs!FullName  '<-- set the global variable to be used in the report's recordsource
Docmd.SendObject acSendReport, "rptYourReportName",acFormatSNP , rs!recipientsEmailAddress,,,"Your Orders", Please see the attachment regarding your orders"


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Also the scope of your question is not clear, are you looking for this entire email "system"?

This will depend on many factors, the most important being what version of Access you are using.

The most simple approach would be to send an individual email to each customer and attach the "orders" as a PDF attacment.

Adding custom text and "Building" the order details in the body of the email (as you are requesting) is a bit trickier...
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Here is a sample of my post.

To be fair, as far as I can tell, it may contain elements that the previous experts have mentioned...

You must be using Access 2007 (with the PDF addin installed) or Access 2010
Examine it carefully and completely, then test it.

Let us know the results...
intsupAuthor Commented:
i haven't forgotten about this question and i apologize for not getting back sooner, i'm simply reading through all the material to do it right.

to simplify, for now forget that it's for a mass e-mail and simply think of it as a report per customer or per order, etc.  i'll work the mailmerge into outlook later.

from the looks of it, a report would be the way to go to create one report per customer and then mailmerge the results of the report.  however i don't know if i can mailmerge from a report.

i'm using Access 2002, by the way.

thanks !
Jeffrey CoachmanMIS LiasonCommented:
< however i don't know if i can mailmerge from a report.>

Not directly,
However, I am still a bit hazy on why a Mail Merge is even needed...

Everything you are asking for here can be done in a Report directly.
(see the attached sample DB)

Just note that this is a simplification of the issue here, based on your new parameters for this Question
(For example, it is not clear if you have a Customer table)

intsupAuthor Commented:

thanks for the feedback and example.  your form works except that i have to manually choose a customer and then it shows order data for that customer.

what i'm looking for is an individual report for all customers.  from the looks of it, i will have to create a loop like MBIZUP suggests to create a report for each customer until it cycles through all customers.

the reason for the mail merge is that i want to mailmerge to outlook and send e-mails to all our customers telling all our customers the info about their orders.
Jeffrey CoachmanMIS LiasonCommented:
<your form works except that i have to manually choose a customer and then it shows order data for that customer.
what i'm looking for is an individual report for all customers.>

I am not sure I understand the difference...?
My sample does generate an individual "Filtered" report...

You really don't generate separate "reports", because "Reports" only really exist inside the Access database.
Creating separate "Reports would clog up the DB...

You could "Filter" the report for each customer and generate separate *Files* though...

But again:
You have not stated if you have a Customer table
You have not sated what version of Access you are using
You have not stated what format you need the files in...

Jeffrey CoachmanMIS LiasonCommented:
In a properly formatted DB you would loop the customer table and filter the report for each customer, then save that filtered report as a *File*

Something the attached new sample file

But again, your abrupt change of the focus of this Question makes this more complicated than it has to be.
<for now forget that it's for a mass e-mail and simply think of it as a report per customer or per order, etc>

We are all Experts here and this issue has been addressed dozens of times, so no simplification was really needed...
(if simplification is need, we'll let you know)

With individual reports why bother trying to "List" the order details IN the email.
Just attach the File to the email...

If you need to "List" the Order Details IN the email, then generating "Files" is not really needed.

Just tell us *Exactly* what you want:
-A list of the order details in the Email
-A filtered file/report attached to the email
-Something else entirely....
   ...and that is exactly what we will give you...

Please clarify you exact need here to avoid confusion as to what the actual "solution" needs to be...


intsupAuthor Commented:
sorry for the continued delay but bigger fires popped up than this one.

in answer to your questions:

customer table:  there IS a customer table and data about the customer, including their email address would be looking to the FULLNAME field which references the customer's full name
Access version:  i'm using access 2002
File Format:  i was hoping to have a flat record to use for mail merging to word or inside an e-mail etc.  but from the looks of it, each query needs to create a separate file and that would contain the order status.  PDF would be the best

i guess you all answered my question but it wasn't the answer i wanted to hear since i wanted to use the result in a mail merge instead of a separate document.  but a separate PDF document would work, once i upgrade to a version of access that can do that.

in effect, attaching a file and emailing our customer(s) telling them that line item 1 is ready to ship but line item 2 still has X units to be complete would work.

thanks for everyone's help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.