merging non-normalized with normalized tables

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

Guest

I designed a normalized database for our research to house vegetation data
where one plot has many species records in it. In my database, these data
are stored in a one-to-many relationship where one plot has many species row
entries. Thus my datastructure looks like:

ID Plot ID# Date Species Field 3...
1 # Species 1 Species1 abundance, etc...
2 # Species 2 Species2 abundance, etc..
3 # Species3 Species3 abundance, etc...

Now i am faced with the task of merging this table structure with a
non-normalized table where each plot has one row and the 250 species! are
listed across the top each species in a separate column. Table structure is
like this:

ID Plot ID Date Species 1 Species 2 Species 3.... Species
250
1
2
3 ... on to plot 1000+

I keep track of plots by Plot ID # (primary key).

I cannot figure out how to combine these 2 tables. I have tried transposing
in EXCEL, various forms of crosstabs, but have not been able to get it to
work. I am not that advanced and have only read about union queries, but it
seems difficult to implement for 255 columns, but don't know even where to
start. any suggestions would be appreciated.
 
How about executing 250 append query statements to add the correct records
to your normalized table.

This kind of thing:

Dim db As DAO.Database
Dim strField As String
Dim strSQL As String
Set db = dbEngine(0)(0)
For i = 1 to 250
strField = "[Species ] " & i & "]"
strSQL = "INSERT INTO Table1 (PlotID, PlotDate, Species) " & _
"SELECT [Plot ID#], [PlotDate], " & strField & _
" FROM BadTable WHERE " & strField & " Is Not Null;"
db.Execute strSQL, dbFailOnError
Debug.Print strField, db.RecordsAffected
Next
 
Mark

To what end? What purpose do you have for "flattening" your data? I ask in
case there might be a different "solution" to your underlying business need.
 
Are you wanting to merge the non-normalised data into the normalised
table, or the normalised data into the non-normalised table?
The solution will differ depending on which you want to do.

Peter Franklin
 
Back
Top