Prev: Latest occurrence for same person
Next: Linking fields in different table in same database
From: Jon on 27 Jan 2010 08:50
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.
 | 
Pages: 1
Prev: Latest occurrence for same person
Next: Linking fields in different table in same database