If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem designing tables: Too many fields
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 |
#2
|
|||
|
|||
Problem designing tables: Too many fields
On 8 Aug 2006 15:04:04 -0700, "erick-flores"
wrote: 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/conrad...resources.html and looking at some of the resources there, particularly the Database Design 101 links. John W. Vinson[MVP] |
#3
|
|||
|
|||
Problem designing tables: Too many fields
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 "erick-flores" wrote in message oups.com... 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 |
#4
|
|||
|
|||
Problem designing tables: Too many fields
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. |
#5
|
|||
|
|||
Problem designing tables: Too many fields
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??? |
#6
|
|||
|
|||
Problem designing tables: Too many fields
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. |
#7
|
|||
|
|||
Problem designing tables: Too many fields
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.? |
#8
|
|||
|
|||
Problem designing tables: Too many fields
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" wrote in message ups.com... 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??? |
#9
|
|||
|
|||
Problem designing tables: Too many fields
I dont need to put totals in any tables, right?
Just create a query and add a field for the total, right? |
#10
|
|||
|
|||
Problem designing tables: Too many fields
"erick-flores" wrote in message
ups.com... 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 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/pf...g=ws-msdevapps "Think outside the box, because anything is possible." "How long it will take or whether it requires divine intervention is another issue" |
Thread Tools | |
Display Modes | |
|
|