Table conversion

[stpiepgr]

I have a question that I hope someone can help me with. I have a database of lake vegetation survey data. The surveys date back to the 40's and always methods for recording and logging the data changes. The current table format has a single record for each sample station (total number varies by lake) for each survey data. The species found are listed in columns 1-20. This makes it difficult to summarize surveys by species and calculate confidence intervals and such.

Essentially I want to combine the 20 columns into one but also include the basic attribute for each records. I would like to create a seperate record for each species found at each sample station. Each record must have the lake ID, survey date and survey id# included. Is there an automated way to do this conversion or do I need to do it manually. In case I haven't explained this well...I included an example. Thanks in advance for any help as this newbie needs it!
>>>>>>>>>
Current:
Proj# LKID Date Station Spec1 Spec2 Spec3
0001 01000 11/20/02 01 X Y Z
0001 01000 11/20/02 02 Z

Future:
Proj# LKID Date Station Species
0001 01000 11/20/02 01 X
0001 01000 11/20/02 01 Y
0001 01000 11/20/02 01 Z
0001 01000 11/20/02 02 Z

 

[Jack Cowley]

You want two tables:

tblLake
LakeID (Primary Key preferably AutoNumber)
Name
Date
...other fields..

tlbSpecies
SpeciesID (Primary Key and Autonumber)
LakeID (Foreign Key and Long Integer)
SpeciesName
...other fields....

For every species field in the current lake record you want to put that species into the Species table with the same LakeID as in the current table. For example:

SpeciesID LakeID SpeciesName
1 .............1 ........X
2 .............1 ........Y
3 .............1 ........Z
4 .............2 ........A
5 .............2 ........B

It will not be easy to move the data from the existing db, but with code and diligence you should be able to do it...

I hope this make sense!

Jack

 

[pdx_man]

I agree with Jack, you should have them in separate tables. After all, we are dealing with a Relational Database. But, I will leave that for you to figure out For what you requested, this should work.

Public Sub Sweep_Table()
Dim dbs As Database
Dim rst_Init, rst_Final As Recordset
Dim i As Byte

    Set dbs = CurrentDb
    Set rst_Init = dbs.OpenRecordset("Tbl_Initial")
    Set rst_Final = dbs.OpenRecordset("Tbl_Final")
    
    Do While Not rst_Init.EOF
        i = 4
        Do While Not IsNull(rst_Init.Fields(i)) And i < 24
            rst_Final.AddNew
            rst_Final!Proj = rst_Init!Proj
            rst_Final!LKID = rst_Init!LKID
            rst_Final!Date_Of = rst_Init!Date_Of
            rst_Final!Station = rst_Init!Station
            rst_Final!Species = rst_Init.Fields(i)
            i = i + 1
            rst_Final.Update
        Loop
        rst_Init.MoveNext
    Loop

End Sub

 

[stpiepgr]

Thanks for the help. I realize they need to be two seperate tables. There is a great deal of additional info in the original table that doesn't need to be in the new species table. I should have mentioned that in my first post. Thanks for the code. I hope my limited programming skills will get it to work or I will be back with more questions