create query to show all items on a customers order
Posted on 2011-10-20
i have the following table and data:
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!