Report using 2 separate tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables:
Ship Table: CustomerID, ShipDate, Qty
Return Table: CustomerID, ReturnDate, Qty

I need to generate a report as follows:
CustomerID
Qty Returned by Month
Qty Shipped by Month
Calculate Field = qty returned / qty shipped

Should like this for example:
Joe's Company
May 2005
12 shipped
6 returned
50%

June 2005
30 shipped
10 returned
33%
etc....

What is the best way to set this up?

TIA
 
There are probably several ways to approach this objective depending
on how you want the printed report to appear. I will offer the
following NOT as a complete solution but as a guideline to get started.

1. Create a summary query for each table grouping by month
and showing sum of items shipped and returned. Don't forget
to accommodate null values.

2. Tie the report to a query comprised of your Customers table
plus each of the two summary queries something like:

SELECT Customers.CustomerID, Customers.CusName,
qryShip.Month, qryShip.Qty
qryReturn.Month, qryReturn.Qty, pctReturned
FROM (Customers LEFT JOIN qryShip ON Customers.CustomerID =
qryShip.CustomerID)
LEFT JOIN qryReturn ON Customers.CustomerID = qryReturn.CustomerID

Use LEFT JOINs to ensure that a response is generated even if no
activity was recorded for the month for a particular customer.

3. Use the appropriate grouping levels on the report to discern
customers and months.


Again I emphasize the above is only intended as an initial guideline
to get you started. Hopefully it will be helpful.

Jack Cannon
 
Back
Top