create query to show all items on a customers order

Posted on 2011-10-20
Last Modified: 2012-05-12
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!
Question by:intsup
    LVL 33

    Assisted Solution

    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.
    LVL 61

    Accepted Solution

    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"

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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...
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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...

    Author Comment

    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 !
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    < 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)


    Author Comment


    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.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    <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...

    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    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...



    Author Comment

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now