merging non-normalized with normalized tables

[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.

 

[Allen Browne]

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

 

[Jeff Boyce]

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.

 

[Peter Franklin]

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