If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Generate a calculate value from two separate tables.
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 |
#2
|
|||
|
|||
Generate a calculate value from two separate tables.
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! |
#3
|
|||
|
|||
Generate a calculate value from two separate tables.
"Jeff L" wrote: 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! 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 |
#4
|
|||
|
|||
Generate a calculate value from two separate tables.
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! |
#5
|
|||
|
|||
Generate a calculate value from two separate tables.
Thanks for your persistence, I'll try it out. Just getting myself familiar
with SQL so it may take a while. "Jeff L" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|