[newbie] Expression for building a percentage

[Jon]

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.

 

[Daryl S]

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.