Data Splash Function

  • Thread starter Thread starter Janetzky
  • Start date Start date
J

Janetzky

Hi All,

I am still trying to find a solution to be used for splashing data into
an Access Cube. I have seven fields determining the data value (eg.
year, period, datatype, order, account, cost center, currency and
value). From an frontend i want to code a form to be able to splash
whole sections of the cube's data. I want to select criterias by using
comboboxes (cmd_cmb_splash_yr, cmd_cmb_splash_per etc.). The user
should be able to select on up to seven criterias. Based on this data
selection, he shall be able to manipulate the selected range of data by
using a second set of combo-boxes (cmd_cmb_copy_yr, cmd_cmb_copy_per
etc.). For instance copying all 2007 January data to make it 2008
August data. I realized already the select portion of the coding, which
looks as follows:

Sub DeleteCubedata()

Dim strSql As String
strSql = "DELETE * FROM Rawdata WHERE True"

If IsNull(Me.cmd_splash_cpy) Then
' proceed
Else
strSql = strSql & " AND [COMP]=" & Me.cmd_splash_cpy & "'"
End If
If IsNull(Me.cmd_splash_yr) Then
' proceed
Else
strSql = strSql & " AND [YEAR]=" & Me.cmd_splash_yr & "'"
End If
If IsNull(Me.cmd_splash_per) Then
' proceed
Else
strSql = strSql & " AND [PER]=" & Me.cmd_splash_per & "'"
End If
If IsNull(Me.cmd_splash_typ) Then
' proceed
Else
strSql = strSql & " AND [DATA]=" & Me.cmd_splash_typ & "'"
End If
If IsNull(Me.cmd_splash_cur) Then
' proceed
Else
strSql = strSql & " AND [CUR]=" & Me.cmd_splash_cur & "'"
End If
If IsNull(Me.cmd_splash_acc) Then
' proceed
Else
strSql = strSql & " AND [ACC]=" & Me.cmd_splash_acc
End If
If IsNull(Me.cmd_splash_cc) Then
' proceed
Else
strSql = strSql & " AND [ORD]=" & Me.cmd_splash_typ & "'"
If IsNull(Me.cmd_splash_ord) Then
' proceed
Else
strSql = strSql & " AND [ORD]=" & Me.cmd_splash_ord & "'"
End If
CurrentDb.Execute strSql, dbFailOnError
End Sub

I honestly don't have clue how to realize the functionality i need......
 
I'm not familiar with "splashing data" ... can you paraphrase what you are
trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The idea is simple. I want to select a range of data by one or up to
seven criterias (combo boxes set 1) in order to copy the whole range of
data by the use of again one or up to seven criterias (Combo boxes set
2).
As an example:
Datastructure table rawdata
Company, Year, Period, Datatype, Account, Cost Center, Order, Value
201,2006,01,Actual,10000, 1000, 9999, 17.5
200,2006,02,Actual,10001,1010,9999,10.2
201,2006,03,Actual,10003, 1020, 9999, 17.6
200,2006,05,Actual,10006,1010,9999,100.2
200,2006,05,Actual,10001,1010,9999,109.0
201,2006,05,Actual,10003, 1020, 9999, 17.9
200,2006,05,Actual,10010,1010,9999,111.3
200,2007,05,Actual,10010,1010,9999,12.6

Now i want to select a specific range of data(combo box set 1): All
Data from Company 200, year 2006, Period 05, Cost center 1010 (again
all seven criterias should be possible at max)
The selection would be:
200,2006,05,Actual,10006,1010,9999,100.2
200,2006,05,Actual,10001,1010,9999,109.0
200,2006,05,Actual,10010,1010,9999,111.3

Now i want to change the criterias (with combo box set 2). For instance
to copy the actual data of 2006 to plan data 2007. change Datatype to:
Plan and Change Year to: 2007. (Again i would need to at max 7
criterias) Then i like to append this data at the end of the table
(rawdata). Data would now look like:
200,2007,05,Plan,10006,1010,9999,100.2
200,2007,05,Plan,10001,1010,9999,109.0
200,2007,05,Plan,10010,1010,9999,111.3

Complete table would now look like:
Company, Year, Period, Datatype, Account, Cost Center, Order, Value
201,2006,01,Actual,10000, 1000, 9999, 17.5
200,2006,02,Actual,10001,1010,9999,10.2
201,2006,03,Actual,10003, 1020, 9999, 17.6
200,2006,05,Actual,10006,1010,9999,100.2
200,2006,05,Actual,10001,1010,9999,109.0
201,2006,05,Actual,10003, 1020, 9999, 17.9
200,2006,05,Actual,10010,1010,9999,111.3
200,2007,05,Actual,10010,1010,9999,12.6
200,2007,05,Plan,10006,1010,9999,100.2
200,2007,05,Plan,10001,1010,9999,109.0
200,2007,05,Plan,10010,1010,9999,111.3

Hopefully my message came across this time. Thanks for your help,
solving this would really brighten my day.

Jeff said:
I'm not familiar with "splashing data" ... can you paraphrase what you are
trying to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi All,

I am still trying to find a solution to be used for splashing data into
an Access Cube. I have seven fields determining the data value (eg.
year, period, datatype, order, account, cost center, currency and
value). From an frontend i want to code a form to be able to splash
whole sections of the cube's data. I want to select criterias by using
comboboxes (cmd_cmb_splash_yr, cmd_cmb_splash_per etc.). The user
should be able to select on up to seven criterias. Based on this data
selection, he shall be able to manipulate the selected range of data by
using a second set of combo-boxes (cmd_cmb_copy_yr, cmd_cmb_copy_per
etc.). For instance copying all 2007 January data to make it 2008
August data. I realized already the select portion of the coding, which
looks as follows:

Sub DeleteCubedata()

Dim strSql As String
strSql = "DELETE * FROM Rawdata WHERE True"

If IsNull(Me.cmd_splash_cpy) Then
' proceed
Else
strSql = strSql & " AND [COMP]=" & Me.cmd_splash_cpy & "'"
End If
If IsNull(Me.cmd_splash_yr) Then
' proceed
Else
strSql = strSql & " AND [YEAR]=" & Me.cmd_splash_yr & "'"
End If
If IsNull(Me.cmd_splash_per) Then
' proceed
Else
strSql = strSql & " AND [PER]=" & Me.cmd_splash_per & "'"
End If
If IsNull(Me.cmd_splash_typ) Then
' proceed
Else
strSql = strSql & " AND [DATA]=" & Me.cmd_splash_typ & "'"
End If
If IsNull(Me.cmd_splash_cur) Then
' proceed
Else
strSql = strSql & " AND [CUR]=" & Me.cmd_splash_cur & "'"
End If
If IsNull(Me.cmd_splash_acc) Then
' proceed
Else
strSql = strSql & " AND [ACC]=" & Me.cmd_splash_acc
End If
If IsNull(Me.cmd_splash_cc) Then
' proceed
Else
strSql = strSql & " AND [ORD]=" & Me.cmd_splash_typ & "'"
If IsNull(Me.cmd_splash_ord) Then
' proceed
Else
strSql = strSql & " AND [ORD]=" & Me.cmd_splash_ord & "'"
End If
CurrentDb.Execute strSql, dbFailOnError
End Sub

I honestly don't have clue how to realize the functionality i need......
Click to expand...
Click to expand...
 
I'll have to step back, based on your description. I don't have any
experience with Access Cubes.

Perhaps one of the other newsgroup readers can offer some pointers. Have
you tried search at, say, Google.com for some of those keywords?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
What exactly is an "Access Cube?" Never heard of it, and going thru Help (in
Access 2000) only thing I could find was related to the funny multi-colored
cube with the Access logo on it! Access is so huge thta I'm always fascinated
to hear of some new feature. BTW, what version of Access are you using?
 
Hi all,

please don't stick to the cube-word. I am using access 2003.The thing
is that normally the type of structuring data, as shown above is called
cube. There are solutions which are called multidimensional databases,
OLAP - which store cubes, unfortunateley my company don't wan't to
invest in such a OLAP Solution yet (like TM1, Alea, Cognos, Palo,
etc.). That's why decided to set up the 7coordinate data structure - a
cube- (thats a structure like shown above) in Access 2003.
At the end i need a sql string to use in vba in order to copy sections
(by comboboxes in form) out of the rawdata table and "splash" them back
to the table with changed (by comboxes in a form) coordinates. I try to
sketch out the idea above. If this is to hard to understand i could
send a file to the genious.

Thank you so much for your help!
 
Maybe I'm way off on this one, but I'll give it a shot.

I'm going to suggest a cascading subform based on combo boxes approach.

Couldn't you just have a subform that contains all data from your table. You
mention you have seven criterion therefore you should have seven comboboxes.
As you choose your criteria, the subform's recordsource or filter is updated
to reflect the criteria.

You'll then need to cover the update part. So I'd just create a button to use
InsertInto and change the data to 'Plan' and '2007'. Sometimes I like to use
Recordset to control this as it seems to allow more room for customizing
recordsets. So if you want more functionality, maybe under each column (since
they'll never change, just filtered), you could have comboboxes that'd give
options for updating. i.e. under Year the combobox could have 2007, 2008,
2009, 2010, etc. If the year combobox is changed to have a value in it, then
so should your appended data.

Hope this helps somewhat or at least puts you on the right track.
 
Back
Top