Access Update Table

[jaabaar]

I have a problem when I need to remove the duplicate values in EstNo and EstWt when you have multiple ReleaseOp for example the 3 first lines below all showing ReleaseOp 1 should leave only the first record with the values 23 and 89. ReleaseAutoID is the Unique ID that identifies the record.
= the numbers in brackets below should be null.

ReqNo DeploymentYear ReleaseOp ReleaseAutoID EstNo EstWt
2 2013 1 1 23 89
2 2013 1 2 [23] [89]
2 2013 1 3 [23] [89]
2 2013 2 4 22 87
2 2013 2 5 [22] [87]
2 2013 3 6 24 90

My attempt as follow can’t seem to do what I want to do, it only checks if values are the same I am stuck on the condition and checks. I hope you can help and my problem makes sense.

Public Sub MakeTableObsFarmEst()

Dim rsExistData, rsSelectData, RsAboveAccessColumnLimit As ADODB.Recordset
Dim conn As ADODB.Connection
Dim cnnDB As ADODB.Connection

Dim strSelectData, strExistData, strRsAboveAccessColumnLimit As String
Dim strSQLInsert As String
Dim strUpdateSQL As String

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control, Source As String
Dim Response As VbMsgBoxResult
Dim lngNextID, lngEmploymentID, lngETTrainingID As Long
Dim lngGiveValue As String
'Dim i As Integer

Set rsExistData = New ADODB.Recordset
Set rsSelectData = New ADODB.Recordset

Set conn = New ADODB.Connection
Set cnnDB = CurrentProject.Connection
'conn.Open GstrConnection
Set RsAboveAccessColumnLimit = New ADODB.Recordset

nl = vbNewLine & vbNewLine

strSelectData = "Select * from qry_Caging_Farm_Obs_Est_Released_BFT_Layout"
rsSelectData.Open strSelectData, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

While Not rsSelectData.EOF

If rsExistData.State = adStateOpen Then
rsExistData.Close
Set rsExistData = Nothing
Set rsExistData = New ADODB.Recordset
End If

strExistData = "Select * from qry_Caging_Farm_Obs_Est_Released_BFT_Layout where [Release Op #]=" & rsSelectData.Fields("Release Op #") & " AND ReleaseAutoID=" & rsSelectData.Fields("ReleaseAutoID")

rsExistData.Open strExistData, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

While Not rsExistData.EOF
If rsSelectData.Fields("ReleaseAutoID") = rsExistData.Fields ("ReleaseAutoID") Then
'do nothing
Else
Debug.Print "Update Action to Clear Field"
End If

rsExistData.MoveNext

Wend
rsSelectData.MoveNext
Wend


If rsSelectData.State = adStateOpen Then
rsSelectData.Close
Set rsSelectData = Nothing
End If

If rsExistData.State = adStateOpen Then
rsExistData.Close
Set rsExistData = Nothing
End If

Exit Sub

 

[jaabaar]

I hope someone can help me soon.

 

[Andrzejek]

I would try something like this:

First, I would like to SEE your example (use [ignore][pre] some text [/pre][/ignore] to PREserve spacing):

[pre]
ReqNo DeploymentYear ReleaseOp ReleaseAutoID EstNo EstWt
2 2013 1 1 23 89
2 2013 1 2 [23] [89]
2 2013 1 3 [23] [89]
2 2013 2 4 22 87
2 2013 2 5 [22] [87]
2 2013 3 6 24 90
[/pre]

Second, I need the records that I would like to keep unchanged:
[tt][blue]
SELECT MIN(ReleaseAutoID)
FROM yourTable
GROUP BY ReleaseOp, EstNo, EstWt[/blue][/tt]

Then, the rest is easy:
[tt]
UPDATE yourTable
SET EstNo = NULL, EstWt = NULL
WHERE ReleaseAutoID NOT IN(
[blue]SELECT MIN(ReleaseAutoID)
FROM yourTable
GROUP BY ReleaseOp, EstNo, EstWt[/blue])
[/tt]

Code NOT tested.

Have fun.

---- Andy

 

[jaabaar]

Thank you for you advice

This are real Records
Before:
ReqNo DeploymentYear ReleaseOp ReleaseAutoID EstNo EstWt
2 2013 1 1 23 89
2 2013 1 2 [23] [89]
2 2013 1 3 [23] [89]
2 2013 2 4 22 87
2 2013 2 5 [22] [87]
2 2013 3 6 24 90

After - The result I Want
ReqNo DeploymentYear ReleaseOp ReleaseAutoID EstNo EstWt
2 2013 1 1 23 89
2 2013 1 2
2 2013 1 3
2 2013 2 4 22 87
2 2013 2 5
2 2013 3 6 24 90


Thank you very much for your help.

 

[jaabaar]

Thank you for you advice

This are real Records
Before:
[pre]
ReqNo DeploymentYear ReleaseOp ReleaseAutoID EstNo EstWt
2 2013 1 1 23 89
2 2013 1 2 [23] [89]
2 2013 1 3 [23] [89]
2 2013 2 4 22 87
2 2013 2 5 [22] [87]
2 2013 3 6 24 90 [/pre]

After - The result I Want
[pre]
ReqNo DeploymentYear ReleaseOp ReleaseAutoID EstNo EstWt
2 2013 1 1 23 89
2 2013 1 2
2 2013 1 3
2 2013 2 4 22 87
2 2013 2 5
2 2013 3 6 24 90 [/pre]

Thank you very much for your help. I think I manged to do it wright the formating. What else I need to change in my code. I hope you can help.

 

[dhookom]

I would first create a ranking query [qrnkGroup] with the following SQL:

SELECT Count(qry_Caging_Farm_Obs_Est_Released_BFT_Layout.ReqNo) AS GroupRank, 
qry_Caging_Farm_Obs_Est_Released_BFT_Layout.ReqNo, qry_Caging_Farm_Obs_Est_Released_BFT_Layout.DeploymentYear, 
qry_Caging_Farm_Obs_Est_Released_BFT_Layout.ReleaseOp, qry_Caging_Farm_Obs_Est_Released_BFT_Layout.Estno, 
qry_Caging_Farm_Obs_Est_Released_BFT_Layout.EstWt, qry_Caging_Farm_Obs_Est_Released_BFT_Layout_1.ReleaseAutoID
FROM qry_Caging_Farm_Obs_Est_Released_BFT_Layout 
INNER JOIN qry_Caging_Farm_Obs_Est_Released_BFT_Layout AS qry_Caging_Farm_Obs_Est_Released_BFT_Layout_1
 ON (qry_Caging_Farm_Obs_Est_Released_BFT_Layout.ReleaseOp = qry_Caging_Farm_Obs_Est_Released_BFT_Layout_1.ReleaseOp)
 AND (qry_Caging_Farm_Obs_Est_Released_BFT_Layout.DeploymentYear = qry_Caging_Farm_Obs_Est_Released_BFT_Layout_1.DeploymentYear)
 AND (qry_Caging_Farm_Obs_Est_Released_BFT_Layout.ReqNo = qry_Caging_Farm_Obs_Est_Released_BFT_Layout_1.ReqNo)
WHERE qry_Caging_Farm_Obs_Est_Released_BFT_Layout.ReleaseAutoID<=[qry_Caging_Farm_Obs_Est_Released_BFT_Layout_1].[ReleaseAutoID]
GROUP BY qry_Caging_Farm_Obs_Est_Released_BFT_Layout.ReqNo, qry_Caging_Farm_Obs_Est_Released_BFT_Layout.DeploymentYear,
 qry_Caging_Farm_Obs_Est_Released_BFT_Layout.ReleaseOp, qry_Caging_Farm_Obs_Est_Released_BFT_Layout.Estno,
 qry_Caging_Farm_Obs_Est_Released_BFT_Layout.EstWt, qry_Caging_Farm_Obs_Est_Released_BFT_Layout_1.ReleaseAutoID;

Then create a union query to get the results:

SELECT ReqNo, DeploymentYear, ReleaseOp, ReleaseAutoID, Estno, EstWt
FROM qrnkGroup
WHERE GroupRank=1
UNION ALL
SELECT ReqNo, DeploymentYear, ReleaseOp, ReleaseAutoID, Null, Null
FROM qrnkGroup
WHERE GroupRank >1
ORDER BY 1,2,3,4;

Results:
[pre]
ReqNo DeploymentYear ReleaseOp ReleaseAutoID Estno EstWt
2 2013 1 1 23 39
2 2013 1 2
2 2013 1 3
2 2013 2 4 22 87
2 2013 2 5
2 2013 3 6 24 90[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[jaabaar]

Dear dhookom,
Thank you so much - it really helped a lot and has solved my problem. I was really stuck and your solution was just two simple queries which reduced my code a lot. I appreciate you taking the time to read my question and your effort in replying with a complete solution.
Jaabaar

 

[jaabaar]

Dear Andrzejek,
Thank you also for your time and effort and all the advise you have given.
It has helped a lot.
Jaabaar