Auto-rating

[Diamond via AccessMonster.com]

Hello, I need your help. I have a form that will rate customers based on
rates that I have in a rate table. Pretty much you only have to do is
select a customer then fill in the weights and access performs the
calculations. However, for some of the customers, the rate changes as the
weight of their shipment increases. How could I get access to know which
rate to select?

 

[Graham R Seach]

Don't know. Maybe you could show us your table structure, relationships, and
a synopsis of how it's supposed to work.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[cboisseau via AccessMonster.com]

Thank you for your reply.
Well the relationship is the acct number, in both the customer table and the
rate table. On the rate table, the fields are:
consignee, consignee#, customer, acct#, rate, minimum, fuel, fumigation, add
charge. On the form, the customer is selected from a combo box, then the
macro runs and updates the charges/totals.

 

[Graham R Seach]

In your original post, you said the rate can change according to the weight
of the shipment, yet in your last post, you mention nothing about weight.
Certainly it appears the rate table doesn't have any weight column. Where
are the rates defined? How are you currently calculating rates? Please be
specific.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[cboisseau via AccessMonster.com]

Hi,

So I understand I have this set up entirely wrong. So I really need your
help.
I thought I could add the weight to an unbound field on the form. Should I
add it to the table instead? How will I use it then? Query? I was thinking
an if/then statement but I don't know how to write the code for that as it is
too long. I have a customer with 10 different rates depending on weight.
The rates are defined in a rate table linked to customer table. I've just
come to realize that the rates vary on weight so it was never set up. On a
seperate table for something else (budgets), I have wt1 & wt2. Depending on
where the weight falls inbetween determines the rate.
wt1 wt2 rate
1 500 .195
501 1000 .185
1001 2000 .175

would this help my rate table?

And the rates vary in calculation. Rate calculates wt* rate, fuel surcharge
is calculated rate*wt*??%, etc.

In your original post, you said the rate can change according to the weight
of the shipment, yet in your last post, you mention nothing about weight.
Certainly it appears the rate table doesn't have any weight column. Where
are the rates defined? How are you currently calculating rates? Please be
specific.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thank you for your reply.
Well the relationship is the acct number, in both the customer table and

[quoted text clipped - 24 lines]

 

[Graham R Seach]

If the rates can be calculated acording to a standard algorithm, then
there's no need to store different rates per weight - they can be calculated
at runtime. However, if they can't be calculated (or if there is likely to
be a large number of alternatives) then you'll need to store a different
rate per weight (plus whatever other criteria are applied to determine a
final rate). This might involve joining tables in order to calculate the
rate in a query (or VBA).

Using the example you gave in your last post, and assuming you enter a
weight in a textbox called txtWeight, you could determine the weight-based
rate. The code you would use in your form might be similar to this:
Private Sub txtWeight_AfterUpdate()
Me!txtRate = Nz(DLookup("[rate]","tblRates", Me!txtWeight & _
" BETWEEN [wt1] AND [wt2] AND [customer] = " &
Me!txtCustomerID), 0)

Me!txtPrice = Me!txtRate + Me!txtFuelSurcharge
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi,

So I understand I have this set up entirely wrong. So I really need your
help.
I thought I could add the weight to an unbound field on the form. Should
I
add it to the table instead? How will I use it then? Query? I was
thinking
an if/then statement but I don't know how to write the code for that as it
is
too long. I have a customer with 10 different rates depending on weight.
The rates are defined in a rate table linked to customer table. I've just
come to realize that the rates vary on weight so it was never set up. On
a
seperate table for something else (budgets), I have wt1 & wt2. Depending
on
where the weight falls inbetween determines the rate.
wt1 wt2 rate
1 500 .195
501 1000 .185
1001 2000 .175

would this help my rate table?

And the rates vary in calculation. Rate calculates wt* rate, fuel
surcharge
is calculated rate*wt*??%, etc.

In your original post, you said the rate can change according to the
weight
of the shipment, yet in your last post, you mention nothing about weight.
Certainly it appears the rate table doesn't have any weight column. Where
are the rates defined? How are you currently calculating rates? Please be
specific.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Thank you for your reply.
Well the relationship is the acct number, in both the customer table and

[quoted text clipped - 24 lines]

which
rate to select?

 

[cboisseau via AccessMonster.com]

Hi Graham,

Thank you for your reply. I'm going to work with this now, but one last
thing. Will this work for customers that only have one rate and it doesn't
change? Just want to be sure I don't get a runtime error if there is only
rate and no wt1 or wt2 field.

thanks.

If the rates can be calculated acording to a standard algorithm, then
there's no need to store different rates per weight - they can be calculated
at runtime. However, if they can't be calculated (or if there is likely to
be a large number of alternatives) then you'll need to store a different
rate per weight (plus whatever other criteria are applied to determine a
final rate). This might involve joining tables in order to calculate the
rate in a query (or VBA).

Using the example you gave in your last post, and assuming you enter a
weight in a textbox called txtWeight, you could determine the weight-based
rate. The code you would use in your form might be similar to this:
Private Sub txtWeight_AfterUpdate()
Me!txtRate = Nz(DLookup("[rate]","tblRates", Me!txtWeight & _
" BETWEEN [wt1] AND [wt2] AND [customer] = " &
Me!txtCustomerID), 0)

Me!txtPrice = Me!txtRate + Me!txtFuelSurcharge
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi,

[quoted text clipped - 42 lines]

 

[Graham R Seach]

In the case where no wt1/wt2 exist, you'd need to account for a null in the
DLookup.

Private Sub txtWeight_AfterUpdate()
Me!txtRate = Nz(DLookup("[rate]","tblRates", Me!txtWeight & _
" BETWEEN Nz([wt1], 0) AND Nz([wt2], 99999) " & _
"AND [customer] = " & Me!txtCustomerID), 0)

Me!txtPrice = Me!txtRate + Me!txtFuelSurcharge
End Sub

Notice in the preceding example, wt1 defaults to zero, and wt2 defaults to
99999. Using these (or similar) wide values ensures that no matter what
weight you specify in txtWeight, it will always be between wt1 and wt2.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi Graham,

Thank you for your reply. I'm going to work with this now, but one last
thing. Will this work for customers that only have one rate and it
doesn't
change? Just want to be sure I don't get a runtime error if there is only
rate and no wt1 or wt2 field.

thanks.

If the rates can be calculated acording to a standard algorithm, then
there's no need to store different rates per weight - they can be
calculated
at runtime. However, if they can't be calculated (or if there is likely to
be a large number of alternatives) then you'll need to store a different
rate per weight (plus whatever other criteria are applied to determine a
final rate). This might involve joining tables in order to calculate the
rate in a query (or VBA).

Using the example you gave in your last post, and assuming you enter a
weight in a textbox called txtWeight, you could determine the weight-based
rate. The code you would use in your form might be similar to this:
Private Sub txtWeight_AfterUpdate()
Me!txtRate = Nz(DLookup("[rate]","tblRates", Me!txtWeight & _
" BETWEEN [wt1] AND [wt2] AND [customer] = " &
Me!txtCustomerID), 0)

Me!txtPrice = Me!txtRate + Me!txtFuelSurcharge
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

Hi,

[quoted text clipped - 42 lines]

which
rate to select?