If then grouping variables help (1 Viewer)

[sneuberg]

GetGroup is a function which you can find in the Common Procedures module. Basically the code takes the command id it gets and returns the applicable group. I don't want to explain this before I can see if I can simplify it but you're welcome to try and unravel it if you don't want to wait.

 

[RobBhat]

Hi Steve...I tried to google GetGroup but with no success..I am not 100% sure what it does in practical terms but I will wait till when you are ready to explain.

Another point is this. This database I am working on is a database that has been running for 5 years and so, its populated with information. I am introducing the delivery charges now as we are streamlining the company to check the charges quoted by the delivery companies against the charges invoiced.

So, the post code per customer is as a UK post code...eg AB10 5DQ or BD3 9QB. So, the post code prefixes are not grouped per customer as in your table. I need the code to identify the prefix from the whole post code and compare against a Group of post codes for which the delivery charges are quoted.

Eg Cust post code AB10 5DQ

Delivery Company: Freight Route
Service: Economy:
Estimated Delivery Charge for AB1 to AB13 prefixes = £55
So, the VBA/SQL needs to compare the prefix AB10 from the customer table with the Group of post codes in the Delivery charges table and pick the right Group to give the value.

Thank you for your patience

Rob

 

[sneuberg]

Hi Steve...I tried to google GetGroup but with no success..I am not 100% sure what it does in practical terms but I will wait till when you are ready to explain.

That's a function I created. It will be replaced by queries in the next version that will be uploaded with an explanation soon.

So, the post code per customer is as a UK post code...eg AB10 5DQ or BD3 9QB. So, the post code prefixes are not grouped per customer as in your table. I need the code to identify the prefix from the whole post code and compare against a Group of post codes for which the delivery charges are quoted.

There's no customer information in the DeliveryCharge table. The fields for determining the cost are:

DeliveryCompany
Group
Service
Pallet


The DeliveryCompany refers to the company providing the pallet distribution service. The Group refers to the group of postal code prefixes. It was my understanding from post #7 that these four field are required to determine the cost. Is this correct?

Eg Cust post code AB10 5DQ

Delivery Company: Freight Route
Service: Economy:
Estimated Delivery Charge for AB1 to AB13 prefixes = £55
So, the VBA/SQL needs to compare the prefix AB10 from the customer table with the Group of post codes in the Delivery charges table and pick the right Group to give the value.

The prefix is extracted from the full postal code in the customers address. The group is looked up in the PostalGroups table which relates the prefixes to a group. Then the group is combine with the other three field to get the cost. Are you see this database doing something else?

 

[sneuberg]

I've attached a new version of the demo database. I will try to explain what it does. This is based on my understanding of the problem. If what I explain doesn't reflect the true situation then let me know.

The tables:

Customer: This table is there just for testing and just provides a source for a postal code.

DeliveryCharge: Please open this in design view. Note that the primary key is an Autonumber. This doesn't have any use in this demo database. I made it the primary key as the true natural key of this table is a composite of the the four fields TransportCompanyName, Group, Service, and Pallet as these uniquely define a cost. When natural keys are composites it's probably better to have an Autonumber as the primary key; however, the natural key should have a unique index so that duplicates are avoided. If you click on Indexes in the ribbon you will see a unique composite index of these fields.

Groups: This table defines the designations for the groups of postal prefixes. For this demo I have groups DG1 and DG2. In your implementation it would probably be better if you could come up with better names maybe Northern, Eastern, etc. Groups are used in the DeliveryCharge and PostalGroups so this table is there to ensure the group names are consistent between them.

PostalGroups: This table relates postal prefixes to a group. Please note that Prefix is the primary key in this table. A prefix shouldn't be assigned to more than one group at a time and this prevents that. Group is a foreign key in this table.

Services: A table that defines the services, i.e., Next day. Economy.

TransportCompanies: A table that defines the transport companies like ADD express.

Now please look at the relationship. Note that the Group is the parent table of the PostalGroups and the DeliveryCharge tables. You might think there should be a relationship between the Postal Code in the Customer table and the Prefix in the PostalGroups but that relationship can only be defined in terms of a function that extract the prefix from the postal code and so can't be shown in the relationships.

The Code:

In the Common Procedures module you will find the following function:

'Returns the part of a string before a dash or the PostCode if no dash
Public Function GetPrefix(PostalCode As String) As Variant

Dim DashPos As Long
If Len(PostalCode) = 0 Then
    GetPrefix = Null
    Exit Function
End If
'DashPos is the postion in the string where the first dash occurs or 0 if no dash
DashPos = InStr(1, PostalCode, "-")
If DashPos = 0 Then
    GetPrefix = PostalCode  'If no dash return the entire postal code
Else
    GetPrefix = Left(PostalCode, DashPos - 1)  'If theres a dash return the part before the dash

End If

End Function

I created this function based on the examples you gave in your first post. Based on that, this function defines the prefix as the part of the postal code that comes before the dash if there is one or the entire postal code if there is no dash. It's important that this be correct. Is it?

frmGroups: This is an example of how you might set up a form to maintain the prefixes associated with a group and the delivery charges. Please try this. If it doesn't make sense to you then I am probably missing something.

frmDelivery: This is the main test form. This should be doing what you need. If not we need to figure out why. To calculate the cost this form uses the qryEstimatedCost which in turn uses the qryCompanyToGroup query.

qryCompanyToGroup: This is an unusual query in that it has a function in the join. Access can't represent this query in the grid mode so you can only view the SQL which is:

SELECT Customers.ID, PostalGroups.Group
FROM Customers INNER JOIN PostalGroups ON [COLOR="blue"]GetPrefix([/COLOR]Customers.PostalCode[COLOR="blue"])[/COLOR] = PostalGroups.Prefix;

This basically forms the relationship between customers and groups by extracting the prefix from the Postal Code and joining that with the prefix from the PostalGroups table. The result is a mapping of customer id to Group. This is used in the qryEstimatedCost query


qryEstimatedCost: You can view this in the design view grid mode. You can see the qryComanyToGroup query is joined to the DeliveryCharge table on the Group field. The criteria for this query comes from the frmDelivery form. Note that the criteria for the ID in the qryComanyToGroup query is

[forms]![frmDelivery]![CompanyName]

which is the ID in the customers table as that is the bound field in that combo box.

You might be able to simplify this some in your implementation depending on what's directly available in your form. The frmDelivery doesn't have the postal code so that adds a little complexity here.

 

[RobBhat]

"The DeliveryCompany refers to the company providing the pallet distribution service. The Group refers to the group of postal code prefixes. It was my understanding from post #7 that these four field are required to determine the cost. Is this correct?"

Hi Steve...This is correct.


The prefix is extracted from the full postal code in the customers address. The group is looked up in the PostalGroups table which relates the prefixes to a group. Then the group is combine with the other three field to get the cost. Are you see this database doing something else?

And this is also correct

Regards

Rob

 

[RobBhat]

Hi again Steve

I couldnt see the new version of the demo DB attached. Could you please send it again?

Thanks...Rob

 

[sneuberg]

Hi again Steve

I couldnt see the new version of the demo DB attached. Could you please send it again?

Thanks...Rob

I pushed a wrong button and lost the attachment before. I have a another version coming shortly too.

 

[sneuberg]

There won't be another version. At least not the one I was working on where I attempted to do what you were doing in your original post. If that had work it would have simplified this quite a bit but it doesn't. Here's why.

Consider the group

DeliveryGroup2 = AB17, AB18, AB19, AB20, AB21, DN1, DN2, DN3, DN4, DN5, DN6, DN7, DN8, DN9, DN11, DN12

and a postal code DN11-78. An expression something like

PostalCode Like DeliveryGroup2 & "*"

has two result namely DN1 and DN11. So this wouldn't work unless you could be sure that any prefix to a prefix as DN1 is to DN11 was alway in the same group.

 

[RobBhat]

Ah...Yes..I see the logic...I am going to study the version that you sent Steve...

 

[RobBhat]

Hi Steve...What you have created in such a short space of time has left me amazed. It is astonishing. It works even though the fields are unbound on the frmDelivery. The data. Am I correct in saying that on a practical level, I have to do the following


1 Create transport companies (on a Form to be created)
2 Create a Group eg DG3, DG4 etc in frmGroups
3 Create postal Groups in frmGroups and in the 2 subforms, assign postcodes to the groups and the relevant transport charges
4 Use frmDelivery (which will be incorporated into my Delivery Note Form, a screenshot of which I sent you).

Now for some comments in the next few posts on your amazing masterpiece

 

[RobBhat]

"Now please look at the relationship. Note that the Group is the parent table of the PostalGroups and the DeliveryCharge tables. You might think there should be a relationship between the Postal Code in the Customer table and the Prefix in the PostalGroups but that relationship can only be defined in terms of a function that extract the prefix from the postal code and so can't be shown in the relationships."

I am not sure where this function kicks in. Could you tell me?

 

[RobBhat]

"I created this function based on the examples you gave in your first post. Based on that, this function defines the prefix as the part of the postal code that comes before the dash if there is one or the entire postal code if there is no dash. It's important that this be correct. Is it?"

Dash…Normally, in UK postcodes, we have a gap between the prefix and suffix eg AD10 2BY. In your examples in the customer table, you hve used a hyphen. Is this what you mean by a dash?
When the database was created all those years ago, it was a simple field and not one custom made to take post codes. Staff have generally put a gap after the prefix but not consistently. So, if possible, a message would be good to have to notify the user if its not found.
In the long term, I have to set a postcode field designed to take post code. Not sure howbut will have to try.
Another issue is that the users have not been putting in the post code consistently in the correct field. Other address field lines are being used ad hoc!. But, this fine tuning can be done once the basics are in place?
I am not sure where you have applied this common procedures module. Please let me know.

 

[RobBhat]

"SELECT Customers.ID, PostalGroups.Group
FROM Customers INNER JOIN PostalGroups ON GetPrefix(Customers.PostalCode) = PostalGroups.Prefix;"

So, it looks at the Customer table, find the post code prefix and then picks the postal group from the postal group table. Then assigns the Customer ID the appropriate postal Group?

 

[RobBhat]

qryEstimatedCost:

This query is in the event procedure for the get quote button on frmDelivery. Excellent

 

[RobBhat]

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

 

[RobBhat]

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

 

[sneuberg]

Edit: I didn't see a lot of your post when I wrote this.

Thanks for the complement but whether it's a masterpiece is yet to be seen.

At this point I suggest testing the structure to see if it is correct or at least adaptable to the real world. You could do this by trying to adding some of the rate information from the transport companines directly into the tables or with the forms I created and see if their are any problems.

I noticed that ADD express says on their WEB site

Our simple pricing structure incorporates all of the UK on either a Next Day or Economy service.

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?

 

[sneuberg]

"Now please look at the relationship. Note that the Group is the parent table of the PostalGroups and the DeliveryCharge tables. You might think there should be a relationship between the Postal Code in the Customer table and the Prefix in the PostalGroups but that relationship can only be defined in terms of a function that extract the prefix from the postal code and so can't be shown in the relationships."

I am not sure where this function kicks in. Could you tell me?

The function is used in this query:

"SELECT Customers.ID, PostalGroups.Group
FROM Customers INNER JOIN PostalGroups ON GetPrefix(Customers.PostalCode) = PostalGroups.Prefix;"

 

[sneuberg]

"I created this function based on the examples you gave in your first post. Based on that, this function defines the prefix as the part of the postal code that comes before the dash if there is one or the entire postal code if there is no dash. It's important that this be correct. Is it?"

Dash…Normally, in UK postcodes, we have a gap between the prefix and suffix eg AD10 2BY. In your examples in the customer table, you hve used a hyphen. Is this what you mean by a dash?

Yes I meant hyphen when I wrote dash.

When the database was created all those years ago, it was a simple field and not one custom made to take post codes. Staff have generally put a gap after the prefix but not consistently. So, if possible, a message would be good to have to notify the user if its not found.
In the long term, I have to set a postcode field designed to take post code. Not sure howbut will have to try.
Another issue is that the users have not been putting in the post code consistently in the correct field. Other address field lines are being used ad hoc!. But, this fine tuning can be done once the basics are in place?
I am not sure where you have applied this common procedures module. Please let me know.

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?

 

[sneuberg]

"SELECT Customers.ID, PostalGroups.Group
FROM Customers INNER JOIN PostalGroups ON GetPrefix(Customers.PostalCode) = PostalGroups.Prefix;"

So, it looks at the Customer table, find the post code prefix and then picks the postal group from the postal group table. Then assigns the Customer ID the appropriate postal Group?

In effect Yes.