Advice needed on DB set-up & Normalization for Novice

[AccessWomble]

Hi Ladies & Gents,

I am an experienced in Excel & VBA but new to Access. Want to set up a new DB but need some advice on how to set up my tables and relationships properly.

The DB will store details of complex transactions, and (for example) the dates when actions relating to the transactions are complete, which will drive a monitoring system eventually. There will be a low number of transactions.

In addition to about 10 fields of general transaction info, each transaction needs the following fields:

* Each transaction has 9 'sections'.
* Each 'section' has its own status & start/due/completed dates, and between 2 and 9 'details'
* Each 'detail' has a Status/Responsibility/Comments/Date fields.

So, if I construct one massive table with one field for each date of each detail on each section etc, I'm looking at well over 200 fields, which sets off 200 alarm bells in my head! Can that be right?!

How would you pros approach this?
I think what i need is to end up with a long thin table where each transaction appears multiple times, for each different date etc, but not sure how to go about it..

Thanks very much!

See Excel sheet 2 attached for grid detailing an outline of the fields for each transaction.

 

[dbDamo]

You most certainly do not want one table with 200 fields!

As a starting point I would suggest you read up on normalisation.

 

[jdraw]

Hi Ladies & Gents,

I am an experienced in Excel & VBA but new to Access. Want to set up a new DB but need some advice on how to set up my tables and relationships properly.

The DB will store details of complex transactions, and (for example) the dates when actions relating to the transactions are complete, which will drive a monitoring system eventually. There will be a low number of transactions.

In addition to about 10 fields of general transaction info, each transaction needs the following fields:

* Each transaction has 9 'sections'.
* Each 'section' has its own status & start/due/completed dates, and between 2 and 9 'details'
* Each 'detail' has a Status/Responsibility/Comments/Date fields.

So, if I construct one massive table with one field for each date of each detail on each section etc, I'm looking at well over 200 fields, which sets off 200 alarm bells in my head! Can that be right?!

How would you pros approach this?
I think what i need is to end up with a long thin table where each transaction appears multiple times, for each different date etc, but not sure how to go about it..

Thanks very much!

See Excel sheet 2 attached for grid detailing an outline of the fields for each transaction.

A table with 200 fields --No Way!
There are some videos related to normalization and planning a database at this site. http://www.allenbrowne.com/casu-22.html

 

[speakers_86]

I feel like I need more information about your business structure, basically how your business operates and what is being transacted, and what each section is. Also, why are there so many sections and so much detail. It looks like information overload. It will be hard to organize all of that in a useful manner. Make sure all of the information is relevant and not redundant. With what information you provided, I would say you would want the tables like so...

tblTransactions
TransactionID
OtherInfo

tblSection1
Section1ID
TransactionID
OtherSection1Info

tblSection2
Section2ID
TransactionID
OtherSection2Info

etc.