If then grouping variables help (1 Viewer)

[RobBhat]

Hi Steve

So using the examples:

In the table pc Group say for post codes BD1, BD2,...to BD10

TblpcGroup
pcID - number (This is autonumber with value 1)
pcArea - Text i.e. BD
pcDistrict - number - start number for the group - This will be 1
pcDistrict2 - number - end number for the group - This will be 10


And, in the table pc Group say for post codes BD11, BD12,...to BD20

TblpcGroup
pcID - number (This is autonumber with value 2)
pcArea - Text i.e. BD
pcDistrict - number - start number for the group - This will be 11
pcDistrict2 - number - end number for the group - This will be 20

And in the table pc Group say for post codes LS1, LS2,...to LS9

TblpcGroup
pcID - number (This is autonumber with value 3)
pcArea - Text i.e. LS
pcDistrict - number - start number for the group - This will be 1
pcDistrict2 - number - end number for the group - This will be 9

Is my assumption correct?

Thanks

rob

 

[static]

That is correct.

One thing to note though
Apparently this is a valid postcode: SW1W 0NY

1W is obviously not numeric. Not sure how you'd handle that. Other than striping the W off.

 

[RobBhat]

Thanks Steve again

I will do a DB along the lines of your recommendation and if I get stuck, I will request your help.

I think, we can disregard one or 2 prefixes and will have a redundancy plan whereby, for any that we cant deal with, we will have a message box asking the operator to manually input?

Rob

 

[RobBhat]

Hi Static

I am sorry I thought you were Steve..Apologies for assuming..

Now, I have a very basic knowledge of Access 2003. From your instructions, I can see only 1 table and many codes. How do I apply this to my requirements? I have multiple variables - Different freight companies, pallets, economy and express delivery services with different costs.

I dont know how to apply the codes to all this.

I apologise for my ignorance

Rob

 

[static]

How do you work out the prices?
I know there are several variables but there must be some sort of formula involved.

 

[static]

Looking at the database sneuberg posted 'Delivery Groups V4.mdb', he used Groups like DG1 and DG2, whereas I've used a number ID.

I haven't read this whole thread and don't know if those group 'names' mean something. In either case my table/code is just a simplified way of doing the PostalGroups table.

Everything else should be the same I guess; Enter the postcode, get the group ID (or name) then get the value from the DeliveryCharge table as normal based on pallets and such.

 

[RobBhat]

Thanks Static...I am not sure how I can apply it to my database to make it functional. I am posting the database, that sort of works using ideas from Steve original database but not using the postcode groups. But, the form that it needs to be applied to is the Delivery note form. If you get a chance, if you can tell me what tables I will need to apply your Codes to make it work, I will be grateful. I will post it tomorrow morning again.

Thanks again,

Rob

 

[RobBhat]

Hi Static

The result I want can be seen in the form "Delivery Note". If you can help me get the result using your code, I will be grateful. I am not advanced enough to apply the.

Thanks

Rob

 

[static]

Nope, I can't see how you'd apply it either.

There are 57 records in TblPostcode. They are all used in TblPallet and all of the data in TblPallet appears to be unique. So it doesn't look as though you can group the codes together any further. Maybe you just need to go with what you have.

I made a couple of observations though.

TblPallet seems a bit superfluous. I'd be tempted to put PostCodePrefix and ServiceID in TblPallet. A four character string will use 8 bytes, the same as a long int so shouldn't be too expensive. And you'll need fewer and simpler queries later when tying the data together.

Try to keep names consistent. You have names like Freight_Comp, Courier_Company and Transport_Company. Simple things like that can become a nightmare to work with.