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
|
|||
|
|||
[newbie] Expression for building a percentage
I need to create an expression that basically creates a division as follows,
adding it as a new field within the SQL query. Change (%) = Quantity of previous year / Quantity of this year SQL Query SELECT Format([Date_of_Reg],"yyyy") AS [Reg Date], Format([Date_of_Reg],"q") AS [Reg Date-q], Car_Model.Manufacturer_ID, Car_Model.Family_name, Car_Class_Segment.Class_segment, Sum(Sales_Registration_Data.Quantity) AS QTY, Car_Model_Variants.AUX_Data_Input_Model_Entry AS Type, [Family_name] & " " & [Variant_Name] AS Class FROM Car_Class_Segment RIGHT JOIN (Car_Model INNER JOIN (Car_Model_Variants INNER JOIN Sales_Registration_Data ON Car_Model_Variants.[Model&Variant_ID] = Sales_Registration_Data.[Model&Variant_ID]) ON Car_Model.Model_ID = Car_Model_Variants.Model_ID) ON Car_Class_Segment.Class_segment_ID = Car_Model.Class_segment GROUP BY Format([Date_of_Reg],"yyyy"), Format([Date_of_Reg],"q"), Car_Model.Manufacturer_ID, Car_Model.Family_name, Car_Class_Segment.Class_segment, Car_Model_Variants.AUX_Data_Input_Model_Entry, [Family_name] & " " & [Variant_Name] HAVING (((Car_Model.Manufacturer_ID) Like "*NSO")) ORDER BY Format([Date_of_Reg],"yyyy"), Format([Date_of_Reg],"q"); Your help would be appreciated. |
#2
|
|||
|
|||
[newbie] Expression for building a percentage
Jon -
Just build another query using your current query as the source (twice). Say your current query name is qryQtrRegistrations. Then run a query like this: Select currYr.*, priorYr.[Reg Date], priorYr.QTY/currYr.QTY As Change_Percent FROM qryQtrRegistrations AS currYr INNER JOIN qryQtrRegistrations AS priorYr ON ( CurrYR.[Reg Date-q] = PriorYr.[Reg Date-q] AND CurrYR.[Manufacturer_ID] = PriorYr.[Manufacturer_ID] AND CurrYR.Family_name = PriorYr.Family_name AND CurrYR.Class_segment = PriorYr.Class_segment AND CurrYR.Type = PriorYr.Type AND CurrYR.Class = PriorYr.Class WHERE (CurrYR.[Reg Date] = PriorYr.[Reg Date] + 1) Note that this will not pull records that don't have a prior year calculation. Also, if one of the prior year QTY amounts could be zero, then you will want to adjust for that. -- Daryl S "Jon" wrote: I need to create an expression that basically creates a division as follows, adding it as a new field within the SQL query. Change (%) = Quantity of previous year / Quantity of this year SQL Query SELECT Format([Date_of_Reg],"yyyy") AS [Reg Date], Format([Date_of_Reg],"q") AS [Reg Date-q], Car_Model.Manufacturer_ID, Car_Model.Family_name, Car_Class_Segment.Class_segment, Sum(Sales_Registration_Data.Quantity) AS QTY, Car_Model_Variants.AUX_Data_Input_Model_Entry AS Type, [Family_name] & " " & [Variant_Name] AS Class FROM Car_Class_Segment RIGHT JOIN (Car_Model INNER JOIN (Car_Model_Variants INNER JOIN Sales_Registration_Data ON Car_Model_Variants.[Model&Variant_ID] = Sales_Registration_Data.[Model&Variant_ID]) ON Car_Model.Model_ID = Car_Model_Variants.Model_ID) ON Car_Class_Segment.Class_segment_ID = Car_Model.Class_segment GROUP BY Format([Date_of_Reg],"yyyy"), Format([Date_of_Reg],"q"), Car_Model.Manufacturer_ID, Car_Model.Family_name, Car_Class_Segment.Class_segment, Car_Model_Variants.AUX_Data_Input_Model_Entry, [Family_name] & " " & [Variant_Name] HAVING (((Car_Model.Manufacturer_ID) Like "*NSO")) ORDER BY Format([Date_of_Reg],"yyyy"), Format([Date_of_Reg],"q"); Your help would be appreciated. |
Thread Tools | |
Display Modes | |
|
|