Query question

[jeff95350]

I'm using Access 2000, and I don't know a lot about Access.

My data has fields that hold a code for a time period. For instance, one time period might be '2006010' and that refers to a time period in 2006. These time periods sort as text correctly (so 20060010 is always before 20060011), but numbers may be skipped (so there may be 20060012 and 20060014 but no 20060013). I didn't make this database, so I have to use that data as-is.

What I want to do is get the sum of all the data (data is a separate field) from the previous 4 periods for each period.

For example if I have:

20060010    1
20060011    2
20060012    4
20060014    8
20060015    16
20060016    32
20060017    64
 

in the original table I want the result to be:

20060010    0 (or NULL)
20060011    1
20060012    3
20060014    7
20060015    15
20060016    31
20060017    62 (notice that it does not add the 1, it only sums 2,4,8,16,32)
 

The real data is not in a pattern, of course. What would be a good query to do this?

I am able to do this in another database with the following query:

SELECT DISTINCT
  i AS i1,
  (SELECT
    SUM(j)
  FROM 
    (SELECT DISTINCT
      i,
      j
    FROM
      t
    WHERE
      i < t1.i
    ORDER BY
      i DESC
    LIMIT 5
    ) t2
  ) AS i2 FROM
t AS t1;
 

But in Access 2000 (after I modify the query to use TOP rather than LIMIT) it does not recognize "t1.i" in the inner inner query.

Any help is appreciated. Also, if someone has a good link to an Access 2000 reference that would help me; for some reason I am having problems finding out which functions are even available to me to make my queries.

Thanks!
Jeff

[NeoPa]

Jeff,
I've been mulling over this for a while now and what you're trying to do is (as far as I'm aware) unsupported in any SQL engine.
Believe me when I give you kudos for the concepts your trying to use but, because in your SQL the engine would need to reprocess the embedded subqueries for each record, this cannot work as is. Your trying to access t1 (defined at the outer level) from within a subquery is a clue and cannot work.
If you give me a short while longer I will try to provide a query (SQL) which can work - it will be based on Domain Aggregate functions.

-Adrian.

[NeoPa]

This is a little embarrasing but it appears that, for various reasons, that is not possible either.
1. Domain Aggregate functions cannot work from a SQL string as a domain.
2. There is no way to specify SELECT predicates (DISTINCT; TOP ?; etc) in Domain Aggregate functions.
3. Domain Aggregate functions can work with Tables or QueryDefs - but not QueryDefs that take parameters.
I'm not sure I could have found a solution with a parameter query anyway, but the other two were certainly show-stoppers.

This problem doesn't appear to have a solution based on simple (or otherwise) SQL, but it can be done with RecordSets in VBA code.
Mary should be along shortly with some pointers (Knowing Mary probably Bells & Whistles too) to help.

[ADezii]

I'm using Access 2000, and I don't know a lot about Access.

My data has fields that hold a code for a time period. For instance, one time period might be '2006010' and that refers to a time period in 2006. These time periods sort as text correctly (so 20060010 is always before 20060011), but numbers may be skipped (so there may be 20060012 and 20060014 but no 20060013). I didn't make this database, so I have to use that data as-is.

What I want to do is get the sum of all the data (data is a separate field) from the previous 4 periods for each period.

For example if I have:

20060010    1
20060011    2
20060012    4
20060014    8
20060015    16
20060016    32
20060017    64
 

in the original table I want the result to be:

20060010    0 (or NULL)
20060011    1
20060012    3
20060014    7
20060015    15
20060016    31
20060017    62 (notice that it does not add the 1, it only sums 2,4,8,16,32)
 

The real data is not in a pattern, of course. What would be a good query to do this?

I am able to do this in another database with the following query:

SELECT DISTINCT
  i AS i1,
  (SELECT
    SUM(j)
  FROM 
    (SELECT DISTINCT
      i,
      j
    FROM
      t
    WHERE
      i < t1.i
    ORDER BY
      i DESC
    LIMIT 5
    ) t2
  ) AS i2 FROM
t AS t1;
 

But in Access 2000 (after I modify the query to use TOP rather than LIMIT) it does not recognize "t1.i" in the inner inner query.

Any help is appreciated. Also, if someone has a good link to an Access 2000 reference that would help me; for some reason I am having problems finding out which functions are even available to me to make my queries.

Thanks!
Jeff

I managed to duplicate the results using reverse logic and a Query with a Calculated Field but did not have the time to refine the code - it is very crude and preliminary. Create a SELECT Query as follows with comparable Field Names:

[Time_Period].....[Data].......Data_Sum : fSumData([Data])
Ascending...... ............... .....(Calculate d Field)

'Global Array to store persisted Data Values
Public DataArray(1000) As Long      'Arbitrary number

'Crude Function called from the Calculated Field
Public Function fSumData(MyData As Long)
Static RecordNumber As Long
 
RecordNumber = RecordNumber + 1
 
DataArray(RecordNumber) = MyData
 
Select Case RecordNumber
  Case 1
    fSumData = 0
  Case 2
    fSumData = DataArray(1)
  Case 3
    fSumData = DataArray(1) + DataArray(2)
  Case 4
    fSumData = DataArray(1) + DataArray(2) + DataArray(3)
  Case 5
    fSumData = DataArray(1) + DataArray(2) + DataArray(3) + DataArray(4)
  Case 6
    fSumData = DataArray(1) + DataArray(2) + DataArray(3) + DataArray(4) + DataArray(5)
  Case 7
    fSumData = DataArray(2) + DataArray(3) + DataArray(4) + DataArray(5) + DataArray(6)
  Case Else
    fSumData = MyData
End Select
End Function

NOTE: The Public Array and Static Record Counter must somehow be Reset prior to Query execution. Got the idea - sorry I didn't have time to refine the coding.

[MMcCarthy]

This problem doesn't appear to have a solution based on simple (or otherwise) SQL, but it can be done with RecordSets in VBA code.
Mary should be along shortly with some pointers (Knowing Mary probably Bells & Whistles too) to help.

Sorry for the delay Jeff.

My solution would involve the use of recordsets in vba. You would need to create a table to hold the results. (tblResults)

Function Period Totals()
Dim db as Database
Dim rs1 as DAO.Recordset
Dim rs2 as DAO.Recordset
Dim rs3 as DAO.Recordset
Dim iDate As String
Dim jSum As Integer
Dim i as Integer
 
Set db = CurrentDB
Set rs1 = db.OpenRecordset("SELECT DISTINCT i FROM t ORDER BY i DESC;")
Set rs2 = db.OpenRecordset("SELECT i, j FROM t ORDER BY i DESC;")
Set rs3 = db.OpenRecordset("tblResults")
 
rs1.MoveFirst
Do Until rs1.EOF
   rs2.MoveFirst
   jSum = 0
   rs2.FindFirst "i = " & rs1!i
   For i = 1 To 5
      rs2.MoveNext
      jSum = jSum + rs2!j
      i = i + 1
   Next i
   rs3.Add
   rs3!i = rs1!i
   rs3!j = jSum
   rs3.Update
   rs1.MoveNext
Loop
 
rs1.Close
rs2.Close
rs3.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set db = Nothing
 
End Function
 

This will give you the sum of the previous five periods for each date. If you need something different. Let me know.

Mary