Rate Customers by Invoiced this Year Field

[JK]

I have a database using Access 2003 with over 8,000 customers. Each Customer
record has a field called, "Invoiced This Year." This field is updated
nightly through an ODBC driver to our main company database (J.D. Edwards.)

I want to create a rating system based on that field. I have no idea how to
do this, and I have not been able to find any help online, but this is what
I'd like it to do.

When the customer is pulled in my database, I'd like there to be a five star
rating system. The higher the "Invoiced this year" field, the more stars the
customer should receive.

Any ideas how I would do this? Thx-

 

[Klatuu]

Your statement is too vague.
You need first to establish the rules.
Is it based on a customer's percentage of total company invoices?
Is based on specific dollar amount?
Where do the brackets break? For example if you were using a specific
dollar amount, you would need define the 5 brackets

1 star = $0 - $500
2 star = $501 - $1000
3 star = $1001 - $2000
4 star = $2001 - $5000
5 star = > $5000

Also, you did not say where you want to present the results of the rating.
That makes a difference in how you do it.

 

[JK]

Well first of all, I'd like to present the results by displaying stars on the
main company form. If I had a text box populated with a 1, 2, 3, 4, or 5 - I
could then make a star image appear depending on the value in the text box.

As for the logic behind this concept - that's where I'm stuck. I can't
figure out excatly how to do this.

The field I'm using is updated daily. I can't pick specific break points for
that reason. I must do a calculation based all all of the customers as a
whole and then have each customer meet a certain criteria to receive a
certain amount of stars.

I'm way over my head right now; I admit it.

I was just wondering if someone had any thoughts regarding how to go about
accomplishing such an idea.

Hope that makes more sense and thx for your reply.

The field is a dollar amount. It's called Invoiced This year and it's simply
a total dollar amount for each each customer year to date.

Thx again...

 

[Klatuu]

Well, I can't figure out the logic if I don't know the rules, but assuming
your form is a form view form, you can populate the text box with the start
by putting your logic in the Form Current event, or you might put the logic
in the form's record sourec query and display a number of starts based on the
results of the logic.

 

[JK]

Basically what I'm looking for is ideas regarding the logic. How could I take
8,000 customers based on one dollar amount field and come up with a five star
rating system. Like I said, I can't just pick the break points like you
suggested becuase the fields are updated nightly (the fields would increment
anyway even if the database wasn't linked to J.D. Edwards.) It must be a
calculation using every "total invoiced this year" field in my database. Some
way of classifying each customer using 1,2,3,4 or 5 using the "total invoiced
this year" field. Displaying the stars on the form would be easy at that
point.

I don't know... I just thought it would be a good thing for anyone to have.
I can see this being useful in many different applications. I was surprised I
couldn't find any ideas anywhere online...

Thx again for your help...

 

[Klatuu]

The break points have nothing to do with how often the data are updated. A
rating system is a rating system. Just like in school. Grades are based
either on an absolute percentage of the number of correct answers or on a
curve based on th accuracy of the class as a whole.

What I am trying to tell you is you have to define the rules before you can
write the logic to support the rule.s

 

[JK]

What you're saying makes sense, I just don't know how effective it would be
in the long run.

If I had six customers:

Cust1 = $100
Cust2 = $150
Cust3 = $200
Cust4 = $250
Cust5 = $300
Cust6 = $350

And I setup the break points as followed:

1 = $50
2 = $150
3 = $250
4 = $350
5 = $450

I would be all set.
Except..................

Next week, the values in the in the "Invoiced this year" field will be much
higher than they are today - throwing the whole thing off.

I could pick figures that would be based on the middle of the year but then
the beginning of the year and the end of the year would be off...

Unless I'm totally missing something - I'm not sure how that would work.

That's why I think a calculation based on the entire table to determin the
logic is necessary. I just have not been able to find a way to caculate the
rank each customer should receive.

Am I that far off?

 

[John Spencer]

So you want to classify the customers on the percentile they fall into
based on how many dollars are invoiced this year.

That is top 20 percent get 5 stars
The next 20 percent get 4 stars
the next 20 percent get 3 stars
the next 20 percent get 2 stars
Bottom 20 percent get 1 star
And anyone that has zero dollars gets no stars.

Is that the logic you want to apply?

If so, I would write a short sub to populate a little table with the
break points.

UNTESTED off the top of my head AIR CODE. If it errors, you can try to
fix it or ignore it and create your own


Public Sub sSetBreakPoints()
Dim strSQL as string
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim rstScores as DAO.Recordset
Dim lCount as long
Dim dMinBreak as Double
Dim dMaxBreak as Double
Dim I as Integer

strSQL = "SELECT [Invoiced] FROM YourTable" & _
" WHERE [Invoiced] is not null ORDER BY [Invoiced] DESC"

Set dbAny = currentdb()
Set rstAny = dbany.openrecordset (StrSQL)

StrSQL = "DELETE FROM ScoresTable"
dbany.Execute StrSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
set rstScores = dbany.openrecordset (strSQL)
rstany.movelast
LCount = rstany.RecordCount\5

rstany.MoveFirst
For I = 5 to 1, step -1

DMaxBreak = RstanyIInvoiced
rstAny.move LCount
DMinBreak = rstany!Invoiced
with rstScores
.Add
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

end With
Next I
End Sub

Now you can use the scores table in a non-equi join to get the number of
stars.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[JK]

That's excatly what I'm looking for... Now it's a matter of getting your code
to work. I know you've already invested much time, so maybe someone else
could give me a hand.

I added my fields to the code and tried to run it from a command button. I
then tried to run it after creating a table called ScoresTable; I added the
fields Maxbreak, Minbreak, and TopX.

When I run the code I get an error that says, "Member already exists in an
object module from which this object module derives."

I'm not very good with Visual Basic; any help would be appreciated. In the
meantime, I'll keep playing with it - hopefully I'll be able to make it work.

Thx!

So you want to classify the customers on the percentile they fall into
based on how many dollars are invoiced this year.

That is top 20 percent get 5 stars
The next 20 percent get 4 stars
the next 20 percent get 3 stars
the next 20 percent get 2 stars
Bottom 20 percent get 1 star
And anyone that has zero dollars gets no stars.

Is that the logic you want to apply?

If so, I would write a short sub to populate a little table with the
break points.

UNTESTED off the top of my head AIR CODE. If it errors, you can try to
fix it or ignore it and create your own


Public Sub sSetBreakPoints()
Dim strSQL as string
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim rstScores as DAO.Recordset
Dim lCount as long
Dim dMinBreak as Double
Dim dMaxBreak as Double
Dim I as Integer

strSQL = "SELECT [Invoiced] FROM YourTable" & _
" WHERE [Invoiced] is not null ORDER BY [Invoiced] DESC"

Set dbAny = currentdb()
Set rstAny = dbany.openrecordset (StrSQL)

StrSQL = "DELETE FROM ScoresTable"
dbany.Execute StrSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
set rstScores = dbany.openrecordset (strSQL)
rstany.movelast
LCount = rstany.RecordCount\5

rstany.MoveFirst
For I = 5 to 1, step -1

DMaxBreak = RstanyIInvoiced
rstAny.move LCount
DMinBreak = rstany!Invoiced
with rstScores
.Add
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

end With
Next I
End Sub

Now you can use the scores table in a non-equi join to get the number of
stars.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

What you're saying makes sense, I just don't know how effective it would be
in the long run.

If I had six customers:

Cust1 = $100
Cust2 = $150
Cust3 = $200
Cust4 = $250
Cust5 = $300
Cust6 = $350

And I setup the break points as followed:

1 = $50
2 = $150
3 = $250
4 = $350
5 = $450

I would be all set.
Except..................

Next week, the values in the in the "Invoiced this year" field will be much
higher than they are today - throwing the whole thing off.

I could pick figures that would be based on the middle of the year but then
the beginning of the year and the end of the year would be off...

Unless I'm totally missing something - I'm not sure how that would work.

That's why I think a calculation based on the entire table to determin the
logic is necessary. I just have not been able to find a way to caculate the
rank each customer should receive.

Am I that far off?

 

[John Spencer]

Ok. I checked the code today. Here is a rewrite that should work. You will
need to replace table and field names as appropriate.

Assumes you have a ScoresTable with three field
TopX - Number, Integer
MaxBreak - Number, Dbl
MinBreak - Number, Dbl

Public Sub sSetBreakPoints()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim rstScores As DAO.Recordset
Dim lCount As Long
Dim dMinBreak As Double
Dim dMaxBreak As Double
Dim I As Integer

strSQL = "SELECT [IntegerField] FROM [IntegerTable]" & _
" WHERE [IntegerField] is not null ORDER BY [IntegerField] DESC"

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset(strSQL)

strSQL = "DELETE FROM ScoresTable"
dbAny.Execute strSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
Set rstScores = dbAny.OpenRecordset(strSQL)
rstAny.MoveLast
lCount = rstAny.RecordCount \ 5

rstAny.MoveFirst

For I = 5 To 1 Step -1

dMaxBreak = rstAny!IntegerField
rstAny.Move lCount

If rstAny.EOF Then rstAny.MovePrevious

dMinBreak = rstAny!IntegerField

With rstScores
.AddNew
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

End With
Next I
End Sub



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

That's excatly what I'm looking for... Now it's a matter of getting your code
to work. I know you've already invested much time, so maybe someone else
could give me a hand.

I added my fields to the code and tried to run it from a command button. I
then tried to run it after creating a table called ScoresTable; I added the
fields Maxbreak, Minbreak, and TopX.

When I run the code I get an error that says, "Member already exists in an
object module from which this object module derives."

I'm not very good with Visual Basic; any help would be appreciated. In the
meantime, I'll keep playing with it - hopefully I'll be able to make it work.

Thx!

So you want to classify the customers on the percentile they fall into
based on how many dollars are invoiced this year.

That is top 20 percent get 5 stars
The next 20 percent get 4 stars
the next 20 percent get 3 stars
the next 20 percent get 2 stars
Bottom 20 percent get 1 star
And anyone that has zero dollars gets no stars.

Is that the logic you want to apply?

If so, I would write a short sub to populate a little table with the
break points.

UNTESTED off the top of my head AIR CODE. If it errors, you can try to
fix it or ignore it and create your own


Public Sub sSetBreakPoints()
Dim strSQL as string
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim rstScores as DAO.Recordset
Dim lCount as long
Dim dMinBreak as Double
Dim dMaxBreak as Double
Dim I as Integer

strSQL = "SELECT [Invoiced] FROM YourTable" & _
" WHERE [Invoiced] is not null ORDER BY [Invoiced] DESC"

Set dbAny = currentdb()
Set rstAny = dbany.openrecordset (StrSQL)

StrSQL = "DELETE FROM ScoresTable"
dbany.Execute StrSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
set rstScores = dbany.openrecordset (strSQL)
rstany.movelast
LCount = rstany.RecordCount\5

rstany.MoveFirst
For I = 5 to 1, step -1

DMaxBreak = RstanyIInvoiced
rstAny.move LCount
DMinBreak = rstany!Invoiced
with rstScores
.Add
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

end With
Next I
End Sub

Now you can use the scores table in a non-equi join to get the number of
stars.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

What you're saying makes sense, I just don't know how effective it would be
in the long run.

If I had six customers:

Cust1 = $100
Cust2 = $150
Cust3 = $200
Cust4 = $250
Cust5 = $300
Cust6 = $350

And I setup the break points as followed:

1 = $50
2 = $150
3 = $250
4 = $350
5 = $450

I would be all set.
Except..................

Next week, the values in the in the "Invoiced this year" field will be much
higher than they are today - throwing the whole thing off.

I could pick figures that would be based on the middle of the year but then
the beginning of the year and the end of the year would be off...

Unless I'm totally missing something - I'm not sure how that would work.

That's why I think a calculation based on the entire table to determin the
logic is necessary. I just have not been able to find a way to caculate the
rank each customer should receive.

Am I that far off?

:

The break points have nothing to do with how often the data are updated. A
rating system is a rating system. Just like in school. Grades are based
either on an absolute percentage of the number of correct answers or on a
curve based on th accuracy of the class as a whole.

What I am trying to tell you is you have to define the rules before you can
write the logic to support the rule.s

--
Dave Hargis, Microsoft Access MVP


:

Basically what I'm looking for is ideas regarding the logic. How could I take
8,000 customers based on one dollar amount field and come up with a five star
rating system. Like I said, I can't just pick the break points like you
suggested becuase the fields are updated nightly (the fields would increment
anyway even if the database wasn't linked to J.D. Edwards.) It must be a
calculation using every "total invoiced this year" field in my database. Some
way of classifying each customer using 1,2,3,4 or 5 using the "total invoiced
this year" field. Displaying the stars on the form would be easy at that
point.

I don't know... I just thought it would be a good thing for anyone to have.
I can see this being useful in many different applications. I was surprised I
couldn't find any ideas anywhere online...

Thx again for your help...

:

Well, I can't figure out the logic if I don't know the rules, but assuming
your form is a form view form, you can populate the text box with the start
by putting your logic in the Form Current event, or you might put the logic
in the form's record sourec query and display a number of starts based on the
results of the logic.
--
Dave Hargis, Microsoft Access MVP


:

Well first of all, I'd like to present the results by displaying stars on the
main company form. If I had a text box populated with a 1, 2, 3, 4, or 5 - I
could then make a star image appear depending on the value in the text box.

As for the logic behind this concept - that's where I'm stuck. I can't
figure out excatly how to do this.

The field I'm using is updated daily. I can't pick specific break points for
that reason. I must do a calculation based all all of the customers as a
whole and then have each customer meet a certain criteria to receive a
certain amount of stars.

I'm way over my head right now; I admit it.

I was just wondering if someone had any thoughts regarding how to go about
accomplishing such an idea.

Hope that makes more sense and thx for your reply.

The field is a dollar amount. It's called Invoiced This year and it's simply
a total dollar amount for each each customer year to date.

Thx again...

:

Your statement is too vague.
You need first to establish the rules.
Is it based on a customer's percentage of total company invoices?
Is based on specific dollar amount?
Where do the brackets break? For example if you were using a specific
dollar amount, you would need define the 5 brackets

1 star = $0 - $500
2 star = $501 - $1000
3 star = $1001 - $2000
4 star = $2001 - $5000
5 star = > $5000

Also, you did not say where you want to present the results of the rating.
That makes a difference in how you do it.
--
Dave Hargis, Microsoft Access MVP


:

I have a database using Access 2003 with over 8,000 customers. Each Customer
record has a field called, "Invoiced This Year." This field is updated
nightly through an ODBC driver to our main company database (J.D. Edwards.)

I want to create a rating system based on that field. I have no idea how to
do this, and I have not been able to find any help online, but this is what
I'd like it to do.

When the customer is pulled in my database, I'd like there to be a five star
rating system. The higher the "Invoiced this year" field, the more stars the
customer should receive.

Any ideas how I would do this? Thx-

 

[JK]

Holy crap - it worked! I think... Here are my results...

TopX MaxBreak MinBreak
5 -0.01 2161.41
4 2161.41 392.3
3 392.3 0
2 0 0
1 0 0


Why is the max break for #5 -0.01?
Shouldn't that value be the highest value in my table?

Thank you soo much!!! Both of you!!!

Ok. I checked the code today. Here is a rewrite that should work. You will
need to replace table and field names as appropriate.

Assumes you have a ScoresTable with three field
TopX - Number, Integer
MaxBreak - Number, Dbl
MinBreak - Number, Dbl

Public Sub sSetBreakPoints()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim rstScores As DAO.Recordset
Dim lCount As Long
Dim dMinBreak As Double
Dim dMaxBreak As Double
Dim I As Integer

strSQL = "SELECT [IntegerField] FROM [IntegerTable]" & _
" WHERE [IntegerField] is not null ORDER BY [IntegerField] DESC"

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset(strSQL)

strSQL = "DELETE FROM ScoresTable"
dbAny.Execute strSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
Set rstScores = dbAny.OpenRecordset(strSQL)
rstAny.MoveLast
lCount = rstAny.RecordCount \ 5

rstAny.MoveFirst

For I = 5 To 1 Step -1

dMaxBreak = rstAny!IntegerField
rstAny.Move lCount

If rstAny.EOF Then rstAny.MovePrevious

dMinBreak = rstAny!IntegerField

With rstScores
.AddNew
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

End With
Next I
End Sub



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

That's excatly what I'm looking for... Now it's a matter of getting your code
to work. I know you've already invested much time, so maybe someone else
could give me a hand.

I added my fields to the code and tried to run it from a command button. I
then tried to run it after creating a table called ScoresTable; I added the
fields Maxbreak, Minbreak, and TopX.

When I run the code I get an error that says, "Member already exists in an
object module from which this object module derives."

I'm not very good with Visual Basic; any help would be appreciated. In the
meantime, I'll keep playing with it - hopefully I'll be able to make it work.

Thx!

So you want to classify the customers on the percentile they fall into
based on how many dollars are invoiced this year.

That is top 20 percent get 5 stars
The next 20 percent get 4 stars
the next 20 percent get 3 stars
the next 20 percent get 2 stars
Bottom 20 percent get 1 star
And anyone that has zero dollars gets no stars.

Is that the logic you want to apply?

If so, I would write a short sub to populate a little table with the
break points.

UNTESTED off the top of my head AIR CODE. If it errors, you can try to
fix it or ignore it and create your own


Public Sub sSetBreakPoints()
Dim strSQL as string
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim rstScores as DAO.Recordset
Dim lCount as long
Dim dMinBreak as Double
Dim dMaxBreak as Double
Dim I as Integer

strSQL = "SELECT [Invoiced] FROM YourTable" & _
" WHERE [Invoiced] is not null ORDER BY [Invoiced] DESC"

Set dbAny = currentdb()
Set rstAny = dbany.openrecordset (StrSQL)

StrSQL = "DELETE FROM ScoresTable"
dbany.Execute StrSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
set rstScores = dbany.openrecordset (strSQL)
rstany.movelast
LCount = rstany.RecordCount\5

rstany.MoveFirst
For I = 5 to 1, step -1

DMaxBreak = RstanyIInvoiced
rstAny.move LCount
DMinBreak = rstany!Invoiced
with rstScores
.Add
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

end With
Next I
End Sub

Now you can use the scores table in a non-equi join to get the number of
stars.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


JK wrote:
What you're saying makes sense, I just don't know how effective it would be
in the long run.

If I had six customers:

Cust1 = $100
Cust2 = $150
Cust3 = $200
Cust4 = $250
Cust5 = $300
Cust6 = $350

And I setup the break points as followed:

1 = $50
2 = $150
3 = $250
4 = $350
5 = $450

I would be all set.
Except..................

Next week, the values in the in the "Invoiced this year" field will be much
higher than they are today - throwing the whole thing off.

I could pick figures that would be based on the middle of the year but then
the beginning of the year and the end of the year would be off...

Unless I'm totally missing something - I'm not sure how that would work.

That's why I think a calculation based on the entire table to determin the
logic is necessary. I just have not been able to find a way to caculate the
rank each customer should receive.

Am I that far off?

:

The break points have nothing to do with how often the data are updated. A
rating system is a rating system. Just like in school. Grades are based
either on an absolute percentage of the number of correct answers or on a
curve based on th accuracy of the class as a whole.

What I am trying to tell you is you have to define the rules before you can
write the logic to support the rule.s

--
Dave Hargis, Microsoft Access MVP


:

Basically what I'm looking for is ideas regarding the logic. How could I take
8,000 customers based on one dollar amount field and come up with a five star
rating system. Like I said, I can't just pick the break points like you
suggested becuase the fields are updated nightly (the fields would increment
anyway even if the database wasn't linked to J.D. Edwards.) It must be a
calculation using every "total invoiced this year" field in my database. Some
way of classifying each customer using 1,2,3,4 or 5 using the "total invoiced
this year" field. Displaying the stars on the form would be easy at that
point.

I don't know... I just thought it would be a good thing for anyone to have.
I can see this being useful in many different applications. I was surprised I
couldn't find any ideas anywhere online...

Thx again for your help...

:

Well, I can't figure out the logic if I don't know the rules, but assuming
your form is a form view form, you can populate the text box with the start
by putting your logic in the Form Current event, or you might put the logic
in the form's record sourec query and display a number of starts based on the
results of the logic.
--
Dave Hargis, Microsoft Access MVP


:

Well first of all, I'd like to present the results by displaying stars on the
main company form. If I had a text box populated with a 1, 2, 3, 4, or 5 - I
could then make a star image appear depending on the value in the text box.

As for the logic behind this concept - that's where I'm stuck. I can't
figure out excatly how to do this.

The field I'm using is updated daily. I can't pick specific break points for
that reason. I must do a calculation based all all of the customers as a
whole and then have each customer meet a certain criteria to receive a
certain amount of stars.

I'm way over my head right now; I admit it.

I was just wondering if someone had any thoughts regarding how to go about
accomplishing such an idea.

Hope that makes more sense and thx for your reply.

The field is a dollar amount. It's called Invoiced This year and it's simply
a total dollar amount for each each customer year to date.

Thx again...

:

Your statement is too vague.
You need first to establish the rules.
Is it based on a customer's percentage of total company invoices?
Is based on specific dollar amount?
Where do the brackets break? For example if you were using a specific
dollar amount, you would need define the 5 brackets

1 star = $0 - $500
2 star = $501 - $1000
3 star = $1001 - $2000
4 star = $2001 - $5000
5 star = > $5000

Also, you did not say where you want to present the results of the rating.
That makes a difference in how you do it.
--
Dave Hargis, Microsoft Access MVP


:

I have a database using Access 2003 with over 8,000 customers. Each Customer
record has a field called, "Invoiced This Year." This field is updated
nightly through an ODBC driver to our main company database (J.D. Edwards.)

I want to create a rating system based on that field. I have no idea how to
do this, and I have not been able to find any help online, but this is what
I'd like it to do.

When the customer is pulled in my database, I'd like there to be a five star
rating system. The higher the "Invoiced this year" field, the more stars the
customer should receive.

Any ideas how I would do this? Thx-

 

[JK]

I just realized, out of the 8,000+ customers in my database, 41 of them have
a negative amount in the "Invoiced This Year" field. This is the currency
field we're running your function against.

I dont' know why this is happening; I've asked our J.D. Edwards
administrator to look into it. As far as I know, a customer should not have a
negative amount in that field. Because of the 41 customers (some with over
100K negative) it's throwing your function off.

Hopefully I can get this fixed - do an update and then I'll be good to go.

Thank again, I really appreciate all of the help you guys have provided.

Ok. I checked the code today. Here is a rewrite that should work. You will
need to replace table and field names as appropriate.

Assumes you have a ScoresTable with three field
TopX - Number, Integer
MaxBreak - Number, Dbl
MinBreak - Number, Dbl

Public Sub sSetBreakPoints()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim rstScores As DAO.Recordset
Dim lCount As Long
Dim dMinBreak As Double
Dim dMaxBreak As Double
Dim I As Integer

strSQL = "SELECT [IntegerField] FROM [IntegerTable]" & _
" WHERE [IntegerField] is not null ORDER BY [IntegerField] DESC"

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset(strSQL)

strSQL = "DELETE FROM ScoresTable"
dbAny.Execute strSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
Set rstScores = dbAny.OpenRecordset(strSQL)
rstAny.MoveLast
lCount = rstAny.RecordCount \ 5

rstAny.MoveFirst

For I = 5 To 1 Step -1

dMaxBreak = rstAny!IntegerField
rstAny.Move lCount

If rstAny.EOF Then rstAny.MovePrevious

dMinBreak = rstAny!IntegerField

With rstScores
.AddNew
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

End With
Next I
End Sub



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

That's excatly what I'm looking for... Now it's a matter of getting your code
to work. I know you've already invested much time, so maybe someone else
could give me a hand.

I added my fields to the code and tried to run it from a command button. I
then tried to run it after creating a table called ScoresTable; I added the
fields Maxbreak, Minbreak, and TopX.

When I run the code I get an error that says, "Member already exists in an
object module from which this object module derives."

I'm not very good with Visual Basic; any help would be appreciated. In the
meantime, I'll keep playing with it - hopefully I'll be able to make it work.

Thx!

So you want to classify the customers on the percentile they fall into
based on how many dollars are invoiced this year.

That is top 20 percent get 5 stars
The next 20 percent get 4 stars
the next 20 percent get 3 stars
the next 20 percent get 2 stars
Bottom 20 percent get 1 star
And anyone that has zero dollars gets no stars.

Is that the logic you want to apply?

If so, I would write a short sub to populate a little table with the
break points.

UNTESTED off the top of my head AIR CODE. If it errors, you can try to
fix it or ignore it and create your own


Public Sub sSetBreakPoints()
Dim strSQL as string
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim rstScores as DAO.Recordset
Dim lCount as long
Dim dMinBreak as Double
Dim dMaxBreak as Double
Dim I as Integer

strSQL = "SELECT [Invoiced] FROM YourTable" & _
" WHERE [Invoiced] is not null ORDER BY [Invoiced] DESC"

Set dbAny = currentdb()
Set rstAny = dbany.openrecordset (StrSQL)

StrSQL = "DELETE FROM ScoresTable"
dbany.Execute StrSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
set rstScores = dbany.openrecordset (strSQL)
rstany.movelast
LCount = rstany.RecordCount\5

rstany.MoveFirst
For I = 5 to 1, step -1

DMaxBreak = RstanyIInvoiced
rstAny.move LCount
DMinBreak = rstany!Invoiced
with rstScores
.Add
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

end With
Next I
End Sub

Now you can use the scores table in a non-equi join to get the number of
stars.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


JK wrote:
What you're saying makes sense, I just don't know how effective it would be
in the long run.

If I had six customers:

Cust1 = $100
Cust2 = $150
Cust3 = $200
Cust4 = $250
Cust5 = $300
Cust6 = $350

And I setup the break points as followed:

1 = $50
2 = $150
3 = $250
4 = $350
5 = $450

I would be all set.
Except..................

Next week, the values in the in the "Invoiced this year" field will be much
higher than they are today - throwing the whole thing off.

I could pick figures that would be based on the middle of the year but then
the beginning of the year and the end of the year would be off...

Unless I'm totally missing something - I'm not sure how that would work.

That's why I think a calculation based on the entire table to determin the
logic is necessary. I just have not been able to find a way to caculate the
rank each customer should receive.

Am I that far off?

:

The break points have nothing to do with how often the data are updated. A
rating system is a rating system. Just like in school. Grades are based
either on an absolute percentage of the number of correct answers or on a
curve based on th accuracy of the class as a whole.

What I am trying to tell you is you have to define the rules before you can
write the logic to support the rule.s

--
Dave Hargis, Microsoft Access MVP


:

Basically what I'm looking for is ideas regarding the logic. How could I take
8,000 customers based on one dollar amount field and come up with a five star
rating system. Like I said, I can't just pick the break points like you
suggested becuase the fields are updated nightly (the fields would increment
anyway even if the database wasn't linked to J.D. Edwards.) It must be a
calculation using every "total invoiced this year" field in my database. Some
way of classifying each customer using 1,2,3,4 or 5 using the "total invoiced
this year" field. Displaying the stars on the form would be easy at that
point.

I don't know... I just thought it would be a good thing for anyone to have.
I can see this being useful in many different applications. I was surprised I
couldn't find any ideas anywhere online...

Thx again for your help...

:

Well, I can't figure out the logic if I don't know the rules, but assuming
your form is a form view form, you can populate the text box with the start
by putting your logic in the Form Current event, or you might put the logic
in the form's record sourec query and display a number of starts based on the
results of the logic.
--
Dave Hargis, Microsoft Access MVP


:

Well first of all, I'd like to present the results by displaying stars on the
main company form. If I had a text box populated with a 1, 2, 3, 4, or 5 - I
could then make a star image appear depending on the value in the text box.

As for the logic behind this concept - that's where I'm stuck. I can't
figure out excatly how to do this.

The field I'm using is updated daily. I can't pick specific break points for
that reason. I must do a calculation based all all of the customers as a
whole and then have each customer meet a certain criteria to receive a
certain amount of stars.

I'm way over my head right now; I admit it.

I was just wondering if someone had any thoughts regarding how to go about
accomplishing such an idea.

Hope that makes more sense and thx for your reply.

The field is a dollar amount. It's called Invoiced This year and it's simply
a total dollar amount for each each customer year to date.

Thx again...

:

Your statement is too vague.
You need first to establish the rules.
Is it based on a customer's percentage of total company invoices?
Is based on specific dollar amount?
Where do the brackets break? For example if you were using a specific
dollar amount, you would need define the 5 brackets

1 star = $0 - $500
2 star = $501 - $1000
3 star = $1001 - $2000
4 star = $2001 - $5000
5 star = > $5000

Also, you did not say where you want to present the results of the rating.
That makes a difference in how you do it.
--
Dave Hargis, Microsoft Access MVP


:

I have a database using Access 2003 with over 8,000 customers. Each Customer
record has a field called, "Invoiced This Year." This field is updated
nightly through an ODBC driver to our main company database (J.D. Edwards.)

I want to create a rating system based on that field. I have no idea how to
do this, and I have not been able to find any help online, but this is what
I'd like it to do.

When the customer is pulled in my database, I'd like there to be a five star
rating system. The higher the "Invoiced this year" field, the more stars the
customer should receive.

Any ideas how I would do this? Thx-

 

[JK]

Final follow-up.....

I changed your code to instead of ignoring null values, to ignore anything
less than or equal to zero. strSQL = "SELECT [InvThisYr] FROM
[tblCompanies]" & _
" WHERE (([InvThisYr]) >0) ORDER BY [InvThisYr] DESC"

I was told the negatives are due to rebates being issued to an account
number (chain account) that is not the usual bill-to account.

Thx again!!!!

Ok. I checked the code today. Here is a rewrite that should work. You will
need to replace table and field names as appropriate.

Assumes you have a ScoresTable with three field
TopX - Number, Integer
MaxBreak - Number, Dbl
MinBreak - Number, Dbl

Public Sub sSetBreakPoints()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim rstScores As DAO.Recordset
Dim lCount As Long
Dim dMinBreak As Double
Dim dMaxBreak As Double
Dim I As Integer

strSQL = "SELECT [IntegerField] FROM [IntegerTable]" & _
" WHERE [IntegerField] is not null ORDER BY [IntegerField] DESC"

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset(strSQL)

strSQL = "DELETE FROM ScoresTable"
dbAny.Execute strSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
Set rstScores = dbAny.OpenRecordset(strSQL)
rstAny.MoveLast
lCount = rstAny.RecordCount \ 5

rstAny.MoveFirst

For I = 5 To 1 Step -1

dMaxBreak = rstAny!IntegerField
rstAny.Move lCount

If rstAny.EOF Then rstAny.MovePrevious

dMinBreak = rstAny!IntegerField

With rstScores
.AddNew
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

End With
Next I
End Sub



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

That's excatly what I'm looking for... Now it's a matter of getting your code
to work. I know you've already invested much time, so maybe someone else
could give me a hand.

I added my fields to the code and tried to run it from a command button. I
then tried to run it after creating a table called ScoresTable; I added the
fields Maxbreak, Minbreak, and TopX.

When I run the code I get an error that says, "Member already exists in an
object module from which this object module derives."

I'm not very good with Visual Basic; any help would be appreciated. In the
meantime, I'll keep playing with it - hopefully I'll be able to make it work.

Thx!

So you want to classify the customers on the percentile they fall into
based on how many dollars are invoiced this year.

That is top 20 percent get 5 stars
The next 20 percent get 4 stars
the next 20 percent get 3 stars
the next 20 percent get 2 stars
Bottom 20 percent get 1 star
And anyone that has zero dollars gets no stars.

Is that the logic you want to apply?

If so, I would write a short sub to populate a little table with the
break points.

UNTESTED off the top of my head AIR CODE. If it errors, you can try to
fix it or ignore it and create your own


Public Sub sSetBreakPoints()
Dim strSQL as string
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim rstScores as DAO.Recordset
Dim lCount as long
Dim dMinBreak as Double
Dim dMaxBreak as Double
Dim I as Integer

strSQL = "SELECT [Invoiced] FROM YourTable" & _
" WHERE [Invoiced] is not null ORDER BY [Invoiced] DESC"

Set dbAny = currentdb()
Set rstAny = dbany.openrecordset (StrSQL)

StrSQL = "DELETE FROM ScoresTable"
dbany.Execute StrSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
set rstScores = dbany.openrecordset (strSQL)
rstany.movelast
LCount = rstany.RecordCount\5

rstany.MoveFirst
For I = 5 to 1, step -1

DMaxBreak = RstanyIInvoiced
rstAny.move LCount
DMinBreak = rstany!Invoiced
with rstScores
.Add
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

end With
Next I
End Sub

Now you can use the scores table in a non-equi join to get the number of
stars.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


JK wrote:
What you're saying makes sense, I just don't know how effective it would be
in the long run.

If I had six customers:

Cust1 = $100
Cust2 = $150
Cust3 = $200
Cust4 = $250
Cust5 = $300
Cust6 = $350

And I setup the break points as followed:

1 = $50
2 = $150
3 = $250
4 = $350
5 = $450

I would be all set.
Except..................

Next week, the values in the in the "Invoiced this year" field will be much
higher than they are today - throwing the whole thing off.

I could pick figures that would be based on the middle of the year but then
the beginning of the year and the end of the year would be off...

Unless I'm totally missing something - I'm not sure how that would work.

That's why I think a calculation based on the entire table to determin the
logic is necessary. I just have not been able to find a way to caculate the
rank each customer should receive.

Am I that far off?

:

The break points have nothing to do with how often the data are updated. A
rating system is a rating system. Just like in school. Grades are based
either on an absolute percentage of the number of correct answers or on a
curve based on th accuracy of the class as a whole.

What I am trying to tell you is you have to define the rules before you can
write the logic to support the rule.s

--
Dave Hargis, Microsoft Access MVP


:

Basically what I'm looking for is ideas regarding the logic. How could I take
8,000 customers based on one dollar amount field and come up with a five star
rating system. Like I said, I can't just pick the break points like you
suggested becuase the fields are updated nightly (the fields would increment
anyway even if the database wasn't linked to J.D. Edwards.) It must be a
calculation using every "total invoiced this year" field in my database. Some
way of classifying each customer using 1,2,3,4 or 5 using the "total invoiced
this year" field. Displaying the stars on the form would be easy at that
point.

I don't know... I just thought it would be a good thing for anyone to have.
I can see this being useful in many different applications. I was surprised I
couldn't find any ideas anywhere online...

Thx again for your help...

:

Well, I can't figure out the logic if I don't know the rules, but assuming
your form is a form view form, you can populate the text box with the start
by putting your logic in the Form Current event, or you might put the logic
in the form's record sourec query and display a number of starts based on the
results of the logic.
--
Dave Hargis, Microsoft Access MVP


:

Well first of all, I'd like to present the results by displaying stars on the
main company form. If I had a text box populated with a 1, 2, 3, 4, or 5 - I
could then make a star image appear depending on the value in the text box.

As for the logic behind this concept - that's where I'm stuck. I can't
figure out excatly how to do this.

The field I'm using is updated daily. I can't pick specific break points for
that reason. I must do a calculation based all all of the customers as a
whole and then have each customer meet a certain criteria to receive a
certain amount of stars.

I'm way over my head right now; I admit it.

I was just wondering if someone had any thoughts regarding how to go about
accomplishing such an idea.

Hope that makes more sense and thx for your reply.

The field is a dollar amount. It's called Invoiced This year and it's simply
a total dollar amount for each each customer year to date.

Thx again...

:

Your statement is too vague.
You need first to establish the rules.
Is it based on a customer's percentage of total company invoices?
Is based on specific dollar amount?
Where do the brackets break? For example if you were using a specific
dollar amount, you would need define the 5 brackets

1 star = $0 - $500
2 star = $501 - $1000
3 star = $1001 - $2000
4 star = $2001 - $5000
5 star = > $5000

Also, you did not say where you want to present the results of the rating.
That makes a difference in how you do it.
--
Dave Hargis, Microsoft Access MVP


:

I have a database using Access 2003 with over 8,000 customers. Each Customer
record has a field called, "Invoiced This Year." This field is updated
nightly through an ODBC driver to our main company database (J.D. Edwards.)

I want to create a rating system based on that field. I have no idea how to
do this, and I have not been able to find any help online, but this is what
I'd like it to do.

When the customer is pulled in my database, I'd like there to be a five star
rating system. The higher the "Invoiced this year" field, the more stars the
customer should receive.

Any ideas how I would do this? Thx-

 

[JK]

I just wanted to quickly post the final steps needed to add a five star
rating system to your application. After implimenting the function created by
John Spencer, add a hidden text box to your main form called "score" and make
the control source =DLookUp("TopX","ScoresTable",[InvThisYr] & " >= MinBreak")

This will display a 1, 2, 3, 4, or a 5 in the hidden text box. Now that you
have the individual customer score, you can either display or hide (visible =
false) the stars (images) on your customer form.

John Spencer & Klatuu ===> I can't thank you enough for the assistance you
provided. My company will now easily be able to treat our top customers with
the special care they deserve.

JK

Final follow-up.....

I changed your code to instead of ignoring null values, to ignore anything
less than or equal to zero. strSQL = "SELECT [InvThisYr] FROM
[tblCompanies]" & _
" WHERE (([InvThisYr]) >0) ORDER BY [InvThisYr] DESC"

I was told the negatives are due to rebates being issued to an account
number (chain account) that is not the usual bill-to account.

Thx again!!!!

Ok. I checked the code today. Here is a rewrite that should work. You will
need to replace table and field names as appropriate.

Assumes you have a ScoresTable with three field
TopX - Number, Integer
MaxBreak - Number, Dbl
MinBreak - Number, Dbl

Public Sub sSetBreakPoints()
Dim strSQL As String
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim rstScores As DAO.Recordset
Dim lCount As Long
Dim dMinBreak As Double
Dim dMaxBreak As Double
Dim I As Integer

strSQL = "SELECT [IntegerField] FROM [IntegerTable]" & _
" WHERE [IntegerField] is not null ORDER BY [IntegerField] DESC"

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset(strSQL)

strSQL = "DELETE FROM ScoresTable"
dbAny.Execute strSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
Set rstScores = dbAny.OpenRecordset(strSQL)
rstAny.MoveLast
lCount = rstAny.RecordCount \ 5

rstAny.MoveFirst

For I = 5 To 1 Step -1

dMaxBreak = rstAny!IntegerField
rstAny.Move lCount

If rstAny.EOF Then rstAny.MovePrevious

dMinBreak = rstAny!IntegerField

With rstScores
.AddNew
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

End With
Next I
End Sub



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

That's excatly what I'm looking for... Now it's a matter of getting your code
to work. I know you've already invested much time, so maybe someone else
could give me a hand.

I added my fields to the code and tried to run it from a command button. I
then tried to run it after creating a table called ScoresTable; I added the
fields Maxbreak, Minbreak, and TopX.

When I run the code I get an error that says, "Member already exists in an
object module from which this object module derives."

I'm not very good with Visual Basic; any help would be appreciated. In the
meantime, I'll keep playing with it - hopefully I'll be able to make it work.

Thx!

:

So you want to classify the customers on the percentile they fall into
based on how many dollars are invoiced this year.

That is top 20 percent get 5 stars
The next 20 percent get 4 stars
the next 20 percent get 3 stars
the next 20 percent get 2 stars
Bottom 20 percent get 1 star
And anyone that has zero dollars gets no stars.

Is that the logic you want to apply?

If so, I would write a short sub to populate a little table with the
break points.

UNTESTED off the top of my head AIR CODE. If it errors, you can try to
fix it or ignore it and create your own


Public Sub sSetBreakPoints()
Dim strSQL as string
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim rstScores as DAO.Recordset
Dim lCount as long
Dim dMinBreak as Double
Dim dMaxBreak as Double
Dim I as Integer

strSQL = "SELECT [Invoiced] FROM YourTable" & _
" WHERE [Invoiced] is not null ORDER BY [Invoiced] DESC"

Set dbAny = currentdb()
Set rstAny = dbany.openrecordset (StrSQL)

StrSQL = "DELETE FROM ScoresTable"
dbany.Execute StrSQL

strSQL = "SELECT MaxBreak, MinBreak, TopX FROM ScoresTable"
set rstScores = dbany.openrecordset (strSQL)
rstany.movelast
LCount = rstany.RecordCount\5

rstany.MoveFirst
For I = 5 to 1, step -1

DMaxBreak = RstanyIInvoiced
rstAny.move LCount
DMinBreak = rstany!Invoiced
with rstScores
.Add
.Fields("Maxbreak") = dMaxBreak
.Fields("MinBreak") = dMinBreak
.Fields("TopX") = I
.Update

end With
Next I
End Sub

Now you can use the scores table in a non-equi join to get the number of
stars.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


JK wrote:
What you're saying makes sense, I just don't know how effective it would be
in the long run.

If I had six customers:

Cust1 = $100
Cust2 = $150
Cust3 = $200
Cust4 = $250
Cust5 = $300
Cust6 = $350

And I setup the break points as followed:

1 = $50
2 = $150
3 = $250
4 = $350
5 = $450

I would be all set.
Except..................

Next week, the values in the in the "Invoiced this year" field will be much
higher than they are today - throwing the whole thing off.

I could pick figures that would be based on the middle of the year but then
the beginning of the year and the end of the year would be off...

Unless I'm totally missing something - I'm not sure how that would work.

That's why I think a calculation based on the entire table to determin the
logic is necessary. I just have not been able to find a way to caculate the
rank each customer should receive.

Am I that far off?

:

The break points have nothing to do with how often the data are updated. A
rating system is a rating system. Just like in school. Grades are based
either on an absolute percentage of the number of correct answers or on a
curve based on th accuracy of the class as a whole.

What I am trying to tell you is you have to define the rules before you can
write the logic to support the rule.s

--
Dave Hargis, Microsoft Access MVP


:

Basically what I'm looking for is ideas regarding the logic. How could I take
8,000 customers based on one dollar amount field and come up with a five star
rating system. Like I said, I can't just pick the break points like you
suggested becuase the fields are updated nightly (the fields would increment
anyway even if the database wasn't linked to J.D. Edwards.) It must be a
calculation using every "total invoiced this year" field in my database. Some
way of classifying each customer using 1,2,3,4 or 5 using the "total invoiced
this year" field. Displaying the stars on the form would be easy at that
point.

I don't know... I just thought it would be a good thing for anyone to have.
I can see this being useful in many different applications. I was surprised I
couldn't find any ideas anywhere online...

Thx again for your help...

:

Well, I can't figure out the logic if I don't know the rules, but assuming
your form is a form view form, you can populate the text box with the start
by putting your logic in the Form Current event, or you might put the logic
in the form's record sourec query and display a number of starts based on the
results of the logic.
--
Dave Hargis, Microsoft Access MVP


:

Well first of all, I'd like to present the results by displaying stars on the
main company form. If I had a text box populated with a 1, 2, 3, 4, or 5 - I
could then make a star image appear depending on the value in the text box.

As for the logic behind this concept - that's where I'm stuck. I can't
figure out excatly how to do this.

The field I'm using is updated daily. I can't pick specific break points for
that reason. I must do a calculation based all all of the customers as a
whole and then have each customer meet a certain criteria to receive a
certain amount of stars.

I'm way over my head right now; I admit it.

I was just wondering if someone had any thoughts regarding how to go about
accomplishing such an idea.

Hope that makes more sense and thx for your reply.

The field is a dollar amount. It's called Invoiced This year and it's simply
a total dollar amount for each each customer year to date.

Thx again...

:

Your statement is too vague.
You need first to establish the rules.
Is it based on a customer's percentage of total company invoices?
Is based on specific dollar amount?
Where do the brackets break? For example if you were using a specific
dollar amount, you would need define the 5 brackets

1 star = $0 - $500
2 star = $501 - $1000
3 star = $1001 - $2000
4 star = $2001 - $5000
5 star = > $5000

Also, you did not say where you want to present the results of the rating.
That makes a difference in how you do it.
--
Dave Hargis, Microsoft Access MVP


:

I have a database using Access 2003 with over 8,000 customers. Each Customer
record has a field called, "Invoiced This Year." This field is updated
nightly through an ODBC driver to our main company database (J.D. Edwards.)