Texas Longhorn
Active Member
- Joined
- Sep 30, 2003
- Messages
- 493
Hi all,
I have a database containing quarterly financial data (e.g. quarterly sales) for many companies over many years. I am using an update query to populate a new field (Sales_LTM [LTM is Last Twelve Months]) which sums four values from the field "Sales". As an example, if the record is for the three months ended 3/31/06, in the field Sales_LTM I want to add the data in Sales for the four 3-month periods ended 3/31/06, 12/31/05, 9/30/05, and 6/30/05. Other pertinent fields for the query are NPERMNO which is a unique company identifier, fqbegdt - fiscal quarter begin date, and fqenddt - fiscal quarter end date. I have one table, Compustat Data, which I added twice in the query design view before switching to SQL view.
I started with the following query as a test (going back to my prior example, this query would only sum two 3-month periods - those ended 12/31/05 and 9/30/05). This query does not work...any help would be much appreciated.
Thanks all,
Bill
I have a database containing quarterly financial data (e.g. quarterly sales) for many companies over many years. I am using an update query to populate a new field (Sales_LTM [LTM is Last Twelve Months]) which sums four values from the field "Sales". As an example, if the record is for the three months ended 3/31/06, in the field Sales_LTM I want to add the data in Sales for the four 3-month periods ended 3/31/06, 12/31/05, 9/30/05, and 6/30/05. Other pertinent fields for the query are NPERMNO which is a unique company identifier, fqbegdt - fiscal quarter begin date, and fqenddt - fiscal quarter end date. I have one table, Compustat Data, which I added twice in the query design view before switching to SQL view.
I started with the following query as a test (going back to my prior example, this query would only sum two 3-month periods - those ended 12/31/05 and 9/30/05). This query does not work...any help would be much appreciated.
Code:
UPDATE [Compustat Data] INNER JOIN [Compustat Data] AS [Compustat Data_1] ON [Compustat Data].NPERMNO = [Compustat Data_1].NPERMNO SET [Compustat Data].Sales_LTM = [Compustat Data_1].[Sales]
WHERE (((DateAdd("d",-1,DateSerial(Left([Compustat Data].[fqbegdt],4),Mid([Compustat Data].[fqbegdt],5,2),Right([Compustat Data].[fqbegdt],2))))=DateSerial(Left([Compustat Data_1].[fqenddt],4),Mid([Compustat Data_1].[fqenddt],5,2),Right([Compustat Data_1].[fqenddt],2))))+[Compustat Data_1].[Sales]
WHERE (((DateAdd("d",-1,DateSerial(Left([Compustat Data].[fqbegdt],4),Mid([Compustat Data].[fqbegdt],5,2)-3,Right([Compustat Data].[fqbegdt],2))))=DateSerial(Left([Compustat Data_1].[fqenddt],4),Mid([Compustat Data_1].[fqenddt],5,2),Right([Compustat Data_1].[fqenddt],2))));
Thanks all,
Bill
Last edited: