Data input - month horizontal; countries vertical

[enfinity]

Hi *,

Currently, we're using an Excel sheet that we use for data input. The Excel sheet has the format:

____________________JAN________FEB________MAR
GERMANY
AUSTRIA
SWITZERLAND

Now, we'd like to use an Access database to save these records. The table I've already created looks something like this:

DATE____COUNTRYID____VALUE


From an data input perspective, it is more difficult to input data in the Access database. I was wondering if someone has an idea of how I can improve data input. Is it possible to get the convenient data input of Excel AND the benefits of a DBMS?

Thank you!
Steve

 

[pbaldy]

You could use either an unbound form or a form bound to a local table with a non-normalized structure (the form has your "Excel" structure). When the user is done they click on a "Save" button, behind which you insert the data into your normalized table.

 

[enfinity]

Thanks Paul!

I'm interested in the second option aka creating a non-normalized table and convert it by the click of a button to a normalized structure. Are there any examples available of how to do that? In my case I would have the denormalized table:

COUNTRYID_01.01.2008___01.02.2008___01.03.2008___...
1__________XXX_________XXX_________XXX_________...
2__________XXX_________XXX_________XXX_________...
3__________XXX_________XXX_________XXX_________...

that I'd like to convert into this format:

COUNTRYID__DATE________VALUE
1___________01.01.2008___XXX
1___________01.02.2008___XXX
.
.
.

Any ideas of how to do this (via VBA or another method...)

Thank you!
Steve

 

[namliam]

Just run some queries...
Insert into normalized table
Select countryID, 01.01.2008, value from <denormalized table>

next:
Insert into normalized table
Select countryID, 01.02.2008, value from <denormalized table>

Next
Insert into normalized table
Select countryID, 01.03.2008, value from <denormalized table>

Etc..

You can (and maybe should) do all this in code... but seperate queries will work as well.

Good Luck!

 

[enfinity]

Thanks!

One question though:

I think this part is not going to work:

Select countryID, 01.02.2008, value from <denormalized table>

Value is not a column in the denormalized table but "below" the column with the name "01.02.2008". I think it's gonna be a little more difficult b/c "Value" is not a column in the denormalized table.

 

[namliam]

Select countryID, 01.02.2008, value from <denormalized table>

The value is the value in the column... in this case the 01.02.2008 is a hardcoded text to "transpose" the column name.... So to be more precise...
Select CountryID, #01/01/2008#, [01.01.2008] From <denormalized table>
Select CountryID, #02/01/2008#, [01.02.2008] From <denormalized table>
Select CountryID, #03/01/2008#, [01.03.2008] From <denormalized table>
etc.

Note the American format of the date!

 

[enfinity]

Thanks! Good point

 

[enfinity]

Alright, I did some VBA coding and came up with this. Only problem: I have to confirm every time when the DoCmd.RunSQL strSQL is executed (which is 3x12 = 36 times!!!). Is it possible to disable the prompt?

Private Sub cmdTransformCountry_Click()
  
  Dim BP_Year As Integer
  Dim CID As Integer
  Dim Monat As Integer
  Dim strSQL As String
  
  Dim MonatName As Variant
  MonatName = Array("JAN", "FEB", "MAR", "APR", "MAR", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC")

  
  BP_Year = 2008
    
  For CID = 1 To 3
    For Monat = 1 To 12
      strSQL = "INSERT INTO bp_country_test ([COUNTRY_ID], [FORECAST_MONTH], [FORECAST_VALUE]) " & _
        "SELECT [COUNTRY_ID], #" & Monat & "/1/" & BP_Year & "#, [" & MonatName(Monat - 1) & "] " & _
        "FROM bp_country_temp " & _
        "WHERE [COUNTRY_ID] = " & CID & " AND [BP_YEAR] = " & BP_Year & ";"
      DoCmd.RunSQL strSQL
    Next Monat
  Next CID

End Sub

 

[namliam]

2 ways:
1) Best way
Currentdb.Execute strSQL

2) Disable warnings ("not so good" way)
Docmd.Setwarnings false
Docmd.RunSQL
Docmd.SetWarnings True

 

[pbaldy]

Looks like this all got resolved while I slept! The only thing I might add is a test for Nulls, if the user could leave any of those fields blank. I use this technique in a couple of applications, and in my case some of the fields are optional, so I have to test for Null.

 

[namliam]

Looks like this all got resolved while I slept!

I hop you dont mind me stepping in while you were catching some ZZZZs Paul

 

[pbaldy]

No problemo, MailDude.

 

[enfinity]

Thank you guys! Do you know a simple way to make sure that the underlying db is updated when a value in the un-normalized table is changed? The code shown above only inserts the values but if the user changes values afterwards, no changes are committed to the normalized db! (I could insert another update loop but maybe you guys know something easier

Steve

 

[namliam]

Seperate tables = seperate updates unfortunatly

 

[pbaldy]

In my case, the unnormalized table is emptied after the normalized table is populated. I let them add records through it, but if they want to work with them later, I present it to them in a normalized manner. I wouldn't try to maintain the data in two places, so I wouldn't leave the data in your unnormalized table either. If they edit a lot and presenting the normalized data is impractical, I might load the requested records back into the unnormalized table/form, then update the normalized data when the user is done.

 

[namliam]

Must agree with Paul... Keep the data in one place (as much as possible)