iterate through database 4

[jagsdj]

Hello all...

I've been working on this for days and finally thought I'd post and see what other ideas I can get.

I have two tables:
table1:
station_ID DO BOD
1 5 <20
2 <10 6

table2:
station_ID DO DO_Commnet BOD BOD_Comment
1 5 (empty) 20 <
2 10 < 6 (empty)


I have the data in table1 and I need that data to be transferred to table2, but before it does I need to qc it and make sure if the fields contain &quot;<&quot; or &quot;>&quot;, then to parse out the &quot;>&quot; or &quot;<&quot; and put them in separate fields in table2.

Does anybody have any idea on how to go about doing this?

any and all help is greatly appreciated.

 

[JunglesMcBeef]

G'day,

What are the field sizes of DO and BOD?

 

[jagsdj]

thanks for the reply jungleMcBeef,

the field sizes for the fields in the database are all 50 (is this waht you were looking for?)

Thanks.

 

[JunglesMcBeef]

Yes,

But what would the max (and min) size of DO and BOD be? eg. 3 chars

 

[jagsdj]

Sorry about that J.McBeef

Well, the DO and BOD are coming in through an import (Transferspreadsheet) to an access table (table1). So, there really isn't a min or max value from what i would see. Now, I do have to check to make sure that DO is between 1 and 10 and BOD is between 3 and 25. This is just one check that has to be done as well...

hope that helps...

thanks again!

 

[JunglesMcBeef]

Ok,

First of all, you only need one table to do this. Keep the table with the five fields. Create a form with a single command button. Add this code to the button:
*******************************
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strDO As String
Dim strBOD As String
Dim intCount As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset(&quot;Table1&quot;, dbOpenTable)

rs.MoveFirst

Do Until rs.EOF
strDO = rs.Fields(&quot;DO&quot
strBOD = rs.Fields(&quot;BOD&quot

If Left(strDO, 1) = &quot;<&quot; Or Left(strDO, 1) = &quot;>&quot; Then
rs.Edit
rs.Fields(&quot;DO&quot = Mid(strDO, 2, Len(strDO))
rs.Fields(&quot;DO_Comment&quot = Left(strDO, 1)
rs.Update
End If

If Left(strBOD, 1) = &quot;<&quot; Or Left(strBOD, 1) = &quot;>&quot; Then
rs.Edit
rs.Fields(&quot;BOD&quot = Mid(strBOD, 2, Len(strBOD))
rs.Fields(&quot;BOD_Comment&quot = Left(strBOD, 1)
rs.Update
End If

rs.MoveNext
Loop

Set db = Nothing
Set rs = Nothing
**************************************

Change the &quot;Table1&quot; table to the name of your table. This code will automatically process your table and do the work for you.

Good Luck!!

 

[JunglesMcBeef]

If you want to keep your current 2 table setup, use this code instead:

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim strDO As String
Dim strBOD As String
Dim intCount As Integer

Set db = CurrentDb()
Set rs1 = db.OpenRecordset(&quot;Table1&quot;, dbOpenTable)
Set rs2 = db.OpenRecordset(&quot;Table2&quot;, dbOpenTable)

rs1.MoveFirst

Do Until rs1.EOF
strDO = rs1.Fields(&quot;DO&quot
strBOD = rs1.Fields(&quot;BOD&quot

rs2.AddNew

If Left(strDO, 1) = &quot;<&quot; Or Left(strDO, 1) = &quot;>&quot; Then
rs2.Fields(&quot;DO&quot = Mid(strDO, 2, Len(strDO))
rs2.Fields(&quot;DO_Comment&quot = Left(strDO, 1)
End If

If Left(strBOD, 1) = &quot;<&quot; Or Left(strBOD, 1) = &quot;>&quot; Then

If rs2.Fields(&quot;DO&quot <> &quot;&quot; Then
rs2.Fields(&quot;BOD&quot = Mid(strBOD, 2, Len(strBOD))
rs2.Fields(&quot;BOD_Comment&quot = Left(strBOD, 1)
Else
rs2.Fields(&quot;BOD&quot = Mid(strBOD, 2, Len(strBOD))
rs2.Fields(&quot;BOD_Comment&quot = Left(strBOD, 1)
End If
End If

rs2.Update
rs1.MoveNext
Loop

Set db = Nothing
Set rs1 = Nothing
Set rs2 = Nothing


Set your station_ID field in table2 to autonumber then fire away!!!

 

[jagsdj]

J.McBeef...I sincerly thank you for your time and effort, I will try this out tonite and keep you posted as to my progress.

As a side note, there are about 40+ parameters (DO, BOD, PH, Zinc, Cadmium, ... n), is there anyway to automate the field selection routine instead of declaring each one as you did below:
strDO = rs1.Fields(&quot;DO&quot
strBOD = rs1.Fields(&quot;BOD&quot


Thank you again!!

 

[Eupher]

Good work, Jungles. I came up with an Append Query solution, but I think yours is more concise and easier to understand. A star for you.

Ken S.

 

[Eupher]

jagsdj,
Yes, you can iterate through the fields in your recordset(s) and as long as your field name scheme is consistent, you should be able to do what you want. Here's a modification of Jungles' code:

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim fld As DAO.Field
    
    Set db = CurrentDb()
    Set rs1 = db.OpenRecordset("tblTable1", dbOpenTable)
    Set rs2 = db.OpenRecordset("tblTable2", dbOpenTable)
    
    rs1.MoveFirst
        
    Do Until rs1.EOF
    
        rs2.AddNew

        For Each fld In rs1.Fields
        If Left(fld, 1) = &quot;<&quot; Or Left(fld, 1) = &quot;>&quot; Then
            rs2.Fields(fld.Name) = Mid(fld, 2, Len(fld))
            rs2.Fields(fld.Name & &quot;_Comment&quot;) = Left(fld, 1)
            Else
                rs2.Fields(fld.Name) = rs1.Fields(fld.Name)
        End If
        Next fld
    
        rs2.Update
        rs1.MoveNext
    Loop
    
    Set db = Nothing
    Set rs1 = Nothing
    Set rs2 = Nothing

HTH...

Ken S.

 

[JunglesMcBeef]

Yep,

just about to post the same thing myself!! Good stuff Ken. If I'm right though, I think the first field of the second table needs to be a text field and not an autonumber like I originally said.

 

[Eupher]

Heh, heh...

Yes, Jungles, if you try to write to an autonumber you'll get an error. And once there's data in the table, you can't change it to an autonumber. It can be a regular number field, though.

Ken S.

 

[jagsdj]

Jungles and Eupher...

I want to thank you very much for taking the time to help me out, both of your posts helped quite a bit in getting me started.

A start for both of you...


Thanks!!!

If I wanted to compare the value of each field to another field in the database and error log it if it does not fall within the range, would I just include another IF/THEN or...??

Thanks again!

 

[jagsdj]

Re-Read my post and don't think i was too clear.

table1:
parameters Min Max
parameter1 0 10
parameter2 5 9

table2:
date parameter1 parameter2
4/2/03 11 8

I would like to see if parameter1 in table2 is within the range specified in table1, if it is, then to add to table 3 (exact data structure as table2), if not, then to log the data in an error table(error table: row number, error Description).

I'm just not sure of how to go about comparing the values --

any ideas/help is greatly appreciated.

jagsdj

 

[Eupher]

jagsdj, are there lots of rows in table1 or do the parameter values in table1 change frequently?

Ken S.

 

[jagsdj]

Ken,

Thanks for the reply.

There is a list of about 40 parameters in table 1 and yes, the min/max is different for each parameter.

I appreciate your time and effort in helping me.

Thank You.

jags

 

[jagsdj]

Ken,

Was this the information you were looking for?

I'm guessing this would be some nested for loops?

Thanks again

 

[JunglesMcBeef]

Hey again Jags and Eupher,

If you only need to check parameter1, I'm thinking all you would need to do is(after declaring variables, setting objects etc):
*******************************************

table1.MoveFirst
table2.MoveFirst

Do Until table2.EOF

If table2!parameter1 >= table1!Min And _
table2!parameter1 <= table1!Max Then
<Add to table3 code here>
Else
<Add to error table code here>
End if

table2.MoveNext

Loop

******************************************

Is this what you were after?

 

[jagsdj]

Thanks J.Beef for the reply...

Acutally, i needed to check every parameter in table1 that is in table2.

This does set me in the right direction, any further info would be great as well. This is very new to me and I appreciate the responses being very helpful.

Thanks!

 

[Eupher]

Hi, jags -

Does every record have all 40+ parameters? Are &quot;parameter1&quot;, &quot;parameter2&quot; really the field names, or just a convention for purposes of our discussion? Does each parameter represent a certain &quot;thing&quot;? I'm asking because I'm trying to get a sense of how your tables work together and whether there are some underlying database normalization issues.

Also, I realize that the min/max values are different for each parameter, but do those values change? That is, will min/max for parameter1 always be 0/10? Or do you need the ability to change those values?

Ken S.