improving the access database 2

[subs1678]

i have a transportation database with the following fields

Origin st, Origin City, Destn St, Destn City, Zip, paid amt, trans amt, keyID( one who enters all the info), fuel surcharge, mode, carrier, client, consignee, desc1, desc2, desc3( all the desc columns are most of the empty), weight, detention charge, demurrage, error code ,- There are like 2o+ fields in one table. My company then creates queries and reports based on this table.

Is this the most efficient way of working with access. Is it better to divide the table with smaller fields. Please help on how to improve this table

thanks a lot

 

[fneily]

One word - Normalization. This is the basis of Access and any relational database. You must normalize your tables before inputting any data. Find a book or online article on normalizing such as
Fundamentals of Relational Database Design

http://r937.com/relational.html

One clue is that you have one table. You might as well have an Excel spreadsheet.
Next clue you have duplicate column headings - desc1, desc2, desc3. Drop the number - duplicate fieldnames. This is against the first Normal form.
I see an error code table, client table, transportation costs table, transaction table. Of course I don't know what some of your terms are and how they relate to each other 'cause I'm not in that field.
The users must be creating very simplistic queries and reports.

 

[JoeAtWork]

Another advantage to normalized databases are that they promote consistency.

I note you have a "carrier" field. So I guess your users just type the name of the carrier in? I wonder how many variations in spelling there are for some of the carriers. This makes it difficult or impossible to make reports grouped by carrier (or filtered for a single carrier) as there will be the same carrier in different groups, one for each spelling of that carrier's name.

In a normalized database you would have a separate Carriers table. When doing transportation data entry, the user would select the Carrier name from a dropdown (whose source would be the Carriers table). That way, you ensure everyone is using the same name for the same entity.

 

[subs1678]

Thanks for the reply

Joe- one clarification please- in this table we have field called as consignee. Consignee names are all over the place- huge spelling mistakes as you pointed out. but how do we avoid these spelling mistakes if we put this field in a different table? with what other fields shall i combine this field in a new table? Could you guys please help me on this

Also will the queries and reports run faster and more efficiently if we divide a big table into smaller tables and normalize this table. Now every query is being created out of this table which has about 25 columns

I appreciate feedback and help

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

You would make a separate Consignee table that has all fields related to a consignee. There would be an ID field that needs to be unique for each Consignee (what you choose for the ID is up to you, it could be automatically generated if you want).

In the form where you enter a Transportation record, when the user gets to the Consignee field, instead of just a textbox where anything can be entered, the user needs to pick something that already exists in the Consignee table. This may be a dropdown list, or if there are thousands of records you would probably give the user a search dialog box. Only if the user can't find the consignee in the existing list (either the dropdown or the results of a search) would he be allowed to enter a new consignee record.

This doesn't eliminate the chance that your users will create a duplicate record. However, it will at least give them pause to try to look for the existing record first. Most users will take the route of least resistance, so they would probably prefer to pick it from the list rather than create a new record.

Also, in the "add new cosignee" form you could write code that checks for duplicates. For instance, it could check if the telephone number entered already exists, and if so inform the user that they may be entering a duplicate.

Finally, if they do create a new "John Smyth" when they should have chosen the existing "John Smith", it can be fixed relatively easily. This is because the Cosignee will be related to the Transportation record through it's unique ID, rather than the Name field. So if you are cleaning up the database and you know that all the "John Smyth" cosignees in the Transportation table should have been "John Smith", you can clean it up with a simple SQL UPDATE statement.

Let's say "John Smith" has the ID = 12, and "John Smyth" has the ID = 55. To clean up the Transportation table you could write a statement like:

UPDATE Transportation SET CosigneeID = 12 WHERE Cosignee = 55

After that you could just delete the unneeded "John Smyth" record.

But what you really want to do is get some background information on relational databases, as suggested by the others.

 

[subs1678]

Many of the fields like client, consignee, fuel surchareg,detention charge, mode, carrier are specific to a particular transaction i.e origin to destination.
So a transaction table could have fields like origin city, origin st, origin zip,destination city, destn state, destn zip,shipdate,paid date. If i transfer rest of the fields to another table , i would have to repeat the transaction fields again in the new table. otherwise the data in the new table would not make sense. so HOW to divide this table into two or more tables

 

[PHV]

Again, have a look here:

http://r937.com/relational.html

and here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JoeAtWork]

Basically, any field that should come from a limited list should be in its own table. Or any data that you want stored only once (i.e. you don't want duplicates where two or more records actually represent the same thing, such as a Client).

So, if you have a "Color" field, there should be a Colors table with values like Red, Green, Brown, Blue, etc.

If a field is likely to be unique and only used once in the database, then don't store in a separate table.

Now, go read the Relational article PHV has provided. It will likely clear some things up for you.

 

[subs1678]

I appreciate all the feedback but this is a transactional database. So i donot understand how is it possible to link the data. Every field like paid amt, transport amt, fuel amount , client(who ordered the shipment) , consignee( one who received it) are all linked to four fields- origin st, origin city, destn city, desn st. so how do you put these four fields in one table and the other fields ( which i described above) in another table. So all the fields basically have to be in one table to make sense of the data. Is it not?

 

[JulieMontgomery]

subs1678, let's step back for a moment. Are the folks using this database now happy with the results it's producing for them? Are there specific problems you've uncovered that need to be addressed? Your question is kind of wide-open at this point, we could give you hundreds of suggestions. If the database is working fine for you and your clients at this point it may be best to leave it as is. It's very possible to implement data transactions across tables, and from a database design perspective you've got opportunities to control redundancy by creating some new tables, but if your application has been serving you fine with just the one table then it may be wise to leave well-enough alone. If you've got the time, try searching for "Relational Databases" with your favorite search engine. It may help clarify potential application improvements down the road....