Postage table SQL query required

Posted on 2006-06-07
Last Modified: 2008-03-10
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,

Question by:LeeGolding
    LVL 44

    Accepted Solution

    what programming environment and database are you using:

    in Access and or VB:

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


    LVL 50

    Assisted Solution

    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...

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


    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....



    Author Comment

    Thanks I'll give it a whirl and get back soon.


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now