• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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!
3 Solutions
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 http://office.microsoft.com/en-us/access-help/using-parameters-with-queries-and-reports-HA001117077.aspx

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"

Jeffrey CoachmanCommented:
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...
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Jeffrey CoachmanCommented:
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 CoachmanCommented:
< 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 CoachmanCommented:
<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 CoachmanCommented:
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!

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now