Generate a calculate value from two separate tables.

F

Francophone

I have to tables:
1. Product shipped
2. Product returned
These two tables share a common field [PartNo] but are not in a relationship.

I need to include a field in a report which will calculate a monthly
measurement of quality in a given time period ie. (product returned in March
/ product shipped in March)?

TIA
 
J

Jeff L

In your report field put
=DateDiff("m", DateSent, DateReturned)

This will give you the number of months between when the product was
sent and when it was returned.

Hope that helps!
 
F

Francophone

Jeff L said:
In your report field put
=DateDiff("m", DateSent, DateReturned)

This will give you the number of months between when the product was
sent and when it was returned.

Hope that helps!
Click to expand...

Didn't mean to confuse. I don't need to compute the number of days between
ship and return dates. What I need is to take a value from one query (qty of
product shipped), take a value from another query (qty of product returned),
and calculate

(qty of product returned) / (qty of product shipped).

I would prefer to break these down in monthly intervals so my displays is as
follows:

"Customer Name"
May 2005
Total Shipped = "sum of product shipped in May"
Total Returned = "sum of product returned in May"
Quality = total returned / total shipped

June 2005...etc

TIA
 
J

Jeff L

Here's what I suggest:

Create two queries.
Query 1 is for your Sent Items:
SELECT Table1.CustID, DatePart("m",[DateSent]) AS MonthSent,
DatePart("yyyy",[DateSent]) AS YearSent, Count(Table1.CustID) AS
TotalShipped
FROM Table1
GROUP BY Table1.CustID, DatePart("m",[DateSent]),
DatePart("yyyy",[DateSent]);

Query 2 is for Returned Items:
SELECT Table2.CustID, DatePart("m",[DateReturned]) AS MonthReturned,
DatePart("yyyy",[DateReturned]) AS YearReturned, Count(Table2.CustID)
AS TotalReturned
FROM Table2
GROUP BY Table2.CustID, DatePart("m",[DateReturned]),
DatePart("yyyy",[DateReturned]);

Now join the two queries together in a third query using outer joins:
SELECT Query1.CustID, MonthName([Query1].[MonthSent]) & " " &
[YearSent] AS WhenSent, Query1.TotalShipped, Query2.TotalReturned
FROM Query1 LEFT JOIN Query2 ON (Query1.YearSent = Query2.YearReturned)
AND (Query1.MonthSent = Query2.MonthReturned) AND (Query1.CustID =
Query2.CustID);

You can also include the Customer name from your customer table if that
is what you want.

Use the third query as the record source for your table. Using the
report wizard will aid you in creating your report. You will want one
heading to be your customer Name, then When Sent. You will now need to
create a text box and in it put:
=Nz(NumberOfReturnedItems,0)/NumberOfSentItems.

Hope that helps!
 
F

Francophone

Thanks for your persistence, I'll try it out. Just getting myself familiar
with SQL so it may take a while.
 
Top