Access automation 1

[DanKay1]

File has 10,000 records, I need to automate this process through VBA. I tried to write a code but i don't know how to approach. Your solutions will be greatly appreciated thanks.

Database has 3 Fields, Policy, Amount and Coverage. Base on Policy field and Amount I need to assign Coverage. If the database doesnt contain duplicate policies i need to assign 01 in coverage field. If it does I need to put 01 in coverage field for that policy and 30 for the next one. If there is a third occurence, then coverage field should have 31.

Note: Smallest Coverage has to be assigned to biggest Amount first.

Database
Policy Amount Coverage
01 20
02 25
03 15
01 40


Policy Amount Coverage
01 20 30
02 25 01
03 15 01
01 40 01

 

[jjlogan]

DanKay,
Suggest you write a Subroutine in a standard module (go to Modules). In the subroutine, dim certain variables for temporarily holding values, dim ADO objects (connection and recordset). Set the connection to CurrentProject and set the recordset variable to a query you have already prepared (this query should sort the records in your table in the order in which you would want to view each record if you were physically inspecting the records one-by-one.). Now, with the recordset variable defined as the query set, you do looping through the recordset and inspect each record with the logic you noted above. Update the Coverage field as appropriate using the ADO update syntax - see Help files.). I think a module, as described above, is necessary or at least easier than a complicated IIF function. I can supply a copy of similar code to above, but recommend you first look at ADO syntax coverage in a Help file - just look at Connection and Recordset objects however.
Jeff
Jeff

 

[DanKay1]

Sub Coverage()
Dim rs As ADODB.Recordset
Dim Conn As ADODB.Connection
Dim cov1 As String
Dim Response As String

Set Conn = New ADODB.Connection
Conn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\Documents and Settings\Desktop\bma.mdb;"

Set rs = Conn.Execute("SELECT * FROM tblmasternew ORDER BY ""taix-pol"" ASC")

cov1 = "a"
While Not rs.EOF
If cov1 = "a" Then
Conn.Execute "UPDATE tblmasternew SET taixcov='01'"
cov1 = "b"
Response = MsgBox(cov1, vbOKOnly, "A")
End If
If cov1 = "b" Then
Conn.Execute "UPDATE tblmasternew SET taixcov='30'"
cov1 = "a"
Response = MsgBox(cov1, vbOKOnly, "B")
End If
rs.MoveNext
Wend


'Close the connection
Set Conn = Nothing
Set rs = Nothing
End Sub

I started writting Update query just to get updates everyother entry and it doesnt work for some reason. If you can provide pseudo code that will be great. Thanks

 

[cgarts]

Maybe I'm missing something, but it looks like the coverage values, 01, 30 , 31 are redundant information, and that the information about policy coverage is not only already in your database, but intrinsic in the same table. If I have that all correctly, then I would use similar code to identify and make note of 2nd and third policies, but to display that information on forms and reports only, but not to write that info back to your tables. Essentially you are writing calculated values back to tables, and that is often a way to generate problems down the road.

What happens if you assign a 30 or 31 value to a policy and then the first policy is cancelled or goes away? Will an update be run again every time a policy is created, exipred, or removed? If not, there's a likelihood that the "coverage" field will gather an increasing number of errors.

Best,

C

 

[DanKay1]

cgarts,

Well, we receive that information from client every quarter, and our system doesn't allow duplicate policies. We reprocess their information everytime we receive it from them. If the Policy repeat multiple times the system then looks for coverages, if the coverages are different it accepts them.

There wont be any errors since the file is reprocessed everytime and the old information is removed completely.

Thanks for the tip.

Its just our files are getting bigger and bigger from month to month and wanted to come up with a technique that would automate the process if its possible.

 

[cgarts]

DamKay
When you posed the problem, Policy 01 is entered twice, once for Amount 20, once for amount 40.

That's the duplication I refer to: two records for 01.

Your original wording:
Database
Policy Amount Coverage
01 20
02 25
03 15
01 40

I suspect other wiser database people will agree that writing calculated data to the table can a potential problem.

Also you say your files "are getting bigger and bigger from month to month". I suspect you can delete that unneeded "Coverage" field completely. It looks completely redundant unless it contains other uncalculated info you have not mentioned.

Best,

C

 

[DanKay1]

cgarts,

Note that policy field is not a KEY. All we need to see how many different coverages we receive under Each policy. Then we group by policy and coverage field and we get unique information. About deleting, we delete the whole table and regenerate from scratch every month.

When we get the file from the company, they have coverages equal to 01 for all even for duplicated policies. When we group by policy and coverage it causes us a big problem.

The problem is not with the design, my problem is to automate the process.

Policy Amount Coverage
01 20 30
02 25 01
03 15 01
01 40 01

 

[cgarts]

DanKay,

Sorry to dig around that question at length: but given that your whole set of records sounds like it is entirely deleted and replaced monthly, and that you are not adding lots of records in the interim, but just populate the db all at once, then I'd suspect you are fine to work up the Coverage field as you hope....

It's not my intent to corner you into defending your decisions, but it sounded at first reading like a problem. I did note that "Policy" is not a key.

C

 

[DanKay1]

Well no one seems to know the solution.

 

[cgarts]

DanKay1,

I have been thinking about how to get a number for how many policies share the policy same number, and then update your Coverage correctly.

My inclinationt would be to have a query do a lot of the heavy work for you before you process the Coverage field

Have the query sort first for the Policy Number ascending. That will put the 01, 01, 01 policies together so we can count that as an 01, a 30 , and a 31

Second, have the query sort for Amount ascending so the sequencing of your "Coverage" 01, 30, and 31 is correct.

the SQL:

SELECT TblPolicy.Policy, TblPolicy.Amount, TblPolicy.Coverage
FROM TblPolicy
ORDER BY TblPolicy.Policy, TblPolicy.Amount
WITH OWNERACCESS OPTION;

Your query is named "QryYours" in this pseudocode. You'll need a variable for counting how many times you are at a record for a given Policy. Lets call it LoopCount. We need a variable to "remember" the last Policy so we know when to increment LoopCount up - call that LastPolicy

This is somewhere between code and pseudocode - typed but not working yet so you'll have to fix this up, it's generating errors still but the logic should get you closer to your needed results with some tweeeeking.

' Start Code

Private Sub UpdateMyCoverage()

Dim LastPolicy As Integer
Dim Loopcount As Integer
Dim SetCov As String ' string I think is what you are using

LastPolicy = -100 ' initialize this as a policy number you will never ever use

Loopcount = 0
Set rs1 = CurrentDb.OpenRecordset("QryYours")

rs1.MoveFirst
While Not rs1.EOF()

' MsgBox sr1.Policy

If sr1.Policy = LastPolicy Then
' i.e. there are multiple records sharing a policy number
Loopcount = Loopcount + 1
Select Case Loopcount
Case 0
SetCov = "01"
Case 1
SetCov = "30"
Case 2
SetCov = "31"
Case Else
MsgBox " Houston, we have a problem"

End Select
Else
Loopcount = 0 ' reset loopcount to 0 if we have a new policy number
End If

LastPolicy = rs1.Policy
With rs1
.Edit
.Coverage = SetCov
.Update
.MoveNext
End With

Wend

MsgBox "We are finished updating"

End Sub

' End code



Best,

C

 

[jjlogan]

DanKay,

This last post is the type of module code I was think of. Mine is slightly different but same idea, especially the .edit and .update commands vs. running an update query.
Jeff

 

[cgarts]

jjlogan or anyone who knows the syntax on this sort of work ...

I was trying to sort out the logic for DanKay1, but I am missing something in the code I have suggested for him.

I am getting errors, and would like to be more helpful to DanKay1, and would like to make that sort of code workable for myself in future.

Can any one see the problem in my code?

Thanks,

C

 

[cgarts]

Ok DanKay1,

I have it working.

Code that works for your problem if I understand it correctly.

Updates your initial values for "Coverage" based on the following:
1. From one to three records may share the same "Policy" number.
2. "Amount" will be different if more than one record share a given policy number
3. Records will be sorted by ascending policy number first
and then by ascending amount by "QryYours"
4. First entry for any given policy number is assigned 01 as "Coverage".
Second entry if any is given 30 as "Coverage".
Third entry if any is assigned 31 as "Coverage".
5. Policy is a number data type.
Amount is a number data type.
Coverage is text data type.

' Start Code

Private Sub UpdateMyCoverage()

Dim LastPolicy As Integer
Dim Loopcount As Integer ' the key to assigning Coverage
Dim SetCov As String ' string I think is what you are using
Dim rs1 As DAO.Recordset

LastPolicy = -100 ' initialize this as a policy number you will never ever use
Loopcount = 0
SetCov = "01"
Set rs1 = CurrentDb.OpenRecordset("QryYours")

rs1.MoveFirst
While Not rs1.EOF()

If rs1("Policy") = LastPolicy Then
' i.e. there are multiple records sharing a policy number
' then increment loopcount up
Loopcount = Loopcount + 1
Select Case Loopcount
Case 0
SetCov = "01"
Case 1
SetCov = "30"
Case 2
SetCov = "31"
Case Else
SetCov = "PROBLEM" ' whatever - data integrity issue
End Select
Else
SetCov = "01"
Loopcount = 0 ' reset loopcount to 0 if we have a new policy number
End If
LastPolicy = rs1("Policy")
rs1.Edit
rs1("Coverage") = SetCov
rs1.Update
rs1.MoveNext
Wend
MsgBox "We are finished updating"
End Sub

' End code