Help designing this database

[azu_daioh]

What would be the best way to design this database with multiple
tables and all tables have a common similarities:

TABLES (divided by type of allegations):
investigation referrals
anonymous tips
staff referrals
pattern claims
eligibility review
document request
false injury allegations


ALL above tables will have these controls:
Client#
First name
Last name

each client# can also have multiple claims (claim#). The tables above
could have the same client# w/same claim#.

I was thinking of creating a separate table calling it CLIENT TABLE
that will hold all the client#, first name, and last name. Client# is
the unique/key.

But not all TABLES (allegations) above will be related to the client.
At the same time, if there are multiple allegations for a client, I
want to be able to do a search and the database will provide me with
all the tables (allegations) where a certain client# is found?

If someone can direct me where I could find a sample of such document
that will help me designing this particular database.

If more info is needed, pls let me know.

Thank you,
Sharon

 

[Jeff Boyce]

Sharon

Consider stepping away from the computer and spending time with paper and
pencil. To get the best use of Access' features and functions, your data
will need to be organized in a way that Access can best use, i.e.,
well-normalized relational tables.

Having multiple tables with the same fields in each is what you'd do if you
were using a spreadsheet ... but not a relational database.

Repeating ClientFirstName and ClientLastName in multiple fields, and/or
multiple tables is what you'd do if you were using a spreadsheet ... but not
.... (you know!).

With your paper and pencil, jot down each of the "things" about which you
want to keep information -- for example, based on what you described, I'll
guess that you want to keep information about persons, about allegations,
and about claims (although I'm not clear if those last two are synonyms).

Check Access HELP on "normalization", and check Jeff Conrad's site:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[azu_daioh]

Hi Jeff,

Thanks for the recommendation. I already started with the pen and
paper approach. It's a little slow but I think this will be the best
approach to this particular database. This is the first time I'm
creating something this big and I didn't want it to take a lot of
space in the future.

I'm also getting re-acquinted with 'normalization.'

Thank a lot,

Sharon

 

[Jeff Boyce]

Sharon

Given the (extremely low) cost of hard drive space these days (?!500 Gb for
<$200?!), you may not need to worry about the space the db takes. But if
you understand "normalization", you understand that getting the table
structure right from the start can make a big difference.

Feel free to post back your questions as you work through this ...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP