count loop routine

  • Thread starter Thread starter carol
  • Start date Start date
C

carol

pls advise as how to write sql (i assume a loop "do while"
routine of some type)for following:

want to create query that will assign a counter in "count"
field so that when i order records within a group, say in
a descending date order, i will have a number assigned
(1,2,3,etc.) for each item in that group where i can then
pull all "2" counter value in each group. can you help me
out? will i have to create two separate queries to
accomplish this? thx much!
 
Do you have some table and field names as well as pasting some values into
an email?
 
What follows isn't the most efficient solution, but it works. I got
it from someone out at comp.databases.ms-access years ago. Maybe
Lyle Fairfield ... I forget.

I created a function to return the ordinal position of a record and
named it GetNextNumber(ID). It takes the unique primary key id
for the recordset as an argument and is called from your query as
follows"

ItemLocation: GetNextNumber([ItemID]) & ")"

This function is NOT reusable for different queries. It has to match
the one you are running, contain the PK ID and be sorted the same.


Public Function GetNextNumber(ByVal lngID As Long) As Integer
On Error Resume Next

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCount As Long
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT [ItemID] FROM tblItems ORDER BY [RequestDate] DESC"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

Do Until rst.EOF
lngCount = lngCount + 1
If rst!ItemID = lngID Then
GetNextNumber = lngCount
Exit Do
End If
rst.MoveNext
Loop

Set rst = Nothing
Set dbs = Nothing

End Function

Cheers and good luck.
 
duane-could not send email to your address indicated!!!

here is more explanation of what i'm dealing with:

"item date" table

field: item #
field: end date
field: new price

table has multiple records for same item #'s due to
continual entry of new end dates with new prices. tables
in database built on relationship with item # in "item"
table which houses all pertinent item data and is joined
(one to many) with other tables such as the "item date"
table

here's what i invision i need to populate the "item date"
table so that at any time i can access the first and/or
second most recent record (based on end date) for each
item #. i will need to retrieve all counter=1 records
either to a table or select query to do other calculations
and reports:

item#: 35642 end date:11/1/03 new price: $3.86
counter: 1
item#: 35642 end date: 8/03/03 new price: $4.25
counter: 2
item#: 44211 end date: 10/05/03 new price: $20.12
counter: 1
item#: 44211 end date: 7/12/03 new price: $21.11
counter:2

routine needs to number records based on end date,
descending.
 
Try create a query with SQL similar to:

SELECT ItemNum, EndDate, NewPrice,
(SELECT Count(*) FROM tblItemDate i WHERE i.ItemNum = tblItemDate.ItemNum
and i.EndDate <=tblItemDate.EndDate) As Counter
FROM tblItemDate;

1) I prefer to not get private emails (most responders feel the same way)
2) Please use the shift key on your keyboard since it makes for easier
reading and you appear less lazy.
3) In the future, consider a naming convention that doesn't use spaces or
symbols.
 
Back
Top