• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1119
  • Last Modified:

Postage table SQL query required

I have created a table in MS Access:

pcID      WeightKG1      WeightKG2      Courier      Price
1      0      1      Royal Mail      £3.95
2      1      2      Royal Mail      £5.95
3      2      5      Parcelforce 48 Hour      £7.95
4      5      25      Parcelforce 24 hour service      £9.95
5      25      1000      Parcelforce 24 hour service      £0.00

A user can specify a total weight in a form, then an SQL query needs to select the correct courier above, by determining if the weight is between the 'WeightKG1' and 'WeightKG2' column values.

For example, if a user specified 4KG, then the query should return 'Parcelforce 48 Hour'. As 4KG falls between the band for row id 3 (pcID).

1) What is the SQL to determine the correct courier depending on the weight a user specified? I may need to change the table structure, if so, to what?

Thanks in advance,

Lee.
0
LeeGolding
Asked:
LeeGolding
2 Solutions
 
Arthur_WoodCommented:
what programming environment and database are you using:

in Access and or VB:

strSQL = "SELECT COURIER FROM POSTAGETABLE WHERE WEIGHTG1 <= " & txtUserWeight & " AND WeightG2 >= " & txtUserWeight

AW





0
 
LowfatspreadCommented:
select courier,price
 from postage
where 4 between weightkg1 and weightkg2
order by price ,courier


is the basic query

you table can be improved in several ways...

basically normalise it...


have a courier table...
will possibly come in handy later for order tracking type services...


Couriers
courierID         Int
Name              varchar(50)
ContactDetails varchar(250)


Postage
PostageID
CourierID
WeightKG1
WeightKG2
CourierServiceName
Price
...

you need to analyse the service are more...

e.g. Airmail, Surface , nextday, First/Second class post how should they be categorised...
   
are recorded delivery  style options addons to a standard service
or is it generally view as a service option....


hth
 


0
 
LeeGoldingAuthor Commented:
Thanks I'll give it a whirl and get back soon.

Lee.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now