Problem designing tables: Too many fields

[erick-flores]

Hello all

I am creating a table with a lottt of fields. And its giving me the too
many fields. I guess I need to design my tables in a better way
(normalization) but I dont know where to start. The table have the
fields that will be use for an Expense Report. Here is an outline of
the Expense Report form:

Name: Location:
Period ending:
Date Total Exp. BDR Comments Lodging Meals CMM Auto
AutoOPN Amount
date1 TE1 BDR1 comm1 lod1 mea1 cmm1 au1
auto1 amo1
date2 TE2 BDR2 comm2 lod2 mea2 cmm2 au2
auto2 amo2
date3 TE3 BDR3 comm3 lod3 mea3 cmm3 au3
auto3 amo3
date4 TE4 BDR4 comm4 lod4 mea4 cmm4 au4
auto4 amo4
date5 TE5 BDR5 comm5 lod5 mea5 cmm5 au5
auto5 amo5
date6 TE6 BDR6 comm6 lod6 mea6 cmm6 au6
auto6 amo6
date7 TE7 BDR7 comm7 lod7 mea7 cmm7 au7
auto7 amo7
date8 TE8 BDR8 comm8 lod8 mea8 cmm8 au8
auto8 amo8
Total Total Total
Total Total Total Total Total

Cross Check:
AUto year
Cross Check:
auto condition

auto make

end milage

start milage

total miles

Note: the date8, TE8, BDR8...they all go up to 22

So this is the Form that the users will be filling out. I first create
a Main Table putting just the totals and some other relevant fields
that you can see in the above form. But then I realize that my manager
want to see a report with a DETAIL view of everysingle control of the
form. So basically I need to store in a DB everysingle control that is
on the form so I can display it in a detail report. So I started adding
all the field to my Main Table and of course it didnt let me, it showed
me the "Too many fields"

Question: whats the best way to organized my tables so I wont get the
too many fields. I want to be able to have ALL the fields control that
would be on my form backed up with a database...so I can display my
detail reports for everysingle user.

Thank you in advance

 

[John Vinson]

Hello all

I am creating a table with a lottt of fields. And its giving me the too
many fields. I guess I need to design my tables in a better way
(normalization) but I dont know where to start. The table have the
fields that will be use for an Expense Report. Here is an outline of
the Expense Report form:


Question: whats the best way to organized my tables so I wont get the
too many fields. I want to be able to have ALL the fields control that
would be on my form backed up with a database...so I can display my
detail reports for everysingle user.

Your mistake is in designing the table structure to fit the form.
That's backwards! The table structure comes FIRST, based on logical
analysis of the relationships between the Entities (real-life persons,
things, or events) of importance. Then you design the forms and
reports later, to fit the information.

Whenever you have fields with names like date1, date2, and so on -
STOP. This is a good sign that you need TWO tables. In this case,
you'll have one Expense Report with many Items. The ExpenseReport
table will have fields applying only to the expense report as a whole
(the EmployeeID, the trip date, the trip purpose, etc.); the Items
table would have the fields pertaining to each line item. Each item
will be a new *RECORD* in this table, linked to the main table.

And whenever you have fields named Total - or any field which can be
calculated on the basis of other fields - don't include it in your
table AT ALL. Calculate it on the fly, in a query or in the control
source of a form or report textbox.

The first thing to get your head around is that the Form, or the
Report, *IS SECONDARY*. You store the *information* in your dataabase,
once; and you can then view it or print it, using forms and reports as
tools - windows onto the data. You're not entering data "into the
form" - you're entering it *VIA* the form, into your Table.

I'd suggest going to
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
and looking at some of the resources there, particularly the Database
Design 101 links.


John W. Vinson[MVP]

 

[Jeff Boyce]

It sounds like you may be approaching Access backwards, starting with the
form and trying to build a table to match.

To get the best of Access' features and functions, you need to start with
the entities (things about which you want to know something) and the
relationships among the entities. In your example, you might be interested
in:

Expense Categories
Persons
Expenditures (Person x Expense Category)

By the way, why bother using Access? This sounds like something that Excel
could do quite easily, and you could keep the multiple-column approach...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Try looking here for tips on normalization:

http://support.microsoft.com/kb/283878/EN-US/

Basically . . .

1) Don't repeat groups in individual tables, such as using fields with the
names vendor1, vendor2, and vendor3.
2) Put all related data into their own tables. Customers place orders, but
customers and orders should each have separate tables.
3) Tables should only contain data that is related to each other in some way.
4) Use primary keys to identify the related data that is in its own table.
Each record in a table should contain specific data that is related through a
unique primary key.

Now, consider how each table relates to another. In a simple relationship

5) Use foreign keys to relate a record in one table with a record in
another table. If you have an orders table, and a customers table, you want
to show which customer has placed an order, so you include a foreign key in
the orders table, which will contain the same number as the primary key (from
the customers table) of the customer placing the order. Generally speaking,
you use the same name for the foreign key as you used for the primary key.

Once you have done those, then

6) Create separate tables for values that apply to multiple records. This
will be used in instances such as when you use a category to identify a
product. There can be many products that fall under the same category. You
can put all categories into a table, and then use a foreign key to signify
which category applies to a product.

In addition,

7) When there are values in two tables that relate many-to-many, you will
need to create a linking table between those two tables. This will be used
in instances such as when you have personnel who have training. The linking
table will have its own unique primary key, and will use a foreign key that
relates to a person, and a foreign key that relates to a training class.
This will enable you to have numerous classes for each person, or numerous
people for each class.

As a final task,

8) Open the relationship window, and create relationships between all of
the related primary and foreign keys. This will tell the database how
everything fits together.

If you have worked with spreadsheets in the past, you will have to relearn
everything you think you know about data management. A table might bear a
passing resemblance to a spreadsheet, but a database is not a spreadsheet. A
table in a database might be rather incomprehensible at first glance,
possibly consisting of a lot of cryptic numbers that represent foreign key
references to other tables. The beauty of a well-designed database, is that
it will do all the work of keeping track of those references, and will duly
bring forth the various types of information represented by them, in forms
and reports.

 

[erick-flores]

By the way, why bother using Access? This sounds like something that Excel

could do quite easily, and you could keep the multiple-column approach...

The format is right now on Excel...but my manage wants to look at a
final report, a detail report (for each employee) and an overall report
(summary for all employees with the totals). Thats why I though about
Access for my best option...or can I do all this in Excel, w/o creating
dbs???

 

[erick-flores]

Your mistake is in designing the table structure to fit the form.

That's backwards! The table structure comes FIRST, based on logical
analysis of the relationships between the Entities (real-life persons,
things, or events) of importance. Then you design the forms and
reports later, to fit the information.

Its not that I am doing it backwards...the thing is if i start
explaining what I want/need then you guys will not understand me. I
know I have to do tables first and then the rest. Anyways...thank for
your answers I am going to start creating different tables and start
doing some pk and fk.

 

[erick-flores]

Whenever you have fields with names like date1, date2, and so on -

STOP. This is a good sign that you need TWO tables. In this case,
you'll have one Expense Report with many Items. The ExpenseReport
table will have fields applying only to the expense report as a whole
(the EmployeeID, the trip date, the trip purpose, etc.); the Items
table would have the fields pertaining to each line item. Each item
will be a new *RECORD* in this table, linked to the main table.

When you say Items table, its that a table with, e.g. date1, date2,
date3, date4, etc...or date1, TE1, BDR1, lod1, etc.?

 

[Jeff Boyce]

The folks in Accounting have been using Excel just about forever to create
reports in whatever degree of summarization/layout. Yes, it is possible to
do that. The question you get to answer is whether you have someone with
the experience with Excel to "massage" your raw data (in Excel) into the
finished form (in Excel) that you/your management want.

Interestingly, that's the same question you have to answer for "Access"
instead of "Excel", but for Access, you also have to be able to get the raw
data into a usable (in Access) structure.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[erick-flores]

I dont need to put totals in any tables, right?
Just create a query and add a field for the total, right?

 

[Craig Hornish]

Its not that I am doing it backwards

Actually you did - in your original post you said you had the form that
the users will be filling out showed it and said you "you had a lot of
fields" - so - and I am assuming here that you took that form and started a
table with

Name
Location
Date1
TE1
.....
Date8
Now it is not bad to start with an "Idea" of what the entering process
'may' be but then you have to analyze what you have.
And that is Employees and Expence Details - (other consideration are how
other information will fit into other tables)

I will start with the first two wich will be the easiest (without furthur
explantion that will be needed from you)
Employee table will have

EmployeeID Pk
FName
LName ...

Expence Detail may have (because you could normalize futhur)
EDId Pk
EmployeeID Fk
EDDate
TE
BDR
Comm
lod
meal

Your 'entry' form would probably NOT have multiple dates shown - but give
the user the ablility to enter each days lodging and meals seperatly. You
then could show all the information for a particular period on one form if
that was needed.

....the thing is if i start

explaining what I want/need then you guys will not understand me.

Wrong - the reason why you explain what you want is so that we can
understand.
Right now I am assuming you want to have people to be able to enter
there expence reports.

I don't know if you want them to see a weeks worth - and am confused
because you actually show 8 days on your form example - very strange - could
you please Explain that

I

know I have to do tables first and then the rest. Anyways...thank for
your answers I am going to start creating different tables and start
doing some pk and fk.

--
Craig Hornish

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pfx/forum.aspx?webtag=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"

 

[erick-flores]

So, would you say its harder doing it in Access than in Excel?
Because for me it sounds way harder in Access!

PLease let me know what you think, because I am lost!

 

[Guest]

Just download the following template from the Microsoft site. It will
already have built-in forms and reports and be ready to use.

http://office.microsoft.com/en-us/templates/TC010175081033.aspx?CategoryID=CT011366791033

 

[erick-flores]

I don't know if you want them to see a weeks worth - and am confused
because you actually show 8 days on your form example - very strange - could
you please Explain that

Ok, first thank you very much for your help....I think im getting
somewhere.
That "form" that I described in my first post, was a "copy-paste" of an
excel file that the employees are using right now for Expenses Report.

Your 'entry' form would probably NOT have multiple dates shown - but give
the user the ablility to enter each days lodging and meals seperatly. You
then could show all the information for a particular period on one form if
that was needed.

This sounds confusing to me...if I have one form then I dont need to
retype the Name, Period Ending, Location, etc...on the other hand if I
have one form for each record it will be more work, maybe i am wrong.
I am just thinking on a way to leave the form as it is right now. It
would me easier for employees to enter their information. Also my
manager told me "I want this" (refering to the excel file). So I dont
know if I am asking for too much help in here...but if you can give me
some hits or something i will really apreciatted, thank you

 

[erick-flores]

WOW, this is great...let me check it out...this can save my life

 

[Jeff Boyce]

Harder ... for whom?!

Again, if you have someone with experience in X, doing something in X is
easier than someone who is just learning X. Do you have Excel experience?
Do you have someone with Excel experience?

Do you have Access experience?

(The learning curve on Access, if you haven't been using it and don't have
experience creating normalized data tables, can be fairly steep.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Just a reminder ...

Even if you have a template in Access, you still need to figure out how to
"parse" your Excel-oriented data into the structure that the Access template
uses.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

This particular template is fairly simple and self-explanatory. Could
possibly just sit and type the information over in an afternoon, depending on
how many records there are. Probably makes more sense than the Excel file
ever did. Well, at least I hope it makes more sense . . . after all,
erick-flores still hasn't come back and told us the results of this
experiment.

 

[erick-flores]

Hey, that template its helping me A LOT!....that what I need

Right now I am customizing some of the tables, and adding others so it
can fit the company's needs. But its PERFECT. The excel file that the
company was using before will be now obsolete, from no on we will be
using the Access template with some modifycation that im doing right
now

Thank you very much for ur helps

 

[Guest]

Glad that the template works for you. If you run into any problems with your
modifications, just start a new thread. There are lots of helpful people
here that can guide you through.

In case you want to create other databases, there are also templates that
are located right on your computer, as well as the ones that are on
Microsoft's site.

Good luck with your modifications.

 

[Guest]

Oh, one other thing: Do regular backups on your database. Do a backup
before changing ANYTHING. Do a backup after changing ANYTHING. It is
possible to destroy a form/report, and with a backup you can import the
form/report back in. Also, once it is being used by people, back it up very
regularly. Depending on how often data is put in, that could be daily,
weekly, or monthly.

Tools/Database Utilities/Back Up Database