473,574 Members | 2,310 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Pivot-Table Alternatives?

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:
----------------------------------------------------------------------------
tblReferenceRat eDate (Date, IsApproved)
tblReferenceRat e (Date, RateType, RateValue)
----------------------------------------------------------------------------

But personally I could live with just one table:
tblReferenceRat e (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
9 6779
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
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:
----------------------------------------------------------------------------
tblReferenceRat eDate (Date, IsApproved)
tblReferenceRat e (Date, RateType, RateValue)
----------------------------------------------------------------------------

But personally I could live with just one table:
tblReferenceRat e (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
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
On Fri, 18 Aug 2006 20:09:09 -0400, "(PeteCresswell )" <x@y.Invalidwro te:
>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
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
On Sat, 19 Aug 2006 20:40:10 -0400, "(PeteCresswell )" <x@y.Invalidwro te:
>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(C rossTableName, 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.Dele te LinearTableName
On Error GoTo myerror
Set LinearTableDef = CurrentDatabase .CreateTableDef (LinearTableNam e)
'Open input table def to get details of row and value field details:
Set CrossTableDef = CurrentDatabase .TableDefs(Cros sTableName)
For i = 0 To NumRowFields - 1
' Create output Field object:
Set myfield2 = CrossTableDef.F ields(i) 'temp
Set myfield = LinearTableDef. CreateField(myf ield2.Name, _
myfield2.Type, myfield2.Size)
LinearTableDef. Fields.Append myfield
Next i
'now add column heads field
Set myfield = LinearTableDef. CreateField(Col umnFieldName, dbText, 50)
' 50 for example
LinearTableDef. Fields.Append myfield
'now add value field
Set myfield2 = CrossTableDef.F ields(NumRowFie lds) 'temp
Set myfield = LinearTableDef. CreateField(Val ueFieldName,
myfield2.Type, myfield2.Size)
LinearTableDef. Fields.Append myfield
CurrentDatabase .TableDefs.Appe nd 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.E OF) Then
record_started = False
Do Until CrossTableSet.E OF 'for each record in crosstableset
For j = NumRowFields To CrossTableSet.F ields.Count - 1
'(for each crosstab column field)
Set myfield = CrossTableSet.F ields(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.F ields(k)
Next k
End If
'now set the column field value
LinearTableSet. Fields(NumRowFi elds) = myfield.Name
'now set the value field value
LinearTableSet. Fields(ValueFie ldName) = myfield.Value
End If
' Save record.
If record_started Then
LinearTableSet. UPDATE
record_started = False
End If
Next j
CrossTableSet.M oveNext
Loop
End If
LinearTableSet. Close
CrossTableSet.C lose
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
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
On Sun, 20 Aug 2006 20:40:23 -0400, "(PeteCresswell )" <x@y.Invalidwro te:
>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
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.Invalidwro te:
>>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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4119
by: Johnny Meredith | last post by:
Dear All, I have an Access database that tracks the progress of income tax audits. When the taxing authorities make a change (an "Adjustment"), I record the pertinent information in the database. At the end of the audit cycle, these adjustments are used to compute the revised taxes due. This computation is done in a fairly complex, but...
2
3100
by: Carl Gilbert | last post by:
Hi I have a math kinda problem where I'm trying to split some lines when two or more lines connect two shapes. The reason I am doing this is to make it clear that there are multiple lines connecting the two shapes. http://www.blackwaterbadgers.co.uk/image1.bmp Image 1 shows how if you have a line between two shapes, the line is...
3
3578
by: GoalieGW | last post by:
Does anyone know how to create a Pivot Chart at run-time? I have a database of meter readings that currently requires me to have 200 forms created so the user can enter date criteria and have a chart pop up. I would like to remove the forms and have them created at run-time. Any ideas?
0
1939
by: Zlatko Matiæ | last post by:
I have experienced some problems with total operations (sum, min, max, avg etc) in pivot tables nad pivot charts in .mde. In .mdb I can activate any totals operation. on both notebook and desktop PC. When I create .mde on the notebook, I can use totals on laptop, but not on the desktop PC. I just can't switch on any total. There is no total...
2
2296
by: PC Datasheet | last post by:
From: "Martin" <Martin@discussions.microsoft.com> Subject: Pivot Chart Date: Monday, February 13, 2006 3:59 PM I am trying to learn how to use pivot charts. In 1995, if John took vacation 10/15 to 10/20, 11/9/ to 11/19 and 12/15 to 12/31, how would you set up the query and the pivot chart? Thank you!
1
2875
by: keliie | last post by:
Hello, I've created a form that contains a single unbound combo box and two subforms. The two subforms display the results of a query (driven by the combo box selection) in both tabular and pivot chart formats. The problem that I'm having is that the Pivot Chart that I've created rounds all of the values down to the nearest integer (e.g.,...
1
3258
by: devagupt | last post by:
i have a form called get downtime data. WHen information is entered into it and the button "get downtime data" pressed , it displays the results of a query. WHen i view the query in pivot chart view , it only charts values which are over 1.Also , it displays only whole numbers and not decimal values like 2.3 etc.. I played with the scale tab in...
3
6024
by: Nassa | last post by:
Hi everyone, I have 3 tables: Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), PK:id TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,F­K:telNumbers.telbookid=telbook.id Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk
1
6796
by: thomas.wordsworth | last post by:
This is a bit of a two-part question. 1. Is there a way to control what filters are placed on a pivot chart via vba. I can easily change the underlying query and refresh pivot chart but it's a big dataset and takes some time to load - my users are impatient! 2. If this is not possible to code filters on the chart are the any
0
1068
by: niveditharao | last post by:
i want to know how can i use two pivot columns in a single query. or ---- pivot <pivotcolumn1> for < > in.... pivot <pivotcolumn2> for < > in....
0
7832
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7747
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8093
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7843
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6498
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5643
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5331
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2262
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1094
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.