Consolidating columns

[oni29]

I'm extracting data from Oracle into a local Microsoft access database. The data stored in access is then used by a number of local staff. This approach is being used due to issues with creating views in the Oracle database & giving Oracle access to everyone.

One of my local tables has the following six columns:

* CURRENT_VALUE_STR
* CURRENT_VALUE
* NEW_VALUE_DATE
* NEW_VALUE_INT
* NEW_VALUE_STR
* NEW_VALUE

Only one of the six columns starting in CURRENT and/or NEW will be populated at the one time. For example if NEW_VALUE and CURRENT_VALUE_STR, is populated, NEW_VALUE_DATE, NEW_VALUE_INT, NEW_VALUE_STR & CURRENT_VALUE won't be.

Given the amount of white space I'd like to find a way of consolidating the six columns down to two (CURRENT_VALUE & NEW_VALUE). What is the best way of doing this? I'm thinking of an 'INSERT INTO' query, but don't know if that is the best way.

 

[KenHigg]

Can you simply add (concatenate) them all:

myNewField: CURRENT_VALUE_STR & CURRENT_VALUE & NEW_VALUE_DATE etc


???

 

[DCrake]

Simple Software Solutions

Yes you could do that. But can you guarantee that this will be 100% accurate?

I would prefer to use the Nz() approach.

 

[DCrake]

Simple Software Solutions

Sorry got called away.

Ok first you will have to determine which fields in oracle have been populated. You can use a simple IF statement. Then based on the outcome use the correct fields. You may have to use nested iff's if you want to do it via a Insert Into query. However I would much prefer the DAO.Recordset approach. This way you can manipulate the decisions far more easily as you are appending the records one at a time.

Typical Example (code truncated for brevity)

Dim RsA As DAO.Recordset  [COLOR="Green"] '<-Table being read[/COLOR]

Dim RsB as DAO.Recordset   [COLOR="green"]'<-Table being populated[/COLOR]

Set RsA = CurrentDb.OpenRecordset("TableIn")
Set RsB = CurrentDb.OpenRecordset("TableOut")

If Not RsA.EOF Then
   Do Until RsA.EOF

       If IsNull(RsA("New Value")) Then
          RsB.AddNew
           ....Your code here
          RsB.Update
       Else
         ....Your code here
       End If
       RsA.MoveNext
   Loop
   RsA.Close
End If

 Set rsA = Nothing
 Set RsB = Nothing

CodeMaster:

 

[oni29]

Thanks for the suggestions. Due to time factors and my coding skill, I've just gone with the concatenate suggestion.