Beregning af fragt

You will need another table with "fragt" method and price (per kilo) for example. Then it sjust a simple case of calculating the fragt for the goods.

Terry,

I have a table named fragt. - Should I in this table add the additional rates/options ?

And how to calculate the rate based on multiple orders, ie item nr 1 = 2 kgs and item nr 2 = 5 kg - total = 7 kg

How to get the access to calculate the total and get the rigth price ?

Hi!
I can only make suggestions as I cant see your application OR your database!


What else does the fragt table contain?

I would suggest something like a combo (drop down) containing the fragt methods. Or maybe radio buttons where the user chooses one of them. They dont need to see the price which youhave in your database but you need to get thsi price when they choose the fragt method.

Then you will have to wait until they are finished chooseing (hopefully :o) ) many items. At som estage I would think you display the total cost, at the same time you can calculate the total weight. Then you just calculate the total weight (example 7 kg) with the kilo price for the fragt method which you have in a variable.

I dont know if you should think of this but quit eoften the fragt price will be cheaper per KILO as it gets heavier. If this is h ecase then you may need to have fragt price intervals in your table.

Method 1, 1-5 Kilo  kr.....
Method 1, 6-10 Kilo kr....
Method 2, 1-5 Kilo  kr.....
Method 2, 6-10 Kilo kr....
.
.

Then you will need to make an SQL Select from the database jsut before you shop the total cost.

Hope that all makes sence.

It may also be an idea to save the "fragt" price in the ORDER table as this may change in the fragt table.

terry - would you take a look at the database (access2000)
and based on what you see either recommend or do the nessecary changes ?

my email addy ifa@sol.dk

I have sent you a mail so you can see where to send

Just so everyone can see what is happening

Shipment
I suggest that you have the following fields ID (Primary key)
Art     - this is the text you display in a combo (drop down) or as radio buttons.
FlatFee – Start price

EXAMPLE:
Shipment
ID = 1
ART “Postdanmark”
FlatFee 110.00

ID = 2
ART “Fedex”
FlatFee 95,00


Now you are going to need another table, you could give it the name ArtKiloPrice or something.
Fields:
ID (primary key)
ShipmentID (foreign key which is related to the ID in shipment table)
UpToKilo - This is the max weight for this price, 10 for example.
KiloPrice – This is the price per kilo.

EXAMPLE
ID 1
ShipmentID 1
UpToKilo 10
KiloPrice 25

ID 2
ShipmentID 1
UpToKilo 20
KiloPrice 20

ID 3
ShipmentID 2
UpToKilo 20
KiloPrice 20

ID 4
ShipmentID 2
UpToKilo 40
KiloPrice 15

Then you can have a query to get alll the information.
SELECT Shipment.Art, Shipment.FlatFee, ArtKiloPrice.UpToKilo, ArtKiloPrice.KiloPrice
FROM Shipment INNER JOIN ArtKiloPrice ON Shipment.ID = ArtKiloPrice.ShipmentID;

and here is an example as to make the calculation, here I use ID = 1 (PostDanmark) and 5 kilo

SELECT TOP 1 Shipment.ID, Shipment.Art, Shipment.FlatFee, ArtKiloPrice.UpToKilo, ArtKiloPrice.KiloPrice, [KiloPrice]*5+[FlatFee] AS TotPrice
FROM Shipment INNER JOIN ArtKiloPrice ON Shipment.ID = ArtKiloPrice.ShipmentID
WHERE (((Shipment.ID)=1) AND ((ArtKiloPrice.UpToKilo)>5))
ORDER BY ArtKiloPrice.UpToKilo;

Ivan have you had a look at this?

Terry, yes i have and I have tried to work further with it.

I have one fast question for you - if I decide to wait for adding the shipment calculation at a later stage, can I do that. The finanl calculation at the webpage is causing some problems in in updating when adding more items to the shopping card.

Im planning in setting up the products table in a way that I by hand adds the shipment cost per item (for now)

At a later stage when we have sort the calculation problem, then add the functionality of precis shipment calculation.

Do you have some guidelines for me ?

Hi Ivan,
It should be not problem to implememnt this at a later stage, but try to keep it in mind when altering your tables. Try and get your tables as they should be BEFORE putting it all to work or you may end up having problems later if the database desugn needs altering drastically.

Hi Terry,

Ok - I have made some modifications to the database which you have seen. The modifications are :

Table Vare (before item) - added following colums:
vaegt, indkoebspris, udsalgspris

table shipment  - added colums id, vaegt, pris

I want a table in which the following values are accumilated/calculated:

udsalgspris + pris = total price per vare

The total price per vare is the value send to asp page.

Not sure what it is you want!

You can make a query in Access which makes the calculation, you should be able to see an example of how to make a query and a CALCULATED field in the dB i sent!

Then in your ASP use the query instead of the table in the SQL selected

Terry

Ill try that

OK

Den samlede pris beregnes af følgende forespørgsel:

SELECT vare.resellerpris, DMin("FragtPris","Fragt","[Vaegt]*100>=" & [Vaegt]*100) AS Fragtpris, [resellerpris]+[FragtPris] AS TotalPris
FROM vare;

Forespøgslen kan kun beregne for en enhed af gangen. Hvis der skal sendes flere samtidigt, skal du lave en tabel, der indeholder, hvor mange af hver vare, der skal leveres pr. kunde/ordre.