Normalized Database

[Guest]

I have read in many postings about normalized databases. My question is this
- I have a table that currently has a lot of fields. I have to account for
different account numbers that are sent out from our department. It could go
from one account number to up to 40 account numbers. I set this up in one
table which created a lot of fields plus the other fields that are in that
table. What would be a different way of setting this up without losing the
information that I need?

 

[Bill Mosca, MS Access MVP]

It sounds like you are creating a field for every account instead of a
record for each account. What is the structure you are using?

 

[Guest]

What do you mean by structure? I'm new at all this and when I set up the
database I didn't have much skill behind it and now that it is set up, I want
to make sure that I don't make some of the same mistakes in the future.

One reason why I think I set it up this way was because I wanted to create a
reroute form for our department. The reps then put in all the account
numbers that they are rerouting to different departments(each deparment would
get their own reroute report) and then a report is generated with all the
information contained on the report

 

[Guest]

I do have a field for each account number and the records is based on the
person that the reroute is being sent to

 

[Bill Mosca, MS Access MVP]

Table structure means the table name, each field and each field's data type.

 

[Bill Mosca, MS Access MVP]

A field for each account goes against first normal form which is the table
has no repeating groups.

If you can post a list of the fields in your table, we can help you
normalize it. Leaving it as you have it will only make dealing with the data
very difficult.

 

[Guest]

Table Name - tblRR

Fields Datatype
ID int
Date datetime
RerouteContact nvarchar
Address nvarchar
MCSNumber nvarchar
Acct1 nvarchar
Acct2 nvarchar
(The Acct# goes up to 40 with the same datatype)
DateRecdBack nvarchar
FirstAttpt nvarchar
SecondAttpt nvarchar
Thirdattpt nvarchar
CountofPieces int
Daysoutofcomp nvarchar

 

[Guest]

Table Name - tblRR

Fields Datatype
ID int
Date datetime
RerouteContact nvarchar
Address nvarchar
MCSNumber nvarchar
Acct1 nvarchar
Acct2 nvarchar
(The Acct# goes up to 40 with the same datatype)
DateRecdBack nvarchar
FirstAttpt nvarchar
SecondAttpt nvarchar
Thirdattpt nvarchar
CountofPieces int
Daysoutofcomp nvarchar

 

[Guest]

Just a glance at your field list tells me that a reroute can have one or more
accounts, and one or more "attempts".

This indicates a Reroutes table, an Accounts table (with a foreign key
linking it to Reroutes), and an Attempts table (with a foreign key linking it
to Reroutes).

But that's assuming accounts and attempts are independent of each other, ie.
they can vary by number in any combination with each other.

 

[Guest]

They both vary by number and dates - If I made 3 seperate tables to hold all
the information - what would I base the form off of? I am assuming a query
but after that assumption I am lost

 

[Guest]

The form would be based on all three tables. A main form linked to Reroutes,
and two subforms on that mainform, each linked to Accounts and Attempts,
respectively.

 

[Guest]

So the subforms would be other forms that opened when they press a button to
enter the account numbes?

 

[Joseph Meehan]

Table Name - tblRR

Fields Datatype
ID int
Date datetime
RerouteContact nvarchar
Address nvarchar
MCSNumber nvarchar
Acct1 nvarchar
Acct2 nvarchar
(The Acct# goes up to 40 with the same datatype)
DateRecdBack nvarchar
FirstAttpt nvarchar
SecondAttpt nvarchar
Thirdattpt nvarchar
CountofPieces int
Daysoutofcomp nvarchar

Table1
Fields Datatype
ID int
Date datetime
RerouteContact nvarchar
Address nvarchar
MCSNumber nvarchar
DateRecdBack nvarchar
FirstAttpt nvarchar
SecondAttpt nvarchar
Thirdattpt nvarchar
CountofPieces int
Daysoutofcomp nvarchar

Table2
Acct nvarchar
ID(same as Table1)

Link the tables using the ID field

 

[Guest]

More simply, they are a "window within a window" on the mainform. You can
click or tab into them and begin entering instances of Accounts and/or
Attempts. In datasheet view they look like a mini-spreadsheet embedded in
the mainform.

Do a google on this concept for examples/tutorials. Also have a look at the
sample Northwind database that comes with Access, make a throwaway copy, and
begin playing with it.

 

[Guest]

I will definitely do that. Thank you for your help.

 

[Guest]

I set up my main form and my subform and it looks like everything is working
correctly - I have another question - Would there be anyway in the future
that the ID numbers wouldn't match? When I first did it I couldn't get it to
work because the ID numbers were not the same - but I fixed that - I just
want to make sure that no one can acidentally do something in the subform
that would cause them to go to another record. I did remove everything that
I know of to try to keep that from happening

 

[Ron2006]

If information is always entered via a parent-child linked form like
you just created, then Access will always keep them in synch.

If you do any other type of query/batch/multiple input from other
sources than it becomes your responsibility to maintain the parent
child relationship (put the correct ID number in). This is not
difficult as long as you work from top down - insert parent first and
then insert the children.

Ron