Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Postage table SQL query required

Posted on 2006-06-07
3
Medium Priority
?
1,101 Views
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,

Lee.
0
Comment
Question by:LeeGolding
3 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 16855095
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 500 total points
ID: 16855207
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
 

Author Comment

by:LeeGolding
ID: 16857510
Thanks I'll give it a whirl and get back soon.

Lee.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

564 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