How can I relate a table with a itself but with an offset 1

[pdupreez]

I do not know if my question is clear enough, so please bear with me:

I have a table with the following fields of information:

Year[tab]Month[tab]ClosingStock
2009[tab]1[tab][tab][tab]1000
2009[tab]2[tab][tab][tab]1500

I want to calculate what was produced, and that is done by subtracting the previous months closing stock from this months closing stock. I want to do that in a query, and end up with a query result as follow:

Year[tab]Month[tab]OpeningStock[tab]ClosingStock[tab]Produced
2009[tab]1[tab][tab][tab]0 [tab][tab][tab][tab][tab]1000[tab][tab][tab][tab]1000
2009[tab]2[tab][tab][tab]1000[tab][tab][tab][tab]1500[tab][tab][tab][tab]500

Is that possible??

 

[dhookom]

Try this SQL

SELECT NoNameTable.Year, NoNameTable.Month, 
Val(Nz((SELECT Top 1 ClosingStock FROM NoNameTable N WHERE DateSerial(N.Year,N.Month,1)<DateSerial(NoNameTable.Year,NoNameTable.Month,1) ORDER BY Year Desc,Month Desc),0)) AS OpeningStock,
NoNameTable.ClosingStock, 
[ClosingStock]-Val(Nz((SELECT Top 1 ClosingStock FROM NoNameTable N WHERE DateSerial(N.Year,N.Month,1)<DateSerial(NoNameTable.Year,NoNameTable.Month,1) ORDER BY Year Desc,Month Desc),0)) AS Produced
FROM NoNameTable;

Duane
Hook'D on Access
MS Access MVP

 

[pdupreez]

Thank you very much Duane, for a noob like me that is an awesome looking query, and exactly the direction I am looking to go. I will look into the detail of what it does to ensure I understand how it works.

I get no major errors when executing that query, but end up with no data (#name? in all fields), and the following warning message on executing the query:

At most one record can be returned by this subquery

What of course could be a problem is the fact that the first opening stock will be "unknown" in the sense that it will look for a record that cannot exist (the first record in the table will have a closing stock, but no preceding closing stock to use as an opening stock) and will have to use zero in such a case, or perhaps some other fixed value.

Any ideas what may the cause of this?

 

[pdupreez]

Having worked through some Google results for the error code given, I understand now that we are talking about subqueries, and that the error is caused by the subquery returning multiple records even though Top 1 is included. The table contains data for many product types, in different budget periods, with different versions of those budget periods, therefore each of them will have a ClosingStock for each month, and I assume I will have to find a way to also include the productcode into the query. The table contains the following fields:

ProductionCode
Year
Month
BudgetPeriod
Version
ClosingStock

The data is exclusive on ProductionCode, Year, Month, BudgetPeriod, Version, i.e there will only be one record for a specific year, month, budgetperiod, version and productioncode

 

[pdupreez]

I have progressed a little, in that if I include the other fields in my sort code, I get an answer, not the right answer always, but something at least. The code now looks as follow:

SELECT NoNameTable.ProductionCODE, NoNameTable.Year, NoNameTable.Month, Val(Nz((SELECT Top 1 ClosingStock FROM NoNameTable N WHERE DateSerial(N.Year,N.Month,1)<DateSerial(NoNameTable.Year,NoNameTable.Month,1) ORDER BY ProductionCode, BudgetPeriod, Version, Year Desc,Month Desc),0)) AS OpeningStock, [ClosingStock]+[Demand]-Val(Nz((SELECT Top 1 ClosingStock FROM NoNameTable N WHERE DateSerial(N.Year,N.Month,1)<DateSerial(NoNameTable.Year,NoNameTable.Month,1) ORDER BY  ProductionCode, BudgetPeriod, Version, Year Desc,Month Desc),0)) AS Produced, NoNameTable.Demand, NoNameTable.ClosingStock
FROM NoNameTable;

All the even row records have the same opening stock which is the same as the closing stock for the first row of data, and I am still trying to work that out

 

[pdupreez]

With the help of a colleague I found the solution:

SELECT NoNameTable.ProductionCODE, NoNameTable.Year, NoNameTable.Month, Val(Nz(
SELECT Top 1 ClosingStock 
FROM NoNameTable N 
WHERE DateSerial(N.Year,N.Month,1)<DateSerial(NoNameTable.Year,NoNameTable.Month,1) 
AND N.ProductionCode=NoNameTable.ProductionCode 
ORDER BY Year Desc,Month Desc,ProductionCode),0) AS OpeningStock, [ClosingStock]+[Demand]-
Val(Nz((
SELECT Top 1 ClosingStock FROM NoNameTable N 
WHERE DateSerial(N.Year,N.Month,1)<DateSerial(NoNameTable.Year,NoNameTable.Month,1) 
AND N.ProductionCode=NoNameTable.ProductionCode 
ORDER BY Year Desc,Month Desc,ProductionCode),0)) AS Produced, NoNameTable.Demand, NoNameTable.ClosingStock
FROM NoNameTable;

It is not complete as I will have to also consider Year, Budgetperiod and Version into the subqueries, but it works fine for now.

 

[dhookom]

Next time, try to be more specific with table and field names and complete needs (ie: by ProductionCode).

Duane
Hook'D on Access
MS Access MVP

 

[pdupreez]

Yes Sir