Is my table setup correctly...how would you do it?

[Jakboi]

Hello,

I am rather new to Access and looking for some help with my table. I have a report that I do, that I used to do in Excel and was wondering if it was setup right or if there was a better way of doing it.

From looking around I noticed that people usually have mulitple tables and such, while mine is all in one. I see there is a table analyzer but am not sure if I should use that, or what the importance of having it split up.

I have attached my table in XML if anyone wants to share opinions on how they would set it up or what they would do.

Is there a bad thing to having it all in one table?

Thanks for any insight or help.

 

[grnzbra]

Well, let's say you have a table:
EmployeeID
LastName
FirstName
Address
City
State
Zip
Country
DateOfBirth
SocialSecurityNumber
SecurityClearanceLevel
ContactNumber
NumberType

In which you list, in ContactNumber, contact numbers such as home phone, office phone, home fax, office fax, home e-mail, office e-mail.

If you had someone with one of each of these, you would have 6 records in your table with FirstName, LastName, Address, City, State, Zip, Country, DateOfBirth, SocialSecurityNumber, SecurityClearanceLevel all repeated six times. By splitting off the contact info into a separate table and including the EmployeeID in the second table, everything except the ContactNumber and EmployeeID (In the second table) occurrs only once.

If you try to do it in one table with only one record, you end up with 5 extra fields and queries get to be nightmares. Try the table analyzer as a starting point.

 

[Jakboi]

Thanks for the Reply.

I am new to rather new to Access and appreciate the help. Ok I did the table analyzer and it split my table into 4 tables.

So basically if any information is going to be repeated in any column then it should have its own table correct?

Is there really any bad way to split up a table?

Thanks.

 

[grnzbra]

When you are joining tables (after you split a table into two, you need to join them in a query), you can have a one to one, one to many or a many to many relationship. (The many to many requires a third table with 2 one to many joins - Access cannot do a many do many directly).

The most common is the one to many such as we would have in the example above.

A one to one join may be used for something like a table that has been split because it has too many fields. So it is split into a table with the most commonly used fields and another with the rarely used fields. Most of the time, you would use just the one table and select the fields you need. However, on a rare occasion you might need some of the info in the other table, so you would use both table's in a query.

An example of a many to many relationship would be a Reports table listing all reports in an Access database, a Criteria table listing all possible criteria for these reports, and a ReportCriteria table which contained ReportID, CriteriaID, LabelCaption, Top, Left, Width, DefaultValue, RequiredYN. This would be used to create a criteria form when a report is called. The OnOpen event of the report calls the criteria form and feeds it the name of the report. The OnOpen event of the form opens a recordset from a query that join all three tables and selects the records based on the name fed to it. It then loops through the records and places the textbox for each criteria at the specified place on the form and set the criteria's label to something readable instead of the name of the textbox, sets the width as specified (determined by possible values of that criteria for that report) and default value. Here you have a bunch of report that need several criteria and a bunch of criteria that are used by several reports. For example, almost all the reports required Start Date and End Date fields. These typically defaulted to the first day of this month one year ago and the last day of last month. One report required Prior Start Date and Prior End Date as well. These defaulted to one year prior to the Start Date and End Date values. About half the report required Office Number and most of these required Employee Number.

As you work with databases, you will develop the ability to see where thing need to be split. As far as bad ways to split a table. Hmm, let's see. How about a State table that looked like:

StateID, State
1,MN
2,VT
3,NH
4,MA
5,CT
6,RI
7,NY
etc.

That's taking normalization (splitting the tables is part of a process known as normalization) to an extreme. However, if I had the need to have the state name spelled out, I might want a table of state names, but I wouldn't use an autonumber for the key field; I'd use the abbreviation field as the key.

St, State
MN, Main
VT, Vermont
NH, New Hampshire
etc.

Then, of course, there are inner join and outer joins. An inner join says "Give me these fields for each record that has a matching record in both tables." If it exists in only one of the tables, you don't get anything for that record. An outer join says "Give me these fields from this table and, if there's a match in the other table, give me those fields also." In that case, you get all the records from the first table plus the fields from the other table if there's a match and a bunch of empty fields if there's no match.

 

[Jakboi]

Thank you for the explanation. I am new and trying to see how the pieces fit together. I think now I have it set. I broke up the table manually into 11 tables. I had to break up Products because the chance of having supplying 2 of the same product.

I think now it looks almost right, at least better than it was when I had it all in one table. The reason being I had this information in Excel and didnt know how to normalize the tables.

I used Autonumber for the keys for each table.

Is there any easy way to update the queries/forms/reports with the new table information. I noticed my reports/queries/forms no longer work...think I goofed something. It wants a new parameter value, which means it cant find it right?

tblProducts1.PRODUCT ID
tblProducts.ID
PIPELINE REPORTS.ID

In the midst of splitting tables may have messed something up. Also I noticed that my original table is now "_OLD". Is it worth phasing that out. Meaning deleting it and fixing the queries/reports/forms?

This is my first database and once I realize the basics should catch on pretty quick, thanks for the help.

If there are any more suggestions on what I should do or take care, I would appreciate it. Thanks.

 

[grnzbra]

Well, you could create a query that puts the whole thing back together and looks like the original table and has the same name as the original table.

It is looking for ProductID but the field names in the tables have a number in them, so it isn't finding the ProductID anywhere. What are these products such that you are cascading tables with them? (Or is that three occurrances of the products table? But then the fields wouldn't have matching numbers.)

Also

The officers table looks like it should be
Officer
OfficerRank (1,2 or 3)
OfficerID

It depends on the nature of the content's of the three fields

Same thing for the ContactDates table. I am assuming that there will be multiple contacts. As you use the table, you lose the records of all contacts except the initial contact. But to do that, you would also have to join on tblClients.ClientID to tblContactDates.ClientID. The oldest date would be the initial contact, the "newest" date would be the next contact date and the second "newest" date would be the last contact date.

To me, it looks like the Clients table is the main table and some of the other tables should have the (one to many)ness reversed. Type and classification look good; each client has a single classification value and type value. The others should be reversed so a client can have multiple dates, amounts, time frame and product. Actually, products and amounts may be good example of many to many relationhips which would be joined by an "Orders" table.