By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,034 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Pivot-Table Alternatives?

P: n/a
I've got something called "Reference Rates".

The idea is that on a given day, we have various rates of return for
various entities.
e.g. Libor 3-month return, Libor 6-month return, US Treasury Bonds, the
Prime rate, and so-forth.

We associate a security with one of those rates.

There are a set of rates for each calendar day, and the rates for that
day have either been reviewed/approved by somebody or they have not.

Seems like the technical solution is:
----------------------------------------------------------------------------
tblReferenceRateDate (Date, IsApproved)
tblReferenceRate (Date, RateType, RateValue)
----------------------------------------------------------------------------

But personally I could live with just one table:
tblReferenceRate (Date, RateType, RateValue, IsApproved)

That would also allow the design to accomodate a user request for
individual rate approvals instead of approving a whole day at a time.

But it seems like the UI is going to be complicated if it's done in a
user-friendly fashion.
To wit: we'd have to pull the individual day/rate records into a pivot
table presentation that has one row for each date and a column for each
rate type.

Problem I have with this is that now we're going to need programming
changes if/when the users add a new rate. i.e. the code that creats
the pivot table will need changing and the work table/screen that
presents same will need changes.

Anybody been here? Is there a way out that doesn't need code changes
as rate types come and go - yet presents the info to the user in an
intuitive fashion (specifically, one row per date...a column for each
rate)?

Aug 18 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
There shouldn't be anything wrong with using a Pivot table. I believe
your issue is normalization. You system is probably not normalized
enough (the term normalization is quite misleading). There are various
levels of normalization, 1st, 2nd, 3rd, 4th, and 5th normal forms. It
gets complex. But the concept of normalization is to create an
application design that will suit the most situations - have the most
flexibility without require design changes to accommodate new
situtations. Normalization mainly deals with redundant data columns,
but the higher normal forms get more sophisticated and deal with Table
keys and column dependencies. You can google for normalization topics
like 1st normal form, 2nd normal form...5th normal form...(I think they
have a 6th normal form too).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Aug 18 '06 #2

P: n/a
Pete,

I think a Pivot-table is ideal to avoid recoding and it is highly
flexible to present important data as cross-tables or graphical charts.
The pivot-table creates dynamic columns from 1 or a few dimensions from
one or
more columns with a limited number of classifying values.
The most important part is designing a table-format to feed the
pivot-table.
In most cases just one column delivers the values to show and hat
column is
dragged into the value area to be aggregated vertically and
horizontally
according to the available values in the descibing categories in all
other
columns that can be freely moved to analyze all releations between
different
dimensions of the model.
The category-columns are mostly text-format or a numerical or
date-format that
can be used to order the data effectively. Often the non-text values
must be
ordered correctly by onverting them in a formatted text-representation
that is
readable for humans and sorts correctly.
In your example it is not clear how the ratetype is put on the
pivot-table and
what you want to put into the value-area. I suppose only a count of
rate-approvals because summing rate-values is not meaningful. If the
amount of
bond-values involved would be available in a value-column that would be
useful to
aggregate using the sum-function. In my perception the rate-value (or
some rounded ratevalue-class) would be useful to show vertically or
horizontally in the pivot-table. If the counting is done in a special
column in the base-table for the pivot-form presentation by using the
sigma-button and creating a count of ID's for instance this
count-column can be summed in the pivot-table and the table to process
is then more efficient.
The pivot-table area-field can be used for the count-column or for the
bond-value column to analyse the data from a different perspective.
In any case you will never have to make provisions for changing
rate-types because they will automatically apear in the pivot-table
after each refresh of the data.
The key-factor is the optimal design of a query to be used in the
pivot-table. That query can be a query with only totals and relevant
category-labels designed in a format useful for the pivot-table.
Eventually the query can be made a parameter-query to be able to
extract only relevant data into the model. (In most cases only a
date-interval, sometimes other filter-criteria to reduce the amount of
extracted data from the database)
Pivot-tables are generated in Excel, but when using Access as interface
for the end-user makes it possible to automate the basetable-generation
in a more controlled fashion with a lot less fuzz to track and update
the source-data then when starting from Excel end linking data or
importing tables to analyse using the pivot-tables. Used from an
Access-form it gives a lot of control over the data-selection process.
The table (query-) design with useful classes and categories to present
the data determines the ease and information-value of use for the
end-user.
When the input-table contains category- en subcategory names in
seperate columns these columns can be used to drill down from the
global categories into de detailed subcategories to view an analyze the
data. This method also is useful to order and present the categories
systematically in stead of just alphabetcally. This systematic order
also can be acieved by concatenting the categories and subcategories
into one column, but then you cannot drill-down into te subcategories
anymore or use a few global categories to order the columns in to
groups or filtering them as a group by selecting them in the page-area.

When I read your problem description I just wondered if you didn't mix
up the pivot-table and the cross-table concept. For the crosstable
concept using in queries or reports you need some stability in the
column-values used because they are fixed when used in a printable
report.

Marc
PeteCresswell schreef:
I've got something called "Reference Rates".

The idea is that on a given day, we have various rates of return for
various entities.
e.g. Libor 3-month return, Libor 6-month return, US Treasury Bonds, the
Prime rate, and so-forth.

We associate a security with one of those rates.

There are a set of rates for each calendar day, and the rates for that
day have either been reviewed/approved by somebody or they have not.

Seems like the technical solution is:
----------------------------------------------------------------------------
tblReferenceRateDate (Date, IsApproved)
tblReferenceRate (Date, RateType, RateValue)
----------------------------------------------------------------------------

But personally I could live with just one table:
tblReferenceRate (Date, RateType, RateValue, IsApproved)

That would also allow the design to accomodate a user request for
individual rate approvals instead of approving a whole day at a time.

But it seems like the UI is going to be complicated if it's done in a
user-friendly fashion.
To wit: we'd have to pull the individual day/rate records into a pivot
table presentation that has one row for each date and a column for each
rate type.

Problem I have with this is that now we're going to need programming
changes if/when the users add a new rate. i.e. the code that creats
the pivot table will need changing and the work table/screen that
presents same will need changes.

Anybody been here? Is there a way out that doesn't need code changes
as rate types come and go - yet presents the info to the user in an
intuitive fashion (specifically, one row per date...a column for each
rate)?
Aug 18 '06 #3

P: n/a
Per MarcHG:
>When I read your problem description I just wondered if you didn't mix
up the pivot-table and the cross-table concept. For the crosstable
concept using in queries or reports you need some stability in the
column-values used because they are fixed when used in a printable
report.
I probably did - not knowing the diff between cross-table and pivot-table.
I used "pivot-table" just as something that flips the data on it's side.

e.g. Transforming something like this storage format:
-------------------------
01/01/2006 Libor 3.45
01/01/2006 Prime 3.02
01/01/2005 Treasury 2.89
01/02/2006 Libor 3.44
01/02/2006 Prime 3.01
01/02/2005 Treasury 2.89
01/03/2006 Libor 3.46
01/03/2006 Prime 3.03
01/03/2005 Treasury 2.90
01/04/2006 Libor 3.45
01/04/2006 Prime 3.02
01/04/2005 Treasury 2.89
-------------------------

Into this user format:
----------------------------------
Libor Prime Treasury
01/01/2006 3.45 3.02 2.89
01/02/2006 3.44 3.01 2.89
01/03/2006 3.46 3.03 2.90
01/04/2006 3.45 3.02 2.89
---------------------------------

Can the solutions you describe present updatable information
or would they be read-only?

Last time I did this, I just wrote a query to flip the data
into a work table. Then I let the user update the work table
via a Form. But then I had to write code to flip it back
from the work table to the storage format.

It wasn't a real big deal, but the way I wound up doing it,
the code would have had to be changed if/when a new
rate type (e.g. "Euribor") were added.
--
PeteCresswell
Aug 19 '06 #4

P: n/a
On Fri, 18 Aug 2006 20:09:09 -0400, "(PeteCresswell)" <x@y.Invalidwrote:
>Per MarcHG:
>>When I read your problem description I just wondered if you didn't mix
up the pivot-table and the cross-table concept. For the crosstable
concept using in queries or reports you need some stability in the
column-values used because they are fixed when used in a printable
report.

I probably did - not knowing the diff between cross-table and pivot-table.
I used "pivot-table" just as something that flips the data on it's side.

e.g. Transforming something like this storage format:
-------------------------
01/01/2006 Libor 3.45
01/01/2006 Prime 3.02
01/01/2005 Treasury 2.89
01/02/2006 Libor 3.44
01/02/2006 Prime 3.01
01/02/2005 Treasury 2.89
01/03/2006 Libor 3.46
01/03/2006 Prime 3.03
01/03/2005 Treasury 2.90
01/04/2006 Libor 3.45
01/04/2006 Prime 3.02
01/04/2005 Treasury 2.89
-------------------------

Into this user format:
----------------------------------
Libor Prime Treasury
01/01/2006 3.45 3.02 2.89
01/02/2006 3.44 3.01 2.89
01/03/2006 3.46 3.03 2.90
01/04/2006 3.45 3.02 2.89
---------------------------------

Can the solutions you describe present updatable information
or would they be read-only?

Last time I did this, I just wrote a query to flip the data
into a work table. Then I let the user update the work table
via a Form. But then I had to write code to flip it back
from the work table to the storage format.

It wasn't a real big deal, but the way I wound up doing it,
the code would have had to be changed if/when a new
rate type (e.g. "Euribor") were added.
To make these things editable you need to use a temporary table.
I have posted an small example in A97 to
http://rapidshare.de/files/30030032/...ample.zip.html
This was created by a sort of wizard I wrote a long time ago!
Aug 19 '06 #5

P: n/a
Per polite person:
>To make these things editable you need to use a temporary table.
I have posted an small example in A97 to
http://rapidshare.de/files/30030032/...ample.zip.html
This was created by a sort of wizard I wrote a long time ago!
Can the work table be flipped back to the storage format in a way that does not
require hard-coding for different values?
--
PeteCresswell
Aug 20 '06 #6

P: n/a
On Sat, 19 Aug 2006 20:40:10 -0400, "(PeteCresswell)" <x@y.Invalidwrote:
>Per polite person:
>>To make these things editable you need to use a temporary table.
I have posted an small example in A97 to
http://rapidshare.de/files/30030032/...ample.zip.html
This was created by a sort of wizard I wrote a long time ago!

Can the work table be flipped back to the storage format in a way that does not
require hard-coding for different values?
Yes, there is no no coding which depends on the actual values in the table.
A standalone example of code:

Function CrossToLinear(CrossTableName, NumRowFields, ColumnFieldName, _
ValueFieldName) As Boolean ' result is success/failure indicator

' Use this for reconverting from crosstab format table.
' Assumes that (row fields + column field) are unique in the output
table, ie
' the original crosstab had only one record contributing to each
value.
' Assumes that the input table (crosstab table) is in the form:
' all the row heading fields first (NumRowfields of them)
' then the columns.
' Creates an output table in the format:
' all the row heading fields
' then the column heading field
' then the value field
' Example
'CrossTable has fields and values
'Person, Project, Jan, Feb, Mar, Apr ...
'Fred, Holidays, 3,0,0,5, ...
'result = CrossToLinear("CrossTable",2,"Month","Days")
'Output table has fields and values
'Person, Project, Month, Days
'Fred, Holidays, Jan, 3
'Fred, Holidays, Apr, 5
' ...
Dim CurrentDatabase As DATABASE
Dim LinearTableName$
Dim LinearTableDef As TableDef ' for output table
Dim LinearTableSet As Recordset
Dim CrossTableDef As TableDef ' for input table
Dim CrossTableSet As Recordset
Dim myfield As Field, myfield2 As Field
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim record_started As Boolean
On Error GoTo myerror
Set CurrentDatabase = DBEngine(0)(0)
LinearTableName$ = CrossTableName & "_Lin"
'Create new TableDef object. Delete first if already there
On Error Resume Next
CurrentDatabase.TableDefs.Delete LinearTableName
On Error GoTo myerror
Set LinearTableDef = CurrentDatabase.CreateTableDef(LinearTableName)
'Open input table def to get details of row and value field details:
Set CrossTableDef = CurrentDatabase.TableDefs(CrossTableName)
For i = 0 To NumRowFields - 1
' Create output Field object:
Set myfield2 = CrossTableDef.Fields(i) 'temp
Set myfield = LinearTableDef.CreateField(myfield2.Name, _
myfield2.Type, myfield2.Size)
LinearTableDef.Fields.Append myfield
Next i
'now add column heads field
Set myfield = LinearTableDef.CreateField(ColumnFieldName, dbText, 50)
' 50 for example
LinearTableDef.Fields.Append myfield
'now add value field
Set myfield2 = CrossTableDef.Fields(NumRowFields) 'temp
Set myfield = LinearTableDef.CreateField(ValueFieldName,
myfield2.Type, myfield2.Size)
LinearTableDef.Fields.Append myfield
CurrentDatabase.TableDefs.Append LinearTableDef
' Open output table
Set LinearTableSet = CurrentDatabase.OpenRecordset(LinearTableName, _
DB_OPEN_DYNASET)
' Open input table
Set CrossTableSet = CurrentDatabase.OpenRecordset(CrossTableName, _
DB_OPEN_DYNASET, DB_FORWARDONLY)
'if there are any records
If Not (CrossTableSet.BOF And CrossTableSet.EOF) Then
record_started = False
Do Until CrossTableSet.EOF 'for each record in crosstableset
For j = NumRowFields To CrossTableSet.Fields.Count - 1
'(for each crosstab column field)
Set myfield = CrossTableSet.Fields(j)
If IsNull(myfield.Value) Then
'ignore null entries
Else
'Add data to linear table
If Not record_started Then
' Prepare new record.
LinearTableSet.AddNew
record_started = True
For k = 0 To NumRowFields - 1
'copy all the row fields
LinearTableSet.Fields(k) = _
CrossTableSet.Fields(k)
Next k
End If
'now set the column field value
LinearTableSet.Fields(NumRowFields) = myfield.Name
'now set the value field value
LinearTableSet.Fields(ValueFieldName) = myfield.Value
End If
' Save record.
If record_started Then
LinearTableSet.UPDATE
record_started = False
End If
Next j
CrossTableSet.MoveNext
Loop
End If
LinearTableSet.Close
CrossTableSet.Close
CrossToLinear = True
Exit Function
myerror:
MsgBox "Error in CrossToLinear, number " & Err.Number & ": " &
Err.Description
CrossToLinear = False
'may leave things open!
Exit Function
End Function

---

The example on Rapidshare includes use of a form with dynamic headings, which can be used
with different tables. It can also deal with crosstabs with more than one value field. The only
dependence on the table is through the calling sequence on the menu form.
Aug 20 '06 #7

P: n/a
Per polite person:
>The example on Rapidshare
I tried for awhile, but couldn't get a download to work from RapidShare.
Do they want money for it?
--
PeteCresswell
Aug 21 '06 #8

P: n/a
On Sun, 20 Aug 2006 20:40:23 -0400, "(PeteCresswell)" <x@y.Invalidwrote:
>Per polite person:
>>The example on Rapidshare

I tried for awhile, but couldn't get a download to work from RapidShare.
Do they want money for it?
try
http://s10.quicksharing.com/v/416788...ample.zip.html

Rapidshare is free but some people have trouble with it
Aug 21 '06 #9

P: n/a
On Mon, 21 Aug 2006 08:16:23 GMT, polite person <sn**@snippers.comwrote:
>On Sun, 20 Aug 2006 20:40:23 -0400, "(PeteCresswell)" <x@y.Invalidwrote:
>>Per polite person:
>>>The example on Rapidshare

I tried for awhile, but couldn't get a download to work from RapidShare.
Do they want money for it?

try
http://s10.quicksharing.com/v/416788...ample.zip.html

Rapidshare is free but some people have trouble with it
PS this is only an example but a few points about using a temp table and writing back the results:
This is usually only suitable if either the db is essentially only used by one user at once - eg to
prepare data offline -, or
if the input query selects only a subset of records which are unlikely to be in use by another user
at the same time (eg in a timesheet application).
Otherwise too many conflicts may occur, though of course you can wrap things in transactions.
Aug 21 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.