Lets say you have a set of tables along the lines of those in the sample
Northwind database, Customers, Orders OrderDetails and Products. You want
your packing slip to show the Order Number form Orders the CustomerName from
Customers and all the ProductNames for that order from Products along with
the Quantity ordered per product from OrderDetails. You'd base a report on a
query which joins all the tables and prompts for an order number, e.g.
SELECT CustomerName, ProductName, Quantity
FROM Customers, Orders, OrderDetails, Products
WHERE Customers.CustomerID = Orders.CustomerID
AND Orders.OrderID = OrderDetails.OrderID
AND OrderDetails.ProductID = Products.ProductID
AND Orders.OrderID = [Enter Order number:];
In the report, as its only reyurns data about one order you don't need to
group the report; you can put the OrderNumber and CustomerName controls in
the Page Header
and the ProductName and Quantity controls in the Detail section. When you
open the report you'll be prompted for the order number and the report will
show all the items ordered on that order.
You can of course include other data if necessary, but I hope the above
gives you the general idea.
Rather than just prompting for the order number you could just have a button
on your Orders data input form to print the report for the current order.
The query would then reference the OrderID on the form:
SELECT CustomerName, ProductName, Quantity
FROM Customers, Orders, OrderDetails, Products
WHERE Customers.CustomerID = Orders.CustomerID
AND Orders.OrderID = OrderDetails.OrderID
AND OrderDetails.ProductID = Products.ProductID
AND Orders.OrderID = Forms!frmOrders!OrderID;
Ken Sheridan
Stafford, England
Teri said:
I need to create a packing slip to be generated by a rather large database (I
am using Access 2002). There are approximately 15 different items that could
possibly be ordered and thus be included on the packing slip, but I want to
only show the items that are actually ordered. Is this something that is
possible to do in Access?
Thanks!
