Summing lagged variables query

[Texas Longhorn]

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.

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

 

[pooratlas]

TL:
Create a query to list quarterly Sales records for only the previous 4Qs by using something like this:
<code>
SELECT tbl090331_c.SalesQDate, tbl090331_c.Comp, tbl090331_c_1.SalesQDate AS PrevFourQDate, tbl090331_c_1.Sales
FROM tbl090331_c INNER JOIN tbl090331_c AS tbl090331_c_1 ON tbl090331_c.Comp = tbl090331_c_1.Comp
WHERE (((tbl090331_c_1.SalesQDate)>[tbl090331_c].[SalesQDate]-365 And (tbl090331_c_1.SalesQDate)<=[tbl090331_c].[SalesQDate]))
ORDER BY tbl090331_c.SalesQDate, tbl090331_c_1.SalesQDate;
</code>
From this dataset, use a summary query to group by SalesQDate and sum the previous 4Qs
<code>
SELECT qry090331_c.SalesQDate, qry090331_c.Comp, Sum(qry090331_c.Sales) AS SumOfSales
FROM qry090331_c
GROUP BY qry090331_c.SalesQDate, qry090331_c.Comp;
</code>
Keep in mind that oldest records may not have four previous Qs worth of data.

 

[Texas Longhorn]

pooratlas - many thanks; just what I was looking for. Thank you.

 

[Texas Longhorn]

A quick follow-up question: I have some records in the Sales field that are null. For example (due to bad data quality), for the four quarters ended 3/31/05, 6/30/05, 9/30/05, and 12/31/05, I might have respective sales figures of "200", "", "100", "300". Rather than having the summary query return 600 for the 12/31/05 SumOfSales date, is there a way to return null? Basically, I would like any four-quarter summation in which any component(s) are null to also be null.

Many thanks for the help,
Bill

 

[alansidman]

Look at the NZ function. This will convert any Nulls to whatever you want them to be.

Alan

 

[Texas Longhorn]

Alan,

Thanks for the reply. I looked at the NZ function, but the Microsoft article gave me the impression that it does the opposite of what I'm looking to do (I am very new to Access, so I've probably misunderstood). The current summation query is effectively treating null values from the select query as zeros, and therefore returning a numeric value even if one of the summands is null. In the case of a null summand, I would like a null value returned by the summation query.

Like I said, I'm a complete newbie to Access, so I've probably missed the point. Could you give me some additional guidance on how to use the NZ function to do what I'm going for?

Thanks for the help,
Bill

 

[alansidman]

I apologize, I read your request backwards. You are right about the NZ function. I would use an immediate if statement =IIF. The syntax for that is =iff(criteria, result if true, result if false).

Alan

 

[pooratlas]

One option would be to eliminate those Final Q dates that did not have four Qs worth of previous "sub-dates".
Add a CountOfSales next to the SumOfSales
<code>
SELECT qry090331_c.SalesQDate, qry090331_c.Comp, Sum(qry090331_c.Sales) AS SumOfSales, Count(qry090331_c.Sales) AS CountOfSales
FROM qry090331_c
GROUP BY qry090331_c.SalesQDate, qry090331_c.Comp;
</code>
Then add a criteria to CountOfSales eg: =4
<code>
SELECT qry090331_c.SalesQDate, qry090331_c.Comp, Sum(qry090331_c.Sales) AS SumOfSales, Count(qry090331_c.Sales) AS CountOfSales
FROM qry090331_c
GROUP BY qry090331_c.SalesQDate, qry090331_c.Comp
HAVING (((Count(qry090331_c.Sales))=4));
</code>

 

[Texas Longhorn]

Thank you both for your responses. pooratlas - that worked perfectly; many thanks for your help.

Bill