Finding Records in Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to find all records within each Prod_ID that occur AFTER the balance
has reached zero. In the example below, Rec_ID's 4, 7, 8, 13 and 14 would be
records I'm trying to retrieve.

Any ideas?


Rec_ID Prod_ID WC_ID Balance
1 950698 137X (192.29)
2 950698 1492 (300.22)
3 950698 1572 0.00
4 950698 1687 (845.49)
5 955480 371224 2,667.24
6 955480 371569 0.00
7 955480 371570 933.30
8 955480 371571 (693.54)
9 969233 1169 (24,699.16)
10 969233 137X (24,557.34)
11 969233 1492 (24,638.77)
12 969233 1572 0.00
13 969233 1599 (23,055.40)
14 969233 2241 (29,456.58)
 
Kirk said:
I need to find all records within each Prod_ID that occur AFTER the balance
has reached zero. In the example below, Rec_ID's 4, 7, 8, 13 and 14 would be
records I'm trying to retrieve.

Rec_ID Prod_ID WC_ID Balance
1 950698 137X (192.29)
2 950698 1492 (300.22)
3 950698 1572 0.00
4 950698 1687 (845.49)
5 955480 371224 2,667.24
6 955480 371569 0.00
7 955480 371570 933.30
8 955480 371571 (693.54)
9 969233 1169 (24,699.16)
10 969233 137X (24,557.34)
11 969233 1492 (24,638.77)
12 969233 1572 0.00
13 969233 1599 (23,055.40)
14 969233 2241 (29,456.58)
Click to expand...


"Occurs after" implies the need for a date/time field that
can be used to order the records. If you've been very
careful in creating the RecID numbers, that might be an
acceptable alternative (an AutoNumber field is NOT adequate
for this).

SELECT T.Rec_ID, T.Prod_ID, T.WC_ID, T.Balance
FROM thetable As T
WHERE T.Rec_ID > (SELECT X.Rec_ID
FROM thetable As X
WHERE X.Prod_ID = T.Prod_ID
AND X.Balance = 0)

But, I wonder what you should do if there is a product with
more than a one record with a balance of 0???
 
I think this will require VBA. You will need to be sure your recordset is
ordered by Prod_ID and WC_ID. It appear that the WC_ID for each Prod_ID.
Hope that is true, or if there is a date field that appear in cronological
order, you could use that. You will need a table to store the results. Here
is the Air code:

Dim blnHitZero as Boolean
Dim txtCurrentProdID as String
Dim rstSource as Recordset
Dim rstDest as Recordset

set rstSouce = currentdb.YourTable
set rstDest = currentdb.NewTabe
bnlHitZero = False
txtCurrentProdId = rstSource.[Prod_Id]

Do While Not rstSource.Eof
Do While txtCurrentProd_ID = rstSource.[Prod_ID]
If rstSource.Balance = 0 then
txtCurrentProdId = rstSource.[Prod_Id]
blnHitZero = True
Else If blnHitZero Then
With rstDest
.Addnew
Set rstDest.[Prod_ID] = rstSource.[Prod_ID]
Set rstDest.[WC_ID] = rstSource.[WD_ID]
Set rstDest.[Balance] = rstSource.[Balance]
.Update
End With
End If
rstSource.Movenext
Loop
txtCurrentProdID = rstSource.[Prod_ID]
blnHitZero = False
Loop

Not at all tested, and syntax may not be correct, but that is the basic
approach
 
I've figured out and corrected the syntax errors you mentioned. When I run
the sub, I'm getting a compile error "invalid use of property" on the Set
rstDest("PROD_ID") = rstSource("Prod_ID") line. Any ideas?

Public Sub KirkCode()

Dim blnHitZero As Boolean
Dim txtCurrentProdID As String
Dim db As Database
Dim rstSource As Recordset
Dim rstDest As Recordset

Set db = CurrentDb()
Set rstSource = db.OpenRecordset("tblFinal_Results")
Set rstDest = db.OpenRecordset("tblBalances")
blnHitZero = False
txtCurrentProdID = rstSource("Prod_ID")

Do While Not rstSource.EOF
Do While txtCurrentProdID = rstSource("Prod_ID")
If rstSource("Balance") = 0 Then
txtCurrentProdID = rstSource("Prod_ID")
blnHitZero = True
Else
If blnHitZero = False Then
With rstDest
.AddNew
Set rstDest("PROD_ID") = rstSource("Prod_ID")
Set rstDest("WC_ID") = rstSource("WC_ID")
Set rstDest("Balance") = rstSource("Balance")
.Update
End With
End If
rstSource.MoveNext
Loop
txtCurrentProdID = rstSource("Prod_ID")
blnHitZero = False
Loop

End Sub


Klatuu said:
I think this will require VBA. You will need to be sure your recordset is
ordered by Prod_ID and WC_ID. It appear that the WC_ID for each Prod_ID.
Hope that is true, or if there is a date field that appear in cronological
order, you could use that. You will need a table to store the results. Here
is the Air code:

Dim blnHitZero as Boolean
Dim txtCurrentProdID as String
Dim rstSource as Recordset
Dim rstDest as Recordset

set rstSouce = currentdb.YourTable
set rstDest = currentdb.NewTabe
bnlHitZero = False
txtCurrentProdId = rstSource.[Prod_Id]

Do While Not rstSource.Eof
Do While txtCurrentProd_ID = rstSource.[Prod_ID]
If rstSource.Balance = 0 then
txtCurrentProdId = rstSource.[Prod_Id]
blnHitZero = True
Else If blnHitZero Then
With rstDest
.Addnew
Set rstDest.[Prod_ID] = rstSource.[Prod_ID]
Set rstDest.[WC_ID] = rstSource.[WD_ID]
Set rstDest.[Balance] = rstSource.[Balance]
.Update
End With
End If
rstSource.Movenext
Loop
txtCurrentProdID = rstSource.[Prod_ID]
blnHitZero = False
Loop

Not at all tested, and syntax may not be correct, but that is the basic
approach

Kirk P. said:
I need to find all records within each Prod_ID that occur AFTER the balance
has reached zero. In the example below, Rec_ID's 4, 7, 8, 13 and 14 would be
records I'm trying to retrieve.

Any ideas?


Rec_ID Prod_ID WC_ID Balance
1 950698 137X (192.29)
2 950698 1492 (300.22)
3 950698 1572 0.00
4 950698 1687 (845.49)
5 955480 371224 2,667.24
6 955480 371569 0.00
7 955480 371570 933.30
8 955480 371571 (693.54)
9 969233 1169 (24,699.16)
10 969233 137X (24,557.34)
11 969233 1492 (24,638.77)
12 969233 1572 0.00
13 969233 1599 (23,055.40)
14 969233 2241 (29,456.58)
Click to expand...
Click to expand...
 
Sorry, I think if you just remove the Set and leave the rest of the line, it
might be olay. I told you it may not be perfect, but I'm glad it is a start.

Kirk P. said:
I've figured out and corrected the syntax errors you mentioned. When I run
the sub, I'm getting a compile error "invalid use of property" on the Set
rstDest("PROD_ID") = rstSource("Prod_ID") line. Any ideas?

Public Sub KirkCode()

Dim blnHitZero As Boolean
Dim txtCurrentProdID As String
Dim db As Database
Dim rstSource As Recordset
Dim rstDest As Recordset

Set db = CurrentDb()
Set rstSource = db.OpenRecordset("tblFinal_Results")
Set rstDest = db.OpenRecordset("tblBalances")
blnHitZero = False
txtCurrentProdID = rstSource("Prod_ID")

Do While Not rstSource.EOF
Do While txtCurrentProdID = rstSource("Prod_ID")
If rstSource("Balance") = 0 Then
txtCurrentProdID = rstSource("Prod_ID")
blnHitZero = True
Else
If blnHitZero = False Then
With rstDest
.AddNew
Set rstDest("PROD_ID") = rstSource("Prod_ID")
Set rstDest("WC_ID") = rstSource("WC_ID")
Set rstDest("Balance") = rstSource("Balance")
.Update
End With
End If
rstSource.MoveNext
Loop
txtCurrentProdID = rstSource("Prod_ID")
blnHitZero = False
Loop

End Sub


Klatuu said:
I think this will require VBA. You will need to be sure your recordset is
ordered by Prod_ID and WC_ID. It appear that the WC_ID for each Prod_ID.
Hope that is true, or if there is a date field that appear in cronological
order, you could use that. You will need a table to store the results. Here
is the Air code:

Dim blnHitZero as Boolean
Dim txtCurrentProdID as String
Dim rstSource as Recordset
Dim rstDest as Recordset

set rstSouce = currentdb.YourTable
set rstDest = currentdb.NewTabe
bnlHitZero = False
txtCurrentProdId = rstSource.[Prod_Id]

Do While Not rstSource.Eof
Do While txtCurrentProd_ID = rstSource.[Prod_ID]
If rstSource.Balance = 0 then
txtCurrentProdId = rstSource.[Prod_Id]
blnHitZero = True
Else If blnHitZero Then
With rstDest
.Addnew
Set rstDest.[Prod_ID] = rstSource.[Prod_ID]
Set rstDest.[WC_ID] = rstSource.[WD_ID]
Set rstDest.[Balance] = rstSource.[Balance]
.Update
End With
End If
rstSource.Movenext
Loop
txtCurrentProdID = rstSource.[Prod_ID]
blnHitZero = False
Loop

Not at all tested, and syntax may not be correct, but that is the basic
approach

Kirk P. said:
I need to find all records within each Prod_ID that occur AFTER the balance
has reached zero. In the example below, Rec_ID's 4, 7, 8, 13 and 14 would be
records I'm trying to retrieve.

Any ideas?


Rec_ID Prod_ID WC_ID Balance
1 950698 137X (192.29)
2 950698 1492 (300.22)
3 950698 1572 0.00
4 950698 1687 (845.49)
5 955480 371224 2,667.24
6 955480 371569 0.00
7 955480 371570 933.30
8 955480 371571 (693.54)
9 969233 1169 (24,699.16)
10 969233 137X (24,557.34)
11 969233 1492 (24,638.77)
12 969233 1572 0.00
13 969233 1599 (23,055.40)
14 969233 2241 (29,456.58)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top