You might find it easier to set up an Access Report than a Word "Mail
Merge" document to do what you describe. You could print the report to
a file, then link the file to your Word document. (For this message, I
printed the report to a file, then pasted the contents into the message.)
Here is an example of what I'm trying to accomplish. I apologize for it being
lengthy.
Selection criteria:
SELECT TABLE1.FIELD1,TABLE1.FIELD2,TABLE1.FIELD3,TABLE1.FIELD4,TABLE1.FIELD5,
TABLE2.FIELD1,TABLE2.FIELD2,TABLE2.FIELD3,TABLE2.FIELD4,TABLE2.FIELD5,
TABLE3.FIELD1,TABLE3.FIELD2,TABLE3.FIELD3,TABLE3.FIELD4,TABLE3.FIELD5
FROM SQL_DATABASE.dbo.TABLE1 TABLE1,SQL_DATABASE.dbo.TABLE2
TABLE2,SQL_DATABASE.dbo.TABLE3 TABLE3
WHERE TABLE1.FIELD1 = TABLE2.FIELD1 AND TABLE1.FIELD2 = TABLE3.FIELD2
GROUP BY
TABLE1.FIELD1,TABLE1.FIELD2,TABLE1.FIELD3,TABLE1.FIELD4,TABLE1.FIELD5,
TABLE2.FIELD1,TABLE2.FIELD2,TABLE2.FIELD3,TABLE2.FIELD4,TABLE2.FIELD5,
TABLE3.FIELD1,TABLE3.FIELD2,TABLE3.FIELD3,TABLE3.FIELD4,TABLE3.FIELD5
It was difficult to see how these field names related to the labels in
your "Desired Results", so I renamed some of them, as shown here:
[Customers] Table (formerly [TABLE1]):
CustomerID NAME PHONE FIELD4 FIELD5
---------- ---------- ------------ ------ ------
12345 MARY SMITH 800-555-1234
12358 JOHN JONES 800-555-1235
[Orders] Table (formerly [TABLE3]):
OrderID DATE FIELD2 FIELD3 FIELD4 FIELD5 CustomerID
---------- --------- ------ ------ ------ ------ ----------
-201268234 11/2/2005 12358
1130961385 11/2/2005 12345
[Order details] Table (formerly [TABLE2]):
ITEM DESCRIPTION QUANTITY PRICE FIELD5 OrderID
---- --------------- -------- ------- ------ ----------
0001 SOME PRODUCT 2 $39.95 1130961385
0022 SOME OTHER PRODUCT 1 $150.00 1130961385
0022 SOMETHING 1 $19.00 -201268234
Desired Results:
Page 1
TABLE1 DATA: TABLE3 DATA: DATE:11/2/2005
ID: 12345
NAME: MARY SMITH
PHONE: 800-555-1234
TABLE2 DATA:
ITEM DESCRIPTION QUANTITY PRICE
0001 SOME PRODUCT 2 39.95
0022 SOME OTHER PRODUCT 1 150.00
SUB-TOTAL 229.90
SHIPPING 23.95
TOTAL 252.95
I set up an Access Report that produces the following output, based on a
Query (shown below) that gets its data from the three Tables:
Page 1
TABLE1 DATA: TABLE3 DATA: DATE: 11/2/2005
ID: 12345
NAME: MARY SMITH
PHONE: 800-555-1234
TABLE2 DATA:
ITEM DESCRIPTION QUANTITY Unit Total
Price Price
0001 SOME PRODUCT 2 $39.95 $79.90
0022 SOME OTHER PRODUCT 1 150.00 $150.00
SUB-TOTAL $229.90
SHIPPING $23.95
TOTAL $253.85
Page 2
TABLE1 DATA: TABLE3 DATA: DATE: 11/2/2005
ID: 12358
NAME: JOHN JONES
PHONE: 800-555-1235
TABLE2 DATA:
ITEM DESCRIPTION QUANTITY Unit Total
Price Price
0022 SOMETHING 1 $19.00 $19.00
SUB-TOTAL $19.00
SHIPPING $23.95
TOTAL $42.95
---
I based this Report on the following Query:
[Q_FullReport] SQL:
SELECT Q_Orders.CustomerID, Q_Orders.NAME,
Q_Orders.PHONE, Q_Orders.OrderID, Q_Orders.DATE,
Q_Orders.[SUB-TOTAL], Q_Orders.SHIPPING,
Q_Orders.TOTAL, Q_OrderDetails.ITEM,
Q_OrderDetails.DESCRIPTION, Q_OrderDetails.QUANTITY,
Q_OrderDetails.[Unit Price],
Q_OrderDetails.[Total Price]
FROM Q_Orders INNER JOIN Q_OrderDetails
ON Q_Orders.OrderID = Q_OrderDetails.OrderID
ORDER BY Q_Orders.CustomerID;
.... which, in Query Datasheet View, looks something like this, except
that I had to fold the lines to fit the message:
[Q_FullReport] Datasheet View:
CustomerID NAME PHONE OrderID DATE
SUB-TOTAL SHIPPING TOTAL ITEM
DESCRIPTION QUANTITY Unit Price Total Price
----------------------------------------------------------
12345 MARY SMITH 800-555-1234 1130961385 11/2/2005
$229.90 $23.95 $253.85 0022
SOME OTHER PRODUCT 1 $150.00 $150.00
12345 MARY SMITH 800-555-1234 1130961385 11/2/2005
$229.90 $23.95 $253.85 0001
SOME PRODUCT 2 $39.95 $79.90
12358 JOHN JONES 800-555-1235 -201268234 11/2/2005
$19.00 $23.95 $42.95 0022
SOMETHING 1 $19.00 $19.00
This Query is based on the following two Queries, which get their data
from the Tables:
[Q_OrderDetails] SQL:
SELECT [Orders].[OrderID], [Customers].[CustomerID],
[Order details].[Order detailID],
[Customers].[NAME], [Customers].[PHONE],
[Orders].[DATE], [Order details].[ITEM],
[Order details].[DESCRIPTION],
[Order details].[QUANTITY],
[Order details].[PRICE] AS [Unit Price],
[QUANTITY]*[PRICE] AS [Total Price]
FROM (Customers INNER JOIN Orders
ON [Customers].[CustomerID]=[Orders].[CustomerID])
INNER JOIN [Order details]
ON [Orders].[OrderID]=[Order details].[OrderID]
ORDER BY [Orders].[OrderID], [Order details].[ITEM],
[Customers].[CustomerID], [Orders].[OrderID];
[Q_Orders] SQL:
SELECT Q_OrderDetails.CustomerID,
Q_OrderDetails.NAME, Q_OrderDetails.PHONE,
Q_OrderDetails.OrderID, Q_OrderDetails.DATE,
Sum(Q_OrderDetails.[Total Price]) AS [SUB-TOTAL],
23.95 AS SHIPPING,
[SUB-TOTAL]+[SHIPPING] AS TOTAL
FROM Q_OrderDetails
GROUP BY Q_OrderDetails.CustomerID,
Q_OrderDetails.NAME, Q_OrderDetails.PHONE,
Q_OrderDetails.OrderID, Q_OrderDetails.DATE;
Current Results:
Page 1
TABLE1 DATA: TABLE3 DATA: DATE:11/2/2005
ID: 12345
NAME: MARY SMITH
PHONE: 800-555-1234
TABLE2 DATA:
ITEM DESCRIPTION QUANTITY PRICE
0001 SOME PRODUCT 2 39.95
SUB-TOTAL 229.90
SHIPPING 23.95
TOTAL 252.95
Page 2
TABLE1 DATA: TABLE3 DATA: DATE:11/2/2005
ID: 12345
NAME: MARY SMITH
PHONE: 800-555-1234
TABLE2 DATA:
ITEM DESCRIPTION QUANTITY PRICE
0022 SOME OTHER PRODUCT 1 150.00
SUB-TOTAL 229.90
SHIPPING 23.95
TOTAL 252.95
Thanks-
E
If you want to set up a Report like mine, you'll need 2 levels of
grouping. The top level will be by [OrderID], and the 2nd level will be
by [ITEM]. You'll need to open the footer on [OrderID], and to set its
"Force New Page" property to "After Section".
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
