Access 2002 Advice

[saschamps9903]

Hello,

I am in the middle of designing a database for my department and I need some
guidance as to how to proceed. I am desiging a form that will be broken down
into 13 seperate sections for users to complete, all sections relate to one
"account number". What I did was create 13 different tables using the same
primary key in each table (ex: account number as PK in each table). Then I
tied together each table as a query and started to create the from using the
one query as the basis for the form. When I type in the "account number" on
the form , all of the tables are updated with the information that
corresponds to each section. my question is, will this type of design turn
my database into one complete mess? or am I on the right track with how I
have designed it to this point? I would like to draw reports off of the
entered information and I feel that this design method may screw everything
up. I am using windows 2K with office xp

 

[Jeff Boyce]

Aside from your desire to use 13 separate sections on the form, what
rationale have you used to determine that this must be 13 separate tables,
plus a main table? I ask because the standard approach to designing a
relational database (Access, SQL Server, Oracle, ...) is to start with the
entities (tables) and relationships first, then design forms that make it
easy for users.

Without more information about how your data is structured, it could be
tough to offer (cogent) suggestions...

 

[saschamps9903]

the form that I am trying to create is a file analysis form. There are 13
key sections that the analyst will have to fill out information, or their
findings under each of the sections. My line of thinking was to not have the
analyst type in the account number, name of account holder, address, etc.
over and over on each seperate section, but to combine all sections together
and have them type the account information once and only once. the query
ties it all together, and for the most part it has worked. My concern is that
as the tables get larger, the database will probably get bogged down and get
slower and slower.

 

[Jeff Boyce]

Please reconsider my first response. To get the most out of Access, you
need to design and build your system with relationships and normalization in
mind. Your rationale seems to say "I have 13 key sections, therefore, I
need 13 tables."

And one more time ... first design the table structure, then the forms to
help the users. If you don't want them to fill out common info before
filling out information that is somehow related (?a "section", whatever you
mean by that), you can accomplish that in any number of ways in Access.

Where's the data?! (think little old lady asking "where's the beef?!")

 

[saschamps9903]

My only answer to the rationale is that I am trying to keep my boss happy.
He has developed the form that the analysts will use but has now decided that
he wants to track all of the information on a database. The form is broken
down into 13 sections for the analysts to review, section A covers one aspect
of the file, section b another, and so on. Each section will not relate to
the other except for loan number. My line of thinking was to create each of
the tables and run a query that would be used for the form they would
eventually use. I have not built anything like this before and I hope that
your expertise will show me a better way to build this database. thank you
for your time.

 

[Tim Ferguson]

My only answer to the rationale is that I am trying to keep my boss
happy. He has developed the form that the analysts will use but has
now decided that he wants to track all of the information on a
database.

In that case, you have two initial jobs to do: first you have to learn
about relational design and the principles of sound databases; then you
have to teach them to your boss. When you've done all that, you can start
doing the systems analysis and requirements analysis, risk log, etc...

Sometimes it's the job #2 that is the hardest!

All the best


Tim F