If then grouping variables help

[RobBhat]

Hi Steve

"Unless the other companies have this pricing structure this seems to violate the rule that all transport companies will use the same groups. How do you see this fitting into this this design? Is this one of the exceptions that needs to be dealt with? I suggest we look at these exceptions at this point to make sure they can be incorporated. Could you tell me about them?"

I have thought about it. The Delivery companies send us their tariffs. For now we are interested in ADD Express and Freight Route.

Luckily the Groupings of post codes are very similar for these companies. There are occasional exceptions where ADD Express might have one or two post code prefixes in a different Group compared to Freight Route. For these isolated cases, I will have to exclude them from the major groups and create their own groups to prevent such problems. If, in the future, we use Freight companies that are significantly different, I will then need to update the groups. I am assuming that this will be possible?

 

[sneuberg]

So now we have the basic gist of it working...How do I make the frmDelivery fields bound and functional?

I don't know what you mean. What's the status of the form you displayed the image of?

Also I'm I don't know if this is ready for that. I think we should consider these exceptions you talked about before you implement this. A reliable conversion between a postal code and a prefix should be established too.

 

[sneuberg]

If then conundrum

Coming back to this. I just thought of a way to circumvent the conflict you mentioned.

Take the example below:

Prefixes AB1 to AB12 £60
Prefixes AB13 to AB23 £70
Prefixes AB24 to AB35 £50

We can do the following:

1 Group AB4 to AB12 and give it a name 'Fred'
2 Group AB13 to AB23 and give it a name 'Mike'
3 Group AB24 to AB35 and give it a name 'Michelle'
4 Group AB1, AB2, AB3 and give it a name 'Sandra'

Then if we use the If then Statement in the current sequence, it will probably avoid the conflict

eg If Like "Sandra * " then go to a costings sub for Sandra (This should pick out AB1, AB2, AB3)

ElseIf Like "Fred * " then go to a costings sub for Fred (If post code is AB10, the previous statement will filter out AB1 to stop the conflict)

ElseIf Like "Mike * " then go to a costings sub for Mike (Again, AB1 and AB2 have been filtered out)

ElseIf Like "Michelle * " then go to a costings sub for Michelle (Again, AB2 and AB3 have been filtered out)

Is this doable? What are your thoughts on this?

Rob

I don't want to think about this right now. I'm getting a headache. Maybe I'll think about it tomorrow.

 

[RobBhat]

"We can modify the function so that it extracts whatever comes before a hyphen or space. If you can give me the rules I'll give you a function but if the data doesn't conform to any rules and it just chaotic then I'm not sure what can be done unless you are willing to accept best guess on this.

Does this system have to work with existing data or can it just work with new entries where you can control the input of the postal codes?

Can you get ahold of a source for valid postal codes?"

I found a sample database for validating UK postcodes on this sight. The link is below:

http://www.access-programmers.co.uk/...light=postcode

Of course, I am in no position to understand the code and not sure how to implement this on my database. But, I will give it a go.

A link also talks about input masks for postcodes. They dont give examples on the formatting that I can understand.

http://www.access-programmers.co.uk/forums/showthread.php?t=202048

 

[RobBhat]

"I don't know what you mean. What's the status of the form you displayed the image of?"

What I mean is that, in my live database, all these functions that you have created will need to be implemented on fields that are derived from tables. The data created needs to be stored in tables for each record created. And there are hundreds of individual records that need to be created. In your frmDelivery, all the fields are unbound. So, I cant create new records.

"Also I'm I don't know if this is ready for that. I think we should consider these exceptions you talked about before you implement this. A reliable conversion between a postal code and a prefix should be established too."

I agree. I will populate the database with real data and test it over next week and give you feedback

 

[RobBhat]

"I don't want to think about this right now. I'm getting a headache. Maybe I'll think about it tomorrow."

LOL..Yes I agree...It is a hell of a lot to take in...And I thank you again

 

[sneuberg]

Hi Steve

"Unless the other companies have this pricing structure this seems to violate the rule that all transport companies will use the same groups. How do you see this fitting into this this design? Is this one of the exceptions that needs to be dealt with? I suggest we look at these exceptions at this point to make sure they can be incorporated. Could you tell me about them?"

I have thought about it. The Delivery companies send us their tariffs. For now we are interested in ADD Express and Freight Route.

Luckily the Groupings of post codes are very similar for these companies. There are occasional exceptions where ADD Express might have one or two post code prefixes in a different Group compared to Freight Route. For these isolated cases, I will have to exclude them from the major groups and create their own groups to prevent such problems. If, in the future, we use Freight companies that are significantly different, I will then need to update the groups. I am assuming that this will be possible?

When I was in the Air Force we had a structural problem that was similar to this. It concern groups of equipment which fulfilled a certain function. Imagine for example a stereo system made up of amplifiers, speaker, tape deck, CD player, etc. We would have a standard system like this deployed at nearly every base but there would be exception that would screw this up. Some base would need an addition headset for example. So with this what's the appropriate key? A composite of the group part number and the base name? That really messes up a clean structure and just for this one exception. In you case the exception would be the company.

I don't know how to design this without including the company as part of the primary key in the PostalGroups table. If the fact that that company might only be needed for let's say three out of 300 entries doesn't change this. So if there weren't these exception the table might have 300 entries but the exceptions make it grow to 600 with two companies, 900 with three companies etc.

I need to think about this for a while. Maybe I'll post a question about this on this forum.

 

[RobBhat]

Got it...Thanks Steve

 

[RobBhat]

Hi Steve

I will be out of the country from later on today till Monday. If you dont hear from me till then, please understand.

Rob

 

[sneuberg]

Hi Steve

I will be out of the country from later on today till Monday. If you dont hear from me till then, please understand.

Rob

Machts Nichts. Have a good trip. I probably won't be able to come up with a solution to the exceptions problem before then anyway.

 

[sneuberg]

I've attached a new version which presents one way of addresses the issue of these exceptions. Please open the database for the following explanations.

Please look at the relationships and note the new table PostalGroupExceptions. This new table is the only change to the structure. The PostalGroups table is still used the same way as before and the PostalGroupExceptions act as a supplement to it. The PostalGroupExceptions table has a composite key of Prefix and TransporCompanyName. You can close the relationships.

Now open the frmGroups. The group AEG1 is a group I added which is an exception for the prefix AB10 for the company Add Express. This form needs some work. Once a users adds a prefix to either the Postal Groups or sfrmPostalGroupExceptions subforms the other subform should be disabled or maybe a radio button should be added to select normal or exceptions mode. Also the choices in the TransportComanyName in the Delivery Change need to be restrict to the companies added in the sfrmPostalGroupExceptions subform. With only Add Express added it wouldn't make any sense to add Delivery Charges for Freight route as the code won't consider them without an entry in the sfrmPostalGroupExceptions subform, i.e., the PostalGroupExceptions table. If you navigation to records 2 and 3 you will see that DG1 and DG2 are the same as they was. Close the frmGroups

Now open the frmDelivery form. Choose the company GPL. It has a postal code of AB10-16. Recall that the exception was for prefix AB10. Choose Add Express for the Transport Co. After you select the number of pallets and service and click on Get Costs you will see the charges that were enter for this exception. If you change the company to Freight route the charges will be base on the regular postal groups. You can close the frmDelivery

If you open the qryEstimatedCost in design view you will see the criteria for the TransportCompanyName, Service, and Pallet come directly from the frmDelivery. However the Group's criteria is the function call:

GetGroup([Forms]![frmDelivery]![PostalCode],[Forms]![frmDelivery]![TransportCo])

The PostalCode and TransportCo are fed to the GetGroup function which returns the applicable group. You can close the qryEstimatedCost.

If you switch to the Visual Basic editor and open up the Common Procedures module you will see, in addition to the GetPrefix function discussion before, the GetGroups function. The code for that is:

Public Function GetGroup(PostalCode As String, TransportCo As String) As Variant

Dim Prefix As Variant
Prefix = GetPrefix(PostalCode)
If IsNull(Prefix) Then
    GetGroup = Null
    Exit Function
End If
'check postal group exceptions
GetGroup = DLookup("[Group]", "[PostalGroupExceptions]", "[TransportCompanyName] = '" & TransportCo & "' AND [Prefix] = '" & Prefix & "'")
If IsNull(GetGroup) Then 'no exception found
    GetGroup = DLookup("[Group]", "[PostalGroups]", "[Prefix] = '" & Prefix & "'")
End If

End Function

This code gets the prefix from the postal code using the GetPrefix function then looks in the PostGroupExceptions table for a group. If none is found then it looks in the PostalGroups table.

If this is not clear on any points please let me know.

 

[RobBhat]

Hi Steve

I modified your database to come very close to what I need. I thank you for it. But, I hit a stumbling block in the syntax of the query. This works:

SELECT QPostCodeCharges.Charge
FROM QPostCodeCharges
WHERE (((QPostCodeCharges.Courier_Company)=forms!FolCustDN!Cust_Transport_Company) And ((QPostCodeCharges.Service)=forms!FolCustDN!Cust_Delivery_Type) And ((QPostCodeCharges.PostCodePrefix)=forms!FolCustDN!Cust_DelPCode) And ((QPostCodeCharges.PalletNo)=forms!FolCustDN!Cust_Pallets));

But, I dont know how I can make the following line work so that it compares the first 4 characters of the field "forms!FolCustDN!Cust_DelPCode)" equal to "(QPostCodeCharges.PostCodePrefix)"

I am attaching the database that I came up with


Thanks again

Rob

 

[sneuberg]

You could do that with the Left function as shown in red below but I don't know how that would work as the prefixes are not all four characters long.

SELECT QPostCodeCharges.Charge
FROM QPostCodeCharges
WHERE (((QPostCodeCharges.Courier_Company)=[forms]![FolCustDN]![Cust_Transport_Company]) AND ((QPostCodeCharges.Service)=[forms]![FolCustDN]![Cust_Delivery_Type]) AND ((QPostCodeCharges.PostCodePrefix)=Left([forms]![FolCustDN]![Cust_DelPCode],4)) AND ((QPostCodeCharges.PalletNo)=[forms]![FolCustDN]![Cust_Pallets]));

 

[RobBhat]

Hi Steve

I changed the query where it looks for either the first 3 characters or the first 4 characters to get a match. This should capture all the post code prefixes:

SELECT QPostCodeCharges.Charge
FROM QPostCodeCharges
WHERE (((QPostCodeCharges.Courier_Company)=[forms]![FolCustDN]![Cust_Transport_Company]) AND ((QPostCodeCharges.Service)=[forms]![FolCustDN]![Cust_Delivery_Type]) AND ((QPostCodeCharges.PostCodePrefix)=Left([forms]![FolCustDN].[Cust_DelPCode],4)) AND ((QPostCodeCharges.PalletNo)=[forms]![FolCustDN]![Cust_Pallets])) OR (((QPostCodeCharges.Courier_Company)=[forms]![FolCustDN]![Cust_Transport_Company]) AND ((QPostCodeCharges.Service)=[forms]![FolCustDN]![Cust_Delivery_Type]) AND ((QPostCodeCharges.PostCodePrefix)=Left([forms]![FolCustDN].[Cust_DelPCode],3)) AND ((QPostCodeCharges.PalletNo)=[forms]![FolCustDN]![Cust_Pallets]));

 

[RobBhat]

Hi Steve

You are right!! Even this doesnt help when we have post codes have common characters in the prefix like BD1 and BD11. The query picks up both values, but displays the first, which is BD1. Can anything be done eg, a function that looks for the characters upto a character that is a space, as the illustration below:

Eg "BD1 " has a space after 3 characters
"BD11 " has a space after 4 characters

Thanks

Rob

 

[sneuberg]

The solution I posted in post 51 solved this problem so far as I know with the GetGroup function but I see you are not using it. That was the best solution I can think of. If you don't like it than I suggest you start a new thread with your problem.

 

[RobBhat]

Hi Steve

I have to apply it to an existing database similar to what I posted where the whole postcode is already in the field. Unfortunately, I cant see how I can apply your solution to this database. This is why, I had to improvise.


Regards

Rob

 

[static]

Table :

pcGroup
pcID - number
pcArea - Text i.e. BD
pcDistrict - number - start number for the group
pcDistrict2 - number - end number for the group

e.g.
1,BD,1,10
2,BD,11,20
3,BD,21,30

Split the postcode up

I based this on postcode format for the UK
https://en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom

Private Type postcode
    Code As Variant    'the post code, i.e. XX11 2YY
    Area As String     'i.e. XX
    District As String 'i.e. 11
    Sector As String   'i.e. 2
    Unit As String     'i.e. YY
End Type

Private Function GetPC(ByVal pc As Variant) As postcode
    If Len(pc) = 0 Then Exit Function
    GetPC.Code = pc
    pc = Replace(pc, " ", "")
    For i = 1 To Len(pc)
        Select Case Mid(pc, i, 1)
            Case 0 To 9
            GetPC.Area = Left(pc, i - 1)
            GetPC.District = Mid(pc, i, Len(pc) - 2 - i)
            GetPC.Unit = Mid(pc, Len(pc) - 1)
            GetPC.Sector = Mid(pc, Len(pc) - 2, 1)
            Exit For
        End Select
    Next
End Function

Get the group ID from pcGroup

Private Function GetPCGroup(pc As postcode) As Long
    With CurrentDb.OpenRecordset("select pcID from pcGroup where pcArea='" & pc.Area & "' and pcDistrict<=" & pc.District & " and pcDistrict2 >=" & pc.District)
        If Not .EOF Then GetPCGroup = .Fields(0)
    End With
End Function

e.g.

Private Sub Command1_Click()
    Debug.Print GetPCGroup(GetPC("BD15 xyz"))
End Sub

 

[RobBhat]

Hi Steve

thank you very much. I will try and get this done in a database.

Just to clarify about the following:

"pcDistrict2 - number - end number for the group"

I suspect that this is the suffixe eg for DB1 2PY,

pcDistrict2 is BD1 and pcDistrict2 is 2PY?

Regards

Rob

 

[static]

Hi, I'm not Steve. Just poking my nose where it doesn't belong again.

District for BD1 is 1

1,BD,1,10
2,BD,11,20
3,BD,21,30

So BD1 2PY would return 1

1,BD,1,10

BD15 2PY would return 2 because it falls between 11 and 20

2,BD,11,20


So, yeah. District is the Start number and District2 (DistrictTO) is the to number giving the range of values.