[AccessD] Mucking around

You're talking about creating a star schema, which is commonly used for
this purpose in data warehouses.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Thursday, September 27, 2007 11:34 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Mucking around


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

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, September 26, 2007 4:52 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Mucking around

Hi John

Thanks. 
He is my man, I've always handled "lookup" tables as any other table.

/gustav

>>> jwcolby at colbyconsulting.com 26-09-2007 11:25 >>>
I found an interesting article on SQL Server central.  The subject has
been discussed in these hallowed halls so I thought I'd throw this out.

http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/146
4/ 

John W. Colby
Colby Consulting
www.ColbyConsulting.com 



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or other
use of or taking action in reliance upon this information by persons or
entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email, you
are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com