[AccessD] Mucking around

Jim,

This is a fact table in a data warehouse.mart.
That is exactly what they are designed for.

I am glad I got some conversation started on
this. I think it has been good for others
to hear how we all do things. And, FYI, I
generally do use a lookup for gender also,
and unknown is one of the choices. It is
also updatable by the end users as are all
my lookups.

I think that using tables for the lookup allows
the end user to "customize" the application to
how they want to use it. And, not necessarily
just use it the way I originally planned.


Robert

At 11:54 PM 9/27/2007, you wrote:
>Date: Thu, 27 Sep 2007 13:33:59 -0500
>From: "Hale, Jim" <Jim.Hale at FleetPride.com>
>Subject: Re: [AccessD] Mucking around
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Message-ID:
>         <FBEA4C39D7934A47A057CDFD3CF070EF4CAED4 at corp-es01.fleetpride.local>
>Content-Type: text/plain;       charset=us-ascii
>
>
>I agree on lookup tables but how about "rollup" tables? Here is an
>example I happen to be working on at the moment.
>Assume a table with general ledger amounts.
>fldAcctNumber fldAcctdescription fldMonth fldYear fldCompany fldDept
>fldAmount.
>
>All the accts for a given month,year and company create the trial
>balance. Now suppose you don't want to see every account in your report
>but would prefer summary accounts, ie "Sales" is comprised of Accounts
>"product sales","labor Sales" ,"Service Sales",etc., "General and
>administrative expense" is comprised of "salaries", "travel and
>entertainment", "rent", etc. You certainly wouldn't just tack a rollup
>field onto the general ledger balance file but would create a file with
>fldAcctNumber fldSummaryAcctNumber fldSummaryAcctdescription. Join this
>table to the GL balance file on fldAcctnumber, create a groupby query
>and voila, you have a summary trial balance.
>This works so well you realize that many other rollups exist, ie you
>might want to create a "tax books" rollup or "cashflow" rollup all using
>the fldAcctNumber fldSummaryAcctNumber fldSummaryAcctdescription
>structure to add together different GL accounts together to produce the
>desired result. You can create separate tables for each rollup structure
>or add a type code to the one rollup table and filter based on the
>desired rollup required for a particular report. Which choice is
>properly normalized? Which is most practical?
>
>Jim Hale