Hello i have a problem with a query and sorry for the length of this. First of all to give you a description of what i am dealing with. I have a query that uses two tables and a query. The first is a Variance query that has the sum of variance by Sales_order. The sales order can show up multiple times because each part has a variance and multiple parts make up an order. That is why i created the query grouping by sales order. The next table is sales table and it has customer information. The final table is a Margin table that has sales information. This is the table where my problem lies. Each sales order can have multiple ship dates within the sales order. My query currently has all the Customer Name, sales data, and variance and it all works great. My boss recently requested that i put in a date paramater and this is my snag. When i enter the date the variance gets inflated due to each sales order potentially having multiple ship dates. If there are 2 ship dates then the variance is twice its value. Does anyone know how i can enter the date parameter and not have my variance inflated. My query currently looks like this:
SELECT DISTINCTROW tbl_Sales_2002.CUST_NO, Sum(tbl_Gross_Margin.QTY_SHIP) AS [Sum Of QTY_SHIP], Sum(tbl_Gross_Margin.SALES_AMT) AS [Sum Of SALES_AMT], Sum(tbl_Gross_Margin.STD_COST) AS [Sum Of STD_COST], Sum(tbl_Gross_Margin.STD_MARGIN) AS [Sum Of STD_MARGIN], Variation_Sum.SumOfVariance
FROM tbl_Sales_2002 INNER JOIN (tbl_Gross_Margin INNER JOIN Variation_Sum ON tbl_Gross_Margin.SALES_ORDER = Variation_Sum.[Sales Order]) ON tbl_Sales_2002.SALES_NUMBER = tbl_Gross_Margin.SALES_ORDER
GROUP BY tbl_Gross_Margin.SALES_ORDER, tbl_Sales_2002.CUST_NO, Variation_Sum.SumOfVariance;
SELECT DISTINCTROW tbl_Sales_2002.CUST_NO, Sum(tbl_Gross_Margin.QTY_SHIP) AS [Sum Of QTY_SHIP], Sum(tbl_Gross_Margin.SALES_AMT) AS [Sum Of SALES_AMT], Sum(tbl_Gross_Margin.STD_COST) AS [Sum Of STD_COST], Sum(tbl_Gross_Margin.STD_MARGIN) AS [Sum Of STD_MARGIN], Variation_Sum.SumOfVariance
FROM tbl_Sales_2002 INNER JOIN (tbl_Gross_Margin INNER JOIN Variation_Sum ON tbl_Gross_Margin.SALES_ORDER = Variation_Sum.[Sales Order]) ON tbl_Sales_2002.SALES_NUMBER = tbl_Gross_Margin.SALES_ORDER
GROUP BY tbl_Gross_Margin.SALES_ORDER, tbl_Sales_2002.CUST_NO, Variation_Sum.SumOfVariance;