I need some macro help

[Guest]

I have 2 tables

1 has all customer data
1 has ranking system I created that rates them 1-5

I import new customers everyday and cant keep up with typing in the rating

I need to create a macro that will take the rankings and put them into the
customer data field

 

[Steve Schapel]

Steveo,

How can the database "know" which ranking to apply to a newly imported
customer?

Maybe if you could show us some example data, in the two tables, and
explain some more details of what you want to do...

 

[Guest]

in the tables category I have

clients
client ranking codes
ID RS BD
1 >0 and <1
2 >=1 and <3
3 >=3 and <8
4 >=8 and <15
5 >15

I created a new category in the clients field called clientranking

So what I am after, I have a huge client database and now all clients need a
client ranking number based on the number of visits they have per year RSBD

I need to come up with a macro that will automate that process for present
and future, preferably each day i import new clients from offices, i would
run that macro and it would assign them a ranking 1-5

 

[Steve Schapel]

Steveo,

So, when the client data is imported "from offices", this data includes
the number of visits?

 

[Guest]

yes. #of visits included, just needing to assign a category id 1-5 with the
values rsbd

 

[Steve Schapel]

Steveo,

This is not a job for a macro. This is a job for a query.

I have not been able to understand what you have written so far, apart
from enough to get the genertal concept. But you have not explained the
structure of your tables.

You need a table that shows the rankings. You need 3 fields:
Rank
StartVisits
EndVisits

So the data in this table will look something like this

1 0 1
2 1 3
3 3 8
4 8 15
5 15 999

Let's say this table is named Rankings

Ok, make a query, and add this Rankings table as well as the Clients table.

Add whichever fields you like from the clients table, to the query
design grid, and also the Rank field from the Rankings table.

Then, in the criteria of the Visits (or whatever it's called - is this
rsbd?) column, put like this...

=[StartVisits] And <[EndVisits]

Hope that makes sense.

Run the query, and you will see each Client has the corect Rank assigned.

 

[Guest]

ok, here are the tables, i am having trouble getting the query right

Clients Table

ltrop bcf visits ranking fb
7 17.5 20 2683
4 8 3.1 2845
3 7 6 2742
6 14.7 7.9 2854
5 11.5 4.5 2605
2 3.25 3 2826
1 0 2.8 2829
2 1 1.7 2820
7 16 24 2295
4 8.5 15.2 2144
6 12 11.1 2608
3 6.5 5.4 2828
8 19 15.9 2304
1 0 2.3 2823
5 8.75 4.4 2778


ranking table

ID VLow VHigh
1 0 1
2 1 3
3 3 8
4 8 15
5 15 999

So I did the query but got a type mismatch, i dont know how i should setup
the query

Steveo,

This is not a job for a macro. This is a job for a query.

I have not been able to understand what you have written so far, apart
from enough to get the genertal concept. But you have not explained the
structure of your tables.

You need a table that shows the rankings. You need 3 fields:
Rank
StartVisits
EndVisits

So the data in this table will look something like this

1 0 1
2 1 3
3 3 8
4 8 15
5 15 999

Let's say this table is named Rankings

Ok, make a query, and add this Rankings table as well as the Clients table.

Add whichever fields you like from the clients table, to the query
design grid, and also the Rank field from the Rankings table.

Then, in the criteria of the Visits (or whatever it's called - is this
rsbd?) column, put like this...

=[StartVisits] And <[EndVisits]

Hope that makes sense.

Run the query, and you will see each Client has the corect Rank assigned.

--
Steve Schapel, Microsoft Access MVP

yes. #of visits included, just needing to assign a category id 1-5 with the
values rsbd

 

[Steve Schapel]

Steveo,

Can you confirm that it is the Visits field in the Clients table that
should be the basis of assigning the Ranks?

If so, what is the Data Type of this field? Is it a number?

And in the Ranking table, what is the Data Type of the VLow and VHigh
fields?

Can you post back with the SQL view of the query as you have it so far?
In design view of the query, select SQL from the View menu.

Please remember that other people do not have knowledgs of your business
processes. As such, "ltrop" and "bcf" etc do not convey any meaning.

Is the 4th field (fb?) the field that identifies the Client?

 

[Guest]

I renamed the ranking table the way you suggested

Ranking Table

Rank
StartVisits
EndVisits

1 0 1
2 1 3
3 3 8
4 8 15
5 15 999

Clients Table

Avg Rank Bcode Bcode
3.1 0 2845 0
6 0 2742 -4
7.9 0 2854 0
4.5 0 2605 -9
3 0 2826 -1
2.8 0 2829 -1
1.7 0 2820 0
24 0 2295 -20
15.2 0 2144 -25
11.1 0 2608 -8
5.4 0 2828 0
15.9 0 2304 -19
2.3 0 2823 0

So the avg is the average visits per year/patient
rank is going to be the 1-5 [ranking table]
bcode is internal billing code

So. I am trying to create an automated way where it will take my rankings
critera
and fill in the rank in the clients table, hope this clarifys more than
before.

 

[Steve Schapel]

Steveo,

Ok, thanks for the additional explanation.

You have 2 Bcode fields shown, I assume this is a typo.

You need a field that identifies the Client. I assume this is the 3rd
column in the data you posted, the one with values such as 2845, 2742,
etc. For the purposes of my example below, I have assumed this is named
FB, so if not, you will need to change to whatever it is really named.

First point is, there should not be a Rank field in the Clients table.
This is a derived/calculated value, and is returned via your query.

So, on the basis of what you have now, the SQL of the query should look
like this:

SELECT Clients.Avg, Clients.FB, Clients.Bcode, Ranking.Rank
FROM Clients, Ranking
WHERE Clients.Avg>=Ranking.StartVisits AND Clients.Avg<Ranking.EndVisits;

--
Steve Schapel, Microsoft Access MVP

I renamed the ranking table the way you suggested

Ranking Table

Rank
StartVisits
EndVisits

1 0 1
2 1 3
3 3 8
4 8 15
5 15 999

Clients Table

Avg Rank Bcode Bcode
3.1 0 2845 0
6 0 2742 -4
7.9 0 2854 0
4.5 0 2605 -9
3 0 2826 -1
2.8 0 2829 -1
1.7 0 2820 0
24 0 2295 -20
15.2 0 2144 -25
11.1 0 2608 -8
5.4 0 2828 0
15.9 0 2304 -19
2.3 0 2823 0

So the avg is the average visits per year/patient
rank is going to be the 1-5 [ranking table]
bcode is internal billing code

So. I am trying to create an automated way where it will take my rankings
critera
and fill in the rank in the clients table, hope this clarifys more than
before.

Steveo,

Can you confirm that it is the Visits field in the Clients table that
should be the basis of assigning the Ranks?

If so, what is the Data Type of this field? Is it a number?

And in the Ranking table, what is the Data Type of the VLow and VHigh
fields?

Can you post back with the SQL view of the query as you have it so far?
In design view of the query, select SQL from the View menu.

Please remember that other people do not have knowledgs of your business
processes. As such, "ltrop" and "bcf" etc do not convey any meaning.

Is the 4th field (fb?) the field that identifies the Client?

 

[Guest]

almost there, having problem with the WHERE. GETTING DATA TYPE MISMATCH

WHERE Clients.Avg>=Ranking.StartVisits AND Clients.Avg<Ranking.EndVisits;

Steveo,

Ok, thanks for the additional explanation.

You have 2 Bcode fields shown, I assume this is a typo.

You need a field that identifies the Client. I assume this is the 3rd
column in the data you posted, the one with values such as 2845, 2742,
etc. For the purposes of my example below, I have assumed this is named
FB, so if not, you will need to change to whatever it is really named.

First point is, there should not be a Rank field in the Clients table.
This is a derived/calculated value, and is returned via your query.

So, on the basis of what you have now, the SQL of the query should look
like this:

SELECT Clients.Avg, Clients.FB, Clients.Bcode, Ranking.Rank
FROM Clients, Ranking
WHERE Clients.Avg>=Ranking.StartVisits AND Clients.Avg<Ranking.EndVisits;

--
Steve Schapel, Microsoft Access MVP

I renamed the ranking table the way you suggested

Ranking Table

Rank
StartVisits
EndVisits

1 0 1
2 1 3
3 3 8
4 8 15
5 15 999

Clients Table

Avg Rank Bcode Bcode
3.1 0 2845 0
6 0 2742 -4
7.9 0 2854 0
4.5 0 2605 -9
3 0 2826 -1
2.8 0 2829 -1
1.7 0 2820 0
24 0 2295 -20
15.2 0 2144 -25
11.1 0 2608 -8
5.4 0 2828 0
15.9 0 2304 -19
2.3 0 2823 0

So the avg is the average visits per year/patient
rank is going to be the 1-5 [ranking table]
bcode is internal billing code

So. I am trying to create an automated way where it will take my rankings
critera
and fill in the rank in the clients table, hope this clarifys more than
before.

Steveo,

Can you confirm that it is the Visits field in the Clients table that
should be the basis of assigning the Ranks?

If so, what is the Data Type of this field? Is it a number?

And in the Ranking table, what is the Data Type of the VLow and VHigh
fields?

Can you post back with the SQL view of the query as you have it so far?
In design view of the query, select SQL from the View menu.

Please remember that other people do not have knowledgs of your business
processes. As such, "ltrop" and "bcf" etc do not convey any meaning.

Is the 4th field (fb?) the field that identifies the Client?

--
Steve Schapel, Microsoft Access MVP

Steveo wrote:
ok, here are the tables, i am having trouble getting the query right

Clients Table

ltrop bcf visits ranking fb
7 17.5 20 2683
4 8 3.1 2845
3 7 6 2742
6 14.7 7.9 2854
5 11.5 4.5 2605
2 3.25 3 2826
1 0 2.8 2829
2 1 1.7 2820
7 16 24 2295
4 8.5 15.2 2144
6 12 11.1 2608
3 6.5 5.4 2828
8 19 15.9 2304
1 0 2.3 2823
5 8.75 4.4 2778


ranking table

ID VLow VHigh
1 0 1
2 1 3
3 3 8
4 8 15
5 15 999

So I did the query but got a type mismatch, i dont know how i should setup
the query

 

[Steve Schapel]

Steveo,

What is the Data TYpe of these fields. Look in table design.