Planning/creating a database

[Stuart Marley]

I am a mortgage advisor, i'm trying to create a database to hold each clients
detials in a seperate record. The problem i have is that i require approx 700
fields of information and i am restricted to 255 per table therefore i split
the date into seperate tables which i hope to link together. This will
hopefully enenable me to view all off the details for each single client on
the same screen with the most common detials on the left handside of the
screen and the rest of the info in sub forms on the right handside of the
screen which i will be able to flick through by clicking the tabs for each
sub form.

The problem i am currently encountering is that i cannot relate all of the
tables together and therefore cannot create any records from the form in the
form view.

I hope to be able to print form letters, faxes and documents required for
the compliance side of the mortgage deal. I would also like to have the
facility to send template/form emails to clients.

As you have probably guessed i am new to Access and i am not at all sure of
its capabilities or functions but would really appreciate some feed back on
this.

 

[Duane Hookom]

I think you need to read some information on normalization and database
design. I have worked with mortgage solutions in the past and understand
that every "_____" on a mortgage application might be considered a field in
your table. This is not the way to create a relational database.

Jeff Conrad has some usefull links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

If you really think you need that many fields, come back with some
information about the fields.

 

[Rick B]

If you have more than 20 or 30 fields in a table, then you almost definitely
have a design flaw.

What type of data are you storing? It needs to be broken down. Without
knowing the details, you would have something like...

TblPerson
PersonNumber
PersonLastName
PersonFirstName
PersonMiddleName
PersonSocial
etc.

TblLoanOfficers
OfficerNumber
OfficerFirstName
OfficerLastName
etc.

TblProperty
PropNumber
PropStreetNumber
PropStreetName
PropCity
etc.

TblLendors
LendorNumber
LendorName
LendorAddress
LEndorCity
LendorState
etc.

TblApplication
ApplicationNumber
MainApplicant (this will be a "person number" from above)
CoApplicant (this will be a "person number" from above)
Application Date
LoanOfficer (this will be an "officer number" from above)
PropertyCode (this will be a "property number" from above)
LendorNumber


In short, Acces is a "relational" database. If you are trying to create a
"flat file" or a spreadsheet, then you should be using Excel. In a
relational database, data should be broken down into small, meaningful
chunks and stored in separate tables. The data in the various tables should
then be related to each other. If you post back a lot of detail, we might
be able to help you figure out a table structure that would work.

 

[roccogrand]

Stuart,

I have a little experience with HUD forms, so let me make a suggestion.

Take each of the main headings in your yours and copy them into separate
Word documents or better yet Excel files. Normally, the main headings are
close to being equivalent to individual tables in Access. If you use Excel
you can import the files into Access. Use some bogus data so that you can
create forms and queries that make sense.

Then think about the key fields that would relate each table to others in
your database. If a table has more than 20 to 30 fields then try splitting
it in the same manner.

This may not be the school solution to database design, but for structured
forms and applications such HUD-1, I think that it will work well enough for
you to get started designing a pseudo-normalized database.

As Duane suggested, a good DB design book will help you tremendously.
Stephens and Plew's "Database Design" is one that pull from my bookshelf on
occasion. The alternative is to hire a pro for the design while you work
on the functionality.

If you create a pseudo-normalized database in the manner described above,
don't be afraid to trash a table, form, or query that doesn't work.

Good luck.

LDN