By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,475 Members | 954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,475 IT Pros & Developers. It's quick & easy.

Query question

P: 1
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:

Expand|Select|Wrap|Line Numbers
  1. 20060010    1
  2. 20060011    2
  3. 20060012    4
  4. 20060014    8
  5. 20060015    16
  6. 20060016    32
  7. 20060017    64
  8.  
in the original table I want the result to be:
Expand|Select|Wrap|Line Numbers
  1. 20060010    0 (or NULL)
  2. 20060011    1
  3. 20060012    3
  4. 20060014    7
  5. 20060015    15
  6. 20060016    31
  7. 20060017    62 (notice that it does not add the 1, it only sums 2,4,8,16,32)
  8.  
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT
  2.   i AS i1,
  3.   (SELECT
  4.     SUM(j)
  5.   FROM 
  6.     (SELECT DISTINCT
  7.       i,
  8.       j
  9.     FROM
  10.       t
  11.     WHERE
  12.       i < t1.i
  13.     ORDER BY
  14.       i DESC
  15.     LIMIT 5
  16.     ) t2
  17.   ) AS i2 FROM
  18. t AS t1;
  19.  
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
Jan 5 '07 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,602
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.
Jan 6 '07 #2

NeoPa
Expert Mod 15k+
P: 31,602
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.
Jan 6 '07 #3

ADezii
Expert 5K+
P: 8,669
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:

Expand|Select|Wrap|Line Numbers
  1. 20060010    1
  2. 20060011    2
  3. 20060012    4
  4. 20060014    8
  5. 20060015    16
  6. 20060016    32
  7. 20060017    64
  8.  
in the original table I want the result to be:
Expand|Select|Wrap|Line Numbers
  1. 20060010    0 (or NULL)
  2. 20060011    1
  3. 20060012    3
  4. 20060014    7
  5. 20060015    15
  6. 20060016    31
  7. 20060017    62 (notice that it does not add the 1, it only sums 2,4,8,16,32)
  8.  
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT
  2.   i AS i1,
  3.   (SELECT
  4.     SUM(j)
  5.   FROM 
  6.     (SELECT DISTINCT
  7.       i,
  8.       j
  9.     FROM
  10.       t
  11.     WHERE
  12.       i < t1.i
  13.     ORDER BY
  14.       i DESC
  15.     LIMIT 5
  16.     ) t2
  17.   ) AS i2 FROM
  18. t AS t1;
  19.  
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..........................(Calculated Field)

Expand|Select|Wrap|Line Numbers
  1. 'Global Array to store persisted Data Values
  2. Public DataArray(1000) As Long      'Arbitrary number
Expand|Select|Wrap|Line Numbers
  1. 'Crude Function called from the Calculated Field
  2. Public Function fSumData(MyData As Long)
  3. Static RecordNumber As Long
  4.  
  5. RecordNumber = RecordNumber + 1
  6.  
  7. DataArray(RecordNumber) = MyData
  8.  
  9. Select Case RecordNumber
  10.   Case 1
  11.     fSumData = 0
  12.   Case 2
  13.     fSumData = DataArray(1)
  14.   Case 3
  15.     fSumData = DataArray(1) + DataArray(2)
  16.   Case 4
  17.     fSumData = DataArray(1) + DataArray(2) + DataArray(3)
  18.   Case 5
  19.     fSumData = DataArray(1) + DataArray(2) + DataArray(3) + DataArray(4)
  20.   Case 6
  21.     fSumData = DataArray(1) + DataArray(2) + DataArray(3) + DataArray(4) + DataArray(5)
  22.   Case 7
  23.     fSumData = DataArray(2) + DataArray(3) + DataArray(4) + DataArray(5) + DataArray(6)
  24.   Case Else
  25.     fSumData = MyData
  26. End Select
  27. 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.
Jan 6 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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)

Expand|Select|Wrap|Line Numbers
  1. Function Period Totals()
  2. Dim db as Database
  3. Dim rs1 as DAO.Recordset
  4. Dim rs2 as DAO.Recordset
  5. Dim rs3 as DAO.Recordset
  6. Dim iDate As String
  7. Dim jSum As Integer
  8. Dim i as Integer
  9.  
  10. Set db = CurrentDB
  11. Set rs1 = db.OpenRecordset("SELECT DISTINCT i FROM t ORDER BY i DESC;")
  12. Set rs2 = db.OpenRecordset("SELECT i, j FROM t ORDER BY i DESC;")
  13. Set rs3 = db.OpenRecordset("tblResults")
  14.  
  15. rs1.MoveFirst
  16. Do Until rs1.EOF
  17.    rs2.MoveFirst
  18.    jSum = 0
  19.    rs2.FindFirst "i = " & rs1!i
  20.    For i = 1 To 5
  21.       rs2.MoveNext
  22.       jSum = jSum + rs2!j
  23.       i = i + 1
  24.    Next i
  25.    rs3.Add
  26.    rs3!i = rs1!i
  27.    rs3!j = jSum
  28.    rs3.Update
  29.    rs1.MoveNext
  30. Loop
  31.  
  32. rs1.Close
  33. rs2.Close
  34. rs3.Close
  35. Set rs1 = Nothing
  36. Set rs2 = Nothing
  37. Set rs3 = Nothing
  38. Set db = Nothing
  39.  
  40. End Function
  41.  
This will give you the sum of the previous five periods for each date. If you need something different. Let me know.

Mary
Jan 6 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.