calculation not running but shows no errors

[funi.t]

hi
i have written a function to calculate the total but it is only running the first if statement.please help this is my function:
=IIf(([Type of Freight]="Economy Part Load Vehicle" Or [Type of Freight]="Express Part Load Vehicle") And [Weight]>=0 And [Weight]<=2000 And [Kilograms]=2000,[Transport Fee]*[Weight],IIf(([Type of Freight]="Economy Part Load Vehicle" Or [Type of Freight]="Express Part Load Vehicle") And [Weight]>=2001 And [Weight]<=4000 And [Kilograms]=4000,[Transport Fee]*[Weight],IIf(([Type of Freight]="Economy Part Load Vehicle" Or [Type of Freight]="Express Part Load Vehicle") And [Weight]>=4001 And [Weight]<=6000 And [kilograms]=6000,[Transport Fee]*[Weight],IIf(([Type of Freight]="Economy Part Load Vehicle" Or [Type of Freight]="Express Part Load Vehicle") And [Weight]>=6001 And [Weight]<=8000 And [Kilograms]=8000,[Transport Fee]*[Weight],IIf(([Type of Freight]="Economy Part Load Vehicle" Or [Type of Freight]="Express Part Load Vehicle") And [Weight]>=8001 And [Weight]<=10000 And [Kilograms]=10000,IIf(([Type of Freight]="Economy Part Load Vehicle" Or [Type of Freight]="Express Part Load Vehicle") And [Weight]>=10001 And [Weight]<=15000 And [Kilograms]=15000,[Transport Fee],0))))))

[jzwp11]

Seeing some of your data might help resolve the issue. Can you zip and post a copy of your database with any sensitive data removed? How is the value of the field Kilogram determined?

[funi.t]

hi the form that you should look under is called freightpak.i just realised that it is giving me all the records for the origin selected origin and destination instead of just one record

[jzwp11]

You have serious structural issues with your database. First, you have separate tables for each company that is offering transport services. And each of those looks like a spreadsheet. Access is not a glorified spreadsheet software package but rather a relational database software package. As such, it requires a different way of thinking about and organizing data. The first thing you have to become familiar with are the rules of normalization which govern how tables are set up in a relational database. This site gives an overview of the rules of normalization

Also, I noticed that you have lookups (combo/list boxes) in your tables. Even though Access has this capability; it is recommended not to have lookups at the table level. It is best to have lookups in your forms but not in the tables. This site give a more detailed explanation of why table level lookups are not recommended.

It is also recommended to not have spaces or special characters in your table or field names.

Following normalization rules, all companies should be in 1 table. Each company can offer many types of freight service (one-to-many relationship). Each sevice can be associated with different routes (origin/desitinations), so another one-to-many relationship. Further, you have different rates based on the amount of the load, so another one-to-many relationship.

If set up properly, you would not even need the calculation you are trying to resolve.

[funi.t]

The calculation of the total prices varies from one to another so how do i go about doing the calculations after i have normalised the data?
Please view the new tables and relationships and tell me if i'm on the right track

[jzwp11]

Your structure is improved but it still needs more work. As to the calculations, they need to wait until the table structure is finalized.

In the service table, you have the company name, this should be replaced by the corresponding key from the company table. Relational databases are more efficient with numeric fields rather than text fields. (Let's leave this as is for now because the service table will change as we normalize further)


In the company table you have mode of transportation. Can a company use more than 1 mode of transportation?

In the service table, you have type of freight. It is clear from your data that a company has many types of freight, so that is a one-to-many relationship. It is also clear that a type of freight can be associated with many companies so you have another one-to-many relationship. When you have two one-to-many relationships between the same two entities (companies and type of freight in your case) you have a many-to-many relationship which requires a junction table. But first you need to separate the types of freight into their own table (I have done that in the attached database).

Then relate each back to the company to which they are applicable (junction table). This is what that junction table would look like, but I'm not sure if we are ready to add that table yet.


tblCompanyFreightTypes
-pkCoFreightTypeID primary key, autonumber
-fkCompanyID foreign key to Company table
-fkFreightTypeID foreign key to tblFreightTypes

The reason I say that is because there might be a potential relationship between the mode of transportion and the freight type. You will have to provide more detail on that. Does a freight type depend on the mode of transportation offered by a company?

Also, all of the cites you have listed should be in their own table (I've done that in the attached as well).

I'm not sure what purpose the ServiceVsCompany table serves since for now you have the company and service summarized in the service table (that will change with the new structure, but I want to leave that table in place for now so as to not lose your data).

[funi.t]

each company uses one mode of transport and each company has their own different types of freight that they use.the freight type does not neccessarily depend on the mode of transport.The reason why i have a junction table for company and service was because of the freight types,which are basically the types of services that each company provides.The mode of transport is actually linked to a company.
Do I also link the locations table to the junction table?

thanks for the help

[funi.t]

I have uploaded the latest database with added new tables.tblCompanyFreightTypes(linked to location)-this has all the LocationID's for both Location 1 and 2,FreightTypeID's and Company ID's.
I also have tblCompanyFreightTypes(not linked to location)-with the companyID and FreightType ID only.

[jzwp11]

Since a company/freight type combination is associated with many locations (origin & destination) that describes a one-to-many relationship. Also since a location can be related to many company/freight type combinations that is another one-to-many relationship. So again you have a many-to-many relationship which requires a junction table.

tblCompanyFreightTypeLocations
-pkCoFrLocID primary key, autonumber
-fkCompanyFreightTypesID foreign key to tblCompanyFreightTypes (without locations)
-fkLocationID foreign key to tblLocations
-fkLocTypeID foreign key field designating origin or destination

tblLocationTypes (2 records in this table: origin, destination)
-pkLocTypeID primary key, autonumber
-txtLocationType

[funi.t]

Does this mean that in the tblLocationType i will have the txtLocationType1 Field and txtLocationType2 Field to distinguish between the origin and destination?

[jzwp11]

Not fields, you want records


tblLocationTypes (2 records in this table: origin, destination)
-pkLocTypeID primary key, autonumber
-txtLocationType

Data in the table would look like this

pkLocTypeID|txtLocationType
1|Origin
2|Destination

[funi.t]

hi i have uploaded the database with all the tables that you requested.under the CompanyFreightTypeLocation table.should i create more origins and destinations for the origins that have different prices for different weights?
For example:
A company called Trition Has:
Cape Town(10kg's-600kg's)= R2.21 /kg
Cape Town(601kg's-1000kg's)= R2.07 /kg etc

[jzwp11]

I still need to get an understanding of how the weights play into the process. Does each transport company give you a weight range and a price for each route (origin & destination combination)?

[funi.t]

The costs are given in rates per kilogram e.g $2.01/Kg.But for Triton and FreightPak the rates per kilogram have ranges i.e as the number of kilograms increases the rates decrease.
Foe example JHB to Cape Town:
(10-2000) kg's =$2.12/kg
(2001-4000) kg's=$2.07/kg

[jzwp11]

After some thought I think we need to separate out the routes (origin & destination) so that we can associate freight types/routes/loads and finally rates properly.

Each route will have 2 locations (origin and destination) so a one to many relationship. A route can apply to many company/freight types and a company/freight type can have many routes (many-to-many relationship). The junction table for this many-to-many relationship would be: tblCompanyFreightTypeRoutes

For each combination of company/freight type/routes you can have many loads weights that work into the final rate. Each load has upper and lower weight (kg) limits. To handle this I created a series of related tables.

All of the changes are summarized in the attached database.