Converting text to a number in query

OK, that worked, but there seems to be a filter? of some sort. If you look
at the example, it's still combining duplicate values. I would still like to
see all three lines for each app.

Graham, has 2 records because two entries were G
Roark, has 1 records because all three entries were Y
Kington, is displayed the way I would like all records displayed

Key Last Name Reviewed 1 App Rating 1 Field1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 3
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 4
8 Roark KAH Y 8 PJ Y 8 JMD Y 8 yes 8
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 2
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 7
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 8

--
SS


"KARL DEWEY" wrote:

Place the criteria for the second one on second criteria row. Place criteria
for third on the third row.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

First example with App Rating 1 working. I have 78 total records, this
displayed all of them.

Second example with App Rating 1 and App Rating 2 working. I have 78 total
records, this displayed 29 of them. You can see that it dropped Fridman
since their second record didn't match their first.

Third example with App Rating 1, App Rating 2 and App Rating 3 working. I
have 78 total records, this displayed 9 of them. At this point only the
record that have the same three ratings are displayed.

These copied over to excel and were legible if that helps. Thanks.

FIRST EXAMPLE

Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
70 Fridman KAH G ++ EOM/JMD G/Y 3 PJ G 3 yes 1
4 Wolff KAH G + VC G 3 AN G 3 yes 2
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
40 Kantamneni KAH G + PJ G 3 JMD G 3 yes 2
28 Kestner KAH G + PJ G 3 JMD Y ++ 6 yes 2
9 Graham KAH G + JMD G 3 PJ G 3 yes 2

SECOND EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
13 Brown KAH G MO G 3 JMD Y 8 yes 3
32 Wiede KAH G JMD G 3 AN G ++ 1 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3

THIRD EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
8 Roark KAH Y PJ Y 8 JMD Y 8 yes 8
75 Bolte KAH Y DR Y 8 GTM Y 8 yes 8
17 Balon KAH R JMD R 11 DR R 11 yes 11
69 Eriksson app R not com R 11 plete R 11 yes 11

--
SS


"KARL DEWEY" wrote:

So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.
You lost me here.
Post sample data - maybe 5 records.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Whoo hoo!! I had spaces, so as soon as I added those, it worked. Thanks!!

Next question, earlier in this message I mentioned I had three columns and
you had replied:
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

I had it set up that way because any 3 people may review any 3 documents and
give them a rating. So I have Rating 1 and Reviewed 1, Rating 2 and Reviewed
2, etc. Rating is the G/Y/R and Reviewed is the reviewer's initials.

Now, what you helped me with worked great on the first column and it worked
on the second and third, but it filtered out any ratings that didn't match.
So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.

I still want to be able to see all 78 records for each reviewer. Is this
possible? Hopefully this makes sense...
--
SS


"KARL DEWEY" wrote:

It works for me. Does one or the other have a space between the letter and
math signs by change?

Post the SQL of your query. Open in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Oohh, this is very close. It's replacing numbers for text for just G, Y, and
R, but not the text with the various ++'s and --'s. Any ideas?

Also, you asked if I wanted this to store the info back into my table. I
don't think so as long as I can generate my report with the letters, but
using the numbers to rank them. (I did this last year by exporting/importing
this info into Excel and then back again. I'm hoping I don't have to keep
doing that.)

Thanks for your help, if this works, my life just got easier :~)
--
SS


"KARL DEWEY" wrote:

I have three separate columns that will be populated
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

As I said you have too many to use nested IIFs.
A translation table will work for you. The translation table would look
like this --
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5


Then to use it in design view of your query add the translation table --
Fields: Field1 Field2 Data Value
Table: Main Main Trans Trans
Criteria: Main.Field3

Main.Field3 is the field of your table that comtains the Gxx information.
The Value field will output the numerical of the G++.

I seem to get the impression you want to store this translation back into
your table. Is that correct?

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I didn't understand what you were asking me to do, so I thought I'd clarify a
little and explain the purpose of these fields.

I work with documents that are rated on the following scale: G++, G+, G, G-,
G--, Y++, Y+, Y, Y-, Y--, R (G++ is the best, R is the worst). Three
different people will rate the same document so I have three separate columns
that will be populated with any of the above.

I want to convert the letters to: 1, 2, 3, 4, 5 ,6, 7, 8 ,9, 10, 11 (1 is
the best, 11 is the worst). After which I will average the 3 number values
to rank the documents best to worst. (I have that formula already.)

I managed to get the following formula to almost work:
Field1: IIf([Document 1]="G++",1)
except it won't populate any numbers, everything is blank, even if I change
the number value to 20, it won't populate.

I appreciate any additional help with this. Thanks.
--
SS


"KARL DEWEY" wrote:

You can use nested IIFs but if you list is very long then a translation table
would work better.
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5

Then to use it in design view --
Fields: Field1 Field2 Data Value
Criteria: Field3

Field3 is the field of your table that comtains the Gxx information.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I'm having trouble with a conversion in order to get a formula to work. I
have text that = numbers. Example: G++=1, G+=2, G=3,G-=4,G--=5, etc. I
need a formula to convert G++ to 1 so that it can be totalled. Thanks for
any help.
--
SS

Post your SQL. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

OK, that worked, but there seems to be a filter? of some sort. If you look
at the example, it's still combining duplicate values. I would still like to
see all three lines for each app.

Graham, has 2 records because two entries were G
Roark, has 1 records because all three entries were Y
Kington, is displayed the way I would like all records displayed

Key Last Name Reviewed 1 App Rating 1 Field1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 3
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 4
8 Roark KAH Y 8 PJ Y 8 JMD Y 8 yes 8
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 2
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 7
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 8

--
SS


"KARL DEWEY" wrote:

Place the criteria for the second one on second criteria row. Place criteria
for third on the third row.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

First example with App Rating 1 working. I have 78 total records, this
displayed all of them.

Second example with App Rating 1 and App Rating 2 working. I have 78 total
records, this displayed 29 of them. You can see that it dropped Fridman
since their second record didn't match their first.

Third example with App Rating 1, App Rating 2 and App Rating 3 working. I
have 78 total records, this displayed 9 of them. At this point only the
record that have the same three ratings are displayed.

These copied over to excel and were legible if that helps. Thanks.

FIRST EXAMPLE

Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
70 Fridman KAH G ++ EOM/JMD G/Y 3 PJ G 3 yes 1
4 Wolff KAH G + VC G 3 AN G 3 yes 2
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
40 Kantamneni KAH G + PJ G 3 JMD G 3 yes 2
28 Kestner KAH G + PJ G 3 JMD Y ++ 6 yes 2
9 Graham KAH G + JMD G 3 PJ G 3 yes 2

SECOND EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
13 Brown KAH G MO G 3 JMD Y 8 yes 3
32 Wiede KAH G JMD G 3 AN G ++ 1 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3

THIRD EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
8 Roark KAH Y PJ Y 8 JMD Y 8 yes 8
75 Bolte KAH Y DR Y 8 GTM Y 8 yes 8
17 Balon KAH R JMD R 11 DR R 11 yes 11
69 Eriksson app R not com R 11 plete R 11 yes 11

--
SS


"KARL DEWEY" wrote:

So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.
You lost me here.
Post sample data - maybe 5 records.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Whoo hoo!! I had spaces, so as soon as I added those, it worked. Thanks!!

Next question, earlier in this message I mentioned I had three columns and
you had replied:
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

I had it set up that way because any 3 people may review any 3 documents and
give them a rating. So I have Rating 1 and Reviewed 1, Rating 2 and Reviewed
2, etc. Rating is the G/Y/R and Reviewed is the reviewer's initials.

Now, what you helped me with worked great on the first column and it worked
on the second and third, but it filtered out any ratings that didn't match.
So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.

I still want to be able to see all 78 records for each reviewer. Is this
possible? Hopefully this makes sense...
--
SS


"KARL DEWEY" wrote:

It works for me. Does one or the other have a space between the letter and
math signs by change?

Post the SQL of your query. Open in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Oohh, this is very close. It's replacing numbers for text for just G, Y, and
R, but not the text with the various ++'s and --'s. Any ideas?

Also, you asked if I wanted this to store the info back into my table. I
don't think so as long as I can generate my report with the letters, but
using the numbers to rank them. (I did this last year by exporting/importing
this info into Excel and then back again. I'm hoping I don't have to keep
doing that.)

Thanks for your help, if this works, my life just got easier :~)
--
SS


"KARL DEWEY" wrote:

I have three separate columns that will be populated
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

As I said you have too many to use nested IIFs.
A translation table will work for you. The translation table would look
like this --
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5


Then to use it in design view of your query add the translation table --
Fields: Field1 Field2 Data Value
Table: Main Main Trans Trans
Criteria: Main.Field3

Main.Field3 is the field of your table that comtains the Gxx information.
The Value field will output the numerical of the G++.

I seem to get the impression you want to store this translation back into
your table. Is that correct?

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I didn't understand what you were asking me to do, so I thought I'd clarify a
little and explain the purpose of these fields.

I work with documents that are rated on the following scale: G++, G+, G, G-,
G--, Y++, Y+, Y, Y-, Y--, R (G++ is the best, R is the worst). Three
different people will rate the same document so I have three separate columns
that will be populated with any of the above.

I want to convert the letters to: 1, 2, 3, 4, 5 ,6, 7, 8 ,9, 10, 11 (1 is
the best, 11 is the worst). After which I will average the 3 number values
to rank the documents best to worst. (I have that formula already.)

I managed to get the following formula to almost work:
Field1: IIf([Document 1]="G++",1)
except it won't populate any numbers, everything is blank, even if I change
the number value to 20, it won't populate.

I appreciate any additional help with this. Thanks.
--
SS


"KARL DEWEY" wrote:

You can use nested IIFs but if you list is very long then a translation table
would work better.
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5

Then to use it in design view --
Fields: Field1 Field2 Data Value
Criteria: Field3

Field3 is the field of your table that comtains the Gxx information.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I'm having trouble with a conversion in order to get a formula to work. I
have text that = numbers. Example: G++=1, G+=2, G=3,G-=4,G--=5, etc. I
need a formula to convert G++ to 1 so that it can be totalled. Thanks for
any help.
--
SS

Here you go. . .

SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].[Reviewed 1], [Application Ratings].[App Rating 1],
[Application Ratings].Field1, [Application Ratings].[Reviewed 2],
[Application Ratings].[App Rating 2], [Application Ratings].Field2,
[Application Ratings].[Reviewed 3], [Application Ratings].[App Rating 3],
[Application Ratings].Field3, [Application Ratings].[Rating Complete],
Translation.[Value 1]
FROM [Application Ratings], [Translation]
WHERE (((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2]) Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 3])));

--
SS


"KARL DEWEY" wrote:

Post your SQL. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

OK, that worked, but there seems to be a filter? of some sort. If you look
at the example, it's still combining duplicate values. I would still like to
see all three lines for each app.

Graham, has 2 records because two entries were G
Roark, has 1 records because all three entries were Y
Kington, is displayed the way I would like all records displayed

Key Last Name Reviewed 1 App Rating 1 Field1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 3
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 4
8 Roark KAH Y 8 PJ Y 8 JMD Y 8 yes 8
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 2
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 7
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 8

--
SS


"KARL DEWEY" wrote:

Place the criteria for the second one on second criteria row. Place criteria
for third on the third row.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

First example with App Rating 1 working. I have 78 total records, this
displayed all of them.

Second example with App Rating 1 and App Rating 2 working. I have 78 total
records, this displayed 29 of them. You can see that it dropped Fridman
since their second record didn't match their first.

Third example with App Rating 1, App Rating 2 and App Rating 3 working. I
have 78 total records, this displayed 9 of them. At this point only the
record that have the same three ratings are displayed.

These copied over to excel and were legible if that helps. Thanks.

FIRST EXAMPLE

Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
70 Fridman KAH G ++ EOM/JMD G/Y 3 PJ G 3 yes 1
4 Wolff KAH G + VC G 3 AN G 3 yes 2
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
40 Kantamneni KAH G + PJ G 3 JMD G 3 yes 2
28 Kestner KAH G + PJ G 3 JMD Y ++ 6 yes 2
9 Graham KAH G + JMD G 3 PJ G 3 yes 2

SECOND EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
13 Brown KAH G MO G 3 JMD Y 8 yes 3
32 Wiede KAH G JMD G 3 AN G ++ 1 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3

THIRD EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
8 Roark KAH Y PJ Y 8 JMD Y 8 yes 8
75 Bolte KAH Y DR Y 8 GTM Y 8 yes 8
17 Balon KAH R JMD R 11 DR R 11 yes 11
69 Eriksson app R not com R 11 plete R 11 yes 11

--
SS


"KARL DEWEY" wrote:

So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.
You lost me here.
Post sample data - maybe 5 records.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Whoo hoo!! I had spaces, so as soon as I added those, it worked. Thanks!!

Next question, earlier in this message I mentioned I had three columns and
you had replied:
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

I had it set up that way because any 3 people may review any 3 documents and
give them a rating. So I have Rating 1 and Reviewed 1, Rating 2 and Reviewed
2, etc. Rating is the G/Y/R and Reviewed is the reviewer's initials.

Now, what you helped me with worked great on the first column and it worked
on the second and third, but it filtered out any ratings that didn't match.
So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.

I still want to be able to see all 78 records for each reviewer. Is this
possible? Hopefully this makes sense...
--
SS


"KARL DEWEY" wrote:

It works for me. Does one or the other have a space between the letter and
math signs by change?

Post the SQL of your query. Open in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Oohh, this is very close. It's replacing numbers for text for just G, Y, and
R, but not the text with the various ++'s and --'s. Any ideas?

Also, you asked if I wanted this to store the info back into my table. I
don't think so as long as I can generate my report with the letters, but
using the numbers to rank them. (I did this last year by exporting/importing
this info into Excel and then back again. I'm hoping I don't have to keep
doing that.)

Thanks for your help, if this works, my life just got easier :~)
--
SS


"KARL DEWEY" wrote:

I have three separate columns that will be populated
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

As I said you have too many to use nested IIFs.
A translation table will work for you. The translation table would look
like this --
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5


Then to use it in design view of your query add the translation table --
Fields: Field1 Field2 Data Value
Table: Main Main Trans Trans
Criteria: Main.Field3

Main.Field3 is the field of your table that comtains the Gxx information.
The Value field will output the numerical of the G++.

I seem to get the impression you want to store this translation back into
your table. Is that correct?

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I didn't understand what you were asking me to do, so I thought I'd clarify a
little and explain the purpose of these fields.

I work with documents that are rated on the following scale: G++, G+, G, G-,
G--, Y++, Y+, Y, Y-, Y--, R (G++ is the best, R is the worst). Three
different people will rate the same document so I have three separate columns
that will be populated with any of the above.

I want to convert the letters to: 1, 2, 3, 4, 5 ,6, 7, 8 ,9, 10, 11 (1 is
the best, 11 is the worst). After which I will average the 3 number values
to rank the documents best to worst. (I have that formula already.)

I managed to get the following formula to almost work:
Field1: IIf([Document 1]="G++",1)
except it won't populate any numbers, everything is blank, even if I change
the number value to 20, it won't populate.

I appreciate any additional help with this. Thanks.
--
SS


"KARL DEWEY" wrote:

You can use nested IIFs but if you list is very long then a translation table
would work better.
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5

Then to use it in design view --
Fields: Field1 Field2 Data Value
Criteria: Field3

Field3 is the field of your table that comtains the Gxx information.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I'm having trouble with a conversion in order to get a formula to work. I
have text that = numbers. Example: G++=1, G+=2, G=3,G-=4,G--=5, etc. I
need a formula to convert G++ to 1 so that it can be totalled. Thanks for
any help.
--
SS

I think this will get you there --
SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].[Reviewed 1], Translation.[Value 1] AS Value_1,
[Application Ratings].[Reviewed 2], Translation_1.[Value 1] AS Value_2,
[Application Ratings].[Reviewed 3], Translation_2.[Value 1] AS Value_3,
[Application Ratings].[Rating Complete]
FROM (([Application Ratings] LEFT JOIN [Translation] ON [Application
Ratings].[App Rating 1] = Translation.[Data 1]) LEFT JOIN [Translation] AS
Translation_1 ON [Application Ratings].[App Rating 2] = Translation_1.[Data
1]) LEFT JOIN [Translation] AS Translation_2 ON [Application Ratings].[App
Rating 3] = Translation_2.[Data 1]
WHERE (((Translation.[Data 1])=[Application Ratings]![App Rating 1])) OR
(((Translation_1.[Data 1])=[Application Ratings]![App Rating 2])) OR
(((Translation_2.[Data 1])=[Application Ratings]![App Rating 3]));

It would be easier if you only had one set of review fields as I suggested ---
SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].Reviewed, Translation.[Value 1] AS Value_1,
[Application Ratings].[Rating Complete], [Application Ratings].Review
FROM [Application Ratings], [Translation]
WHERE (((Translation.[Data 1])=[Application Ratings].[App Rating]));


--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Here you go. . .

SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].[Reviewed 1], [Application Ratings].[App Rating 1],
[Application Ratings].Field1, [Application Ratings].[Reviewed 2],
[Application Ratings].[App Rating 2], [Application Ratings].Field2,
[Application Ratings].[Reviewed 3], [Application Ratings].[App Rating 3],
[Application Ratings].Field3, [Application Ratings].[Rating Complete],
Translation.[Value 1]
FROM [Application Ratings], [Translation]
WHERE (((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2]) Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 3])));

--
SS


"KARL DEWEY" wrote:

Post your SQL. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

OK, that worked, but there seems to be a filter? of some sort. If you look
at the example, it's still combining duplicate values. I would still like to
see all three lines for each app.

Graham, has 2 records because two entries were G
Roark, has 1 records because all three entries were Y
Kington, is displayed the way I would like all records displayed

Key Last Name Reviewed 1 App Rating 1 Field1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 3
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 4
8 Roark KAH Y 8 PJ Y 8 JMD Y 8 yes 8
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 2
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 7
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 8

--
SS


"KARL DEWEY" wrote:

Place the criteria for the second one on second criteria row. Place criteria
for third on the third row.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

First example with App Rating 1 working. I have 78 total records, this
displayed all of them.

Second example with App Rating 1 and App Rating 2 working. I have 78 total
records, this displayed 29 of them. You can see that it dropped Fridman
since their second record didn't match their first.

Third example with App Rating 1, App Rating 2 and App Rating 3 working. I
have 78 total records, this displayed 9 of them. At this point only the
record that have the same three ratings are displayed.

These copied over to excel and were legible if that helps. Thanks.

FIRST EXAMPLE

Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
70 Fridman KAH G ++ EOM/JMD G/Y 3 PJ G 3 yes 1
4 Wolff KAH G + VC G 3 AN G 3 yes 2
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
40 Kantamneni KAH G + PJ G 3 JMD G 3 yes 2
28 Kestner KAH G + PJ G 3 JMD Y ++ 6 yes 2
9 Graham KAH G + JMD G 3 PJ G 3 yes 2

SECOND EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
13 Brown KAH G MO G 3 JMD Y 8 yes 3
32 Wiede KAH G JMD G 3 AN G ++ 1 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3

THIRD EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
8 Roark KAH Y PJ Y 8 JMD Y 8 yes 8
75 Bolte KAH Y DR Y 8 GTM Y 8 yes 8
17 Balon KAH R JMD R 11 DR R 11 yes 11
69 Eriksson app R not com R 11 plete R 11 yes 11

--
SS


"KARL DEWEY" wrote:

So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.
You lost me here.
Post sample data - maybe 5 records.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Whoo hoo!! I had spaces, so as soon as I added those, it worked. Thanks!!

Next question, earlier in this message I mentioned I had three columns and
you had replied:
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

I had it set up that way because any 3 people may review any 3 documents and
give them a rating. So I have Rating 1 and Reviewed 1, Rating 2 and Reviewed
2, etc. Rating is the G/Y/R and Reviewed is the reviewer's initials.

Now, what you helped me with worked great on the first column and it worked
on the second and third, but it filtered out any ratings that didn't match.
So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.

I still want to be able to see all 78 records for each reviewer. Is this
possible? Hopefully this makes sense...
--
SS


"KARL DEWEY" wrote:

It works for me. Does one or the other have a space between the letter and
math signs by change?

Post the SQL of your query. Open in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Oohh, this is very close. It's replacing numbers for text for just G, Y, and
R, but not the text with the various ++'s and --'s. Any ideas?

Also, you asked if I wanted this to store the info back into my table. I
don't think so as long as I can generate my report with the letters, but
using the numbers to rank them. (I did this last year by exporting/importing
this info into Excel and then back again. I'm hoping I don't have to keep
doing that.)

Thanks for your help, if this works, my life just got easier :~)
--
SS


"KARL DEWEY" wrote:

I have three separate columns that will be populated
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

As I said you have too many to use nested IIFs.
A translation table will work for you. The translation table would look
like this --
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5


Then to use it in design view of your query add the translation table --
Fields: Field1 Field2 Data Value
Table: Main Main Trans Trans
Criteria: Main.Field3

Main.Field3 is the field of your table that comtains the Gxx information.
The Value field will output the numerical of the G++.

I seem to get the impression you want to store this translation back into
your table. Is that correct?

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I didn't understand what you were asking me to do, so I thought I'd clarify a
little and explain the purpose of these fields.

I work with documents that are rated on the following scale: G++, G+, G, G-,
G--, Y++, Y+, Y, Y-, Y--, R (G++ is the best, R is the worst). Three
different people will rate the same document so I have three separate columns
that will be populated with any of the above.

I want to convert the letters to: 1, 2, 3, 4, 5 ,6, 7, 8 ,9, 10, 11 (1 is
the best, 11 is the worst). After which I will average the 3 number values
to rank the documents best to worst. (I have that formula already.)

I managed to get the following formula to almost work:
Field1: IIf([Document 1]="G++",1)
except it won't populate any numbers, everything is blank, even if I change
the number value to 20, it won't populate.

I appreciate any additional help with this. Thanks.
--
SS


"KARL DEWEY" wrote:

You can use nested IIFs but if you list is very long then a translation table
would work better.
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5

Then to use it in design view --
Fields: Field1 Field2 Data Value
Criteria: Field3

Field3 is the field of your table that comtains the Gxx information.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I'm having trouble with a conversion in order to get a formula to work. I
have text that = numbers. Example: G++=1, G+=2, G=3,G-=4,G--=5, etc. I
need a formula to convert G++ to 1 so that it can be totalled. Thanks for
any help.
--
SS

Good morning Karl, I copied and pasted this over the old SQL and clicked
run. It came back with an error message of:

Syntax error in JOIN operation

Then it highlighted: [Application Ratings]

This was within this portion of the code: FROM (([Application Ratings]
LEFT JOIN [Translation] ON [Application Ratings].[App Rating 1]

You are probably getting tired of me, but I REALLY appreciate your help.
After this, I will be able to take what I've learned and apply it to other
databases, so I just want to thank you for your time.
--
SS


"KARL DEWEY" wrote:

I think this will get you there --
SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].[Reviewed 1], Translation.[Value 1] AS Value_1,
[Application Ratings].[Reviewed 2], Translation_1.[Value 1] AS Value_2,
[Application Ratings].[Reviewed 3], Translation_2.[Value 1] AS Value_3,
[Application Ratings].[Rating Complete]
FROM (([Application Ratings] LEFT JOIN [Translation] ON [Application
Ratings].[App Rating 1] = Translation.[Data 1]) LEFT JOIN [Translation] AS
Translation_1 ON [Application Ratings].[App Rating 2] = Translation_1.[Data
1]) LEFT JOIN [Translation] AS Translation_2 ON [Application Ratings].[App
Rating 3] = Translation_2.[Data 1]
WHERE (((Translation.[Data 1])=[Application Ratings]![App Rating 1])) OR
(((Translation_1.[Data 1])=[Application Ratings]![App Rating 2])) OR
(((Translation_2.[Data 1])=[Application Ratings]![App Rating 3]));

It would be easier if you only had one set of review fields as I suggested ---
SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].Reviewed, Translation.[Value 1] AS Value_1,
[Application Ratings].[Rating Complete], [Application Ratings].Review
FROM [Application Ratings], [Translation]
WHERE (((Translation.[Data 1])=[Application Ratings].[App Rating]));


--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Here you go. . .

SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].[Reviewed 1], [Application Ratings].[App Rating 1],
[Application Ratings].Field1, [Application Ratings].[Reviewed 2],
[Application Ratings].[App Rating 2], [Application Ratings].Field2,
[Application Ratings].[Reviewed 3], [Application Ratings].[App Rating 3],
[Application Ratings].Field3, [Application Ratings].[Rating Complete],
Translation.[Value 1]
FROM [Application Ratings], [Translation]
WHERE (((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2]) Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 3])));

--
SS


"KARL DEWEY" wrote:

Post your SQL. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

OK, that worked, but there seems to be a filter? of some sort. If you look
at the example, it's still combining duplicate values. I would still like to
see all three lines for each app.

Graham, has 2 records because two entries were G
Roark, has 1 records because all three entries were Y
Kington, is displayed the way I would like all records displayed

Key Last Name Reviewed 1 App Rating 1 Field1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 3
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 4
8 Roark KAH Y 8 PJ Y 8 JMD Y 8 yes 8
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 2
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 7
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 8

--
SS


"KARL DEWEY" wrote:

Place the criteria for the second one on second criteria row. Place criteria
for third on the third row.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

First example with App Rating 1 working. I have 78 total records, this
displayed all of them.

Second example with App Rating 1 and App Rating 2 working. I have 78 total
records, this displayed 29 of them. You can see that it dropped Fridman
since their second record didn't match their first.

Third example with App Rating 1, App Rating 2 and App Rating 3 working. I
have 78 total records, this displayed 9 of them. At this point only the
record that have the same three ratings are displayed.

These copied over to excel and were legible if that helps. Thanks.

FIRST EXAMPLE

Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
70 Fridman KAH G ++ EOM/JMD G/Y 3 PJ G 3 yes 1
4 Wolff KAH G + VC G 3 AN G 3 yes 2
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
40 Kantamneni KAH G + PJ G 3 JMD G 3 yes 2
28 Kestner KAH G + PJ G 3 JMD Y ++ 6 yes 2
9 Graham KAH G + JMD G 3 PJ G 3 yes 2

SECOND EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
13 Brown KAH G MO G 3 JMD Y 8 yes 3
32 Wiede KAH G JMD G 3 AN G ++ 1 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3

THIRD EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
8 Roark KAH Y PJ Y 8 JMD Y 8 yes 8
75 Bolte KAH Y DR Y 8 GTM Y 8 yes 8
17 Balon KAH R JMD R 11 DR R 11 yes 11
69 Eriksson app R not com R 11 plete R 11 yes 11

--
SS


"KARL DEWEY" wrote:

So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.
You lost me here.
Post sample data - maybe 5 records.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Whoo hoo!! I had spaces, so as soon as I added those, it worked. Thanks!!

Next question, earlier in this message I mentioned I had three columns and
you had replied:
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

I had it set up that way because any 3 people may review any 3 documents and
give them a rating. So I have Rating 1 and Reviewed 1, Rating 2 and Reviewed
2, etc. Rating is the G/Y/R and Reviewed is the reviewer's initials.

Now, what you helped me with worked great on the first column and it worked
on the second and third, but it filtered out any ratings that didn't match.
So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.

I still want to be able to see all 78 records for each reviewer. Is this
possible? Hopefully this makes sense...
--
SS


"KARL DEWEY" wrote:

It works for me. Does one or the other have a space between the letter and
math signs by change?

Post the SQL of your query. Open in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Oohh, this is very close. It's replacing numbers for text for just G, Y, and
R, but not the text with the various ++'s and --'s. Any ideas?

Also, you asked if I wanted this to store the info back into my table. I
don't think so as long as I can generate my report with the letters, but
using the numbers to rank them. (I did this last year by exporting/importing
this info into Excel and then back again. I'm hoping I don't have to keep
doing that.)

Thanks for your help, if this works, my life just got easier :~)
--
SS


"KARL DEWEY" wrote:

I have three separate columns that will be populated
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

As I said you have too many to use nested IIFs.
A translation table will work for you. The translation table would look
like this --
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5


Then to use it in design view of your query add the translation table --
Fields: Field1 Field2 Data Value
Table: Main Main Trans Trans
Criteria: Main.Field3

Main.Field3 is the field of your table that comtains the Gxx information.
The Value field will output the numerical of the G++.

I seem to get the impression you want to store this translation back into
your table. Is that correct?

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I didn't understand what you were asking me to do, so I thought I'd clarify a
little and explain the purpose of these fields.

I work with documents that are rated on the following scale: G++, G+, G, G-,
G--, Y++, Y+, Y, Y-, Y--, R (G++ is the best, R is the worst). Three
different people will rate the same document so I have three separate columns
that will be populated with any of the above.

I want to convert the letters to: 1, 2, 3, 4, 5 ,6, 7, 8 ,9, 10, 11 (1 is
the best, 11 is the worst). After which I will average the 3 number values
to rank the documents best to worst. (I have that formula already.)

I managed to get the following formula to almost work:
Field1: IIf([Document 1]="G++",1)
except it won't populate any numbers, everything is blank, even if I change
the number value to 20, it won't populate.

I appreciate any additional help with this. Thanks.
--
SS


"KARL DEWEY" wrote:

You can use nested IIFs but if you list is very long then a translation table
would work better.
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5

Then to use it in design view --
Fields: Field1 Field2 Data Value
Criteria: Field3

Field3 is the field of your table that comtains the Gxx information.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

All I can recommend is to check for any hard returns that were caused by the
posting, copying, and pasting.

You would be better off to use the latter query method that use only one set
of fields for review rather than three.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Good morning Karl, I copied and pasted this over the old SQL and clicked
run. It came back with an error message of:

Syntax error in JOIN operation

Then it highlighted: [Application Ratings]

This was within this portion of the code: FROM (([Application Ratings]
LEFT JOIN [Translation] ON [Application Ratings].[App Rating 1]

You are probably getting tired of me, but I REALLY appreciate your help.
After this, I will be able to take what I've learned and apply it to other
databases, so I just want to thank you for your time.
--
SS


"KARL DEWEY" wrote:

I think this will get you there --
SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].[Reviewed 1], Translation.[Value 1] AS Value_1,
[Application Ratings].[Reviewed 2], Translation_1.[Value 1] AS Value_2,
[Application Ratings].[Reviewed 3], Translation_2.[Value 1] AS Value_3,
[Application Ratings].[Rating Complete]
FROM (([Application Ratings] LEFT JOIN [Translation] ON [Application
Ratings].[App Rating 1] = Translation.[Data 1]) LEFT JOIN [Translation] AS
Translation_1 ON [Application Ratings].[App Rating 2] = Translation_1.[Data
1]) LEFT JOIN [Translation] AS Translation_2 ON [Application Ratings].[App
Rating 3] = Translation_2.[Data 1]
WHERE (((Translation.[Data 1])=[Application Ratings]![App Rating 1])) OR
(((Translation_1.[Data 1])=[Application Ratings]![App Rating 2])) OR
(((Translation_2.[Data 1])=[Application Ratings]![App Rating 3]));

It would be easier if you only had one set of review fields as I suggested ---
SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].Reviewed, Translation.[Value 1] AS Value_1,
[Application Ratings].[Rating Complete], [Application Ratings].Review
FROM [Application Ratings], [Translation]
WHERE (((Translation.[Data 1])=[Application Ratings].[App Rating]));


--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Here you go. . .

SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].[Reviewed 1], [Application Ratings].[App Rating 1],
[Application Ratings].Field1, [Application Ratings].[Reviewed 2],
[Application Ratings].[App Rating 2], [Application Ratings].Field2,
[Application Ratings].[Reviewed 3], [Application Ratings].[App Rating 3],
[Application Ratings].Field3, [Application Ratings].[Rating Complete],
Translation.[Value 1]
FROM [Application Ratings], [Translation]
WHERE (((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2]) Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 3])));

--
SS


"KARL DEWEY" wrote:

Post your SQL. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

OK, that worked, but there seems to be a filter? of some sort. If you look
at the example, it's still combining duplicate values. I would still like to
see all three lines for each app.

Graham, has 2 records because two entries were G
Roark, has 1 records because all three entries were Y
Kington, is displayed the way I would like all records displayed

Key Last Name Reviewed 1 App Rating 1 Field1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 3
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 4
8 Roark KAH Y 8 PJ Y 8 JMD Y 8 yes 8
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 2
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 7
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 8

--
SS


"KARL DEWEY" wrote:

Place the criteria for the second one on second criteria row. Place criteria
for third on the third row.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

First example with App Rating 1 working. I have 78 total records, this
displayed all of them.

Second example with App Rating 1 and App Rating 2 working. I have 78 total
records, this displayed 29 of them. You can see that it dropped Fridman
since their second record didn't match their first.

Third example with App Rating 1, App Rating 2 and App Rating 3 working. I
have 78 total records, this displayed 9 of them. At this point only the
record that have the same three ratings are displayed.

These copied over to excel and were legible if that helps. Thanks.

FIRST EXAMPLE

Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
70 Fridman KAH G ++ EOM/JMD G/Y 3 PJ G 3 yes 1
4 Wolff KAH G + VC G 3 AN G 3 yes 2
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
40 Kantamneni KAH G + PJ G 3 JMD G 3 yes 2
28 Kestner KAH G + PJ G 3 JMD Y ++ 6 yes 2
9 Graham KAH G + JMD G 3 PJ G 3 yes 2

SECOND EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
13 Brown KAH G MO G 3 JMD Y 8 yes 3
32 Wiede KAH G JMD G 3 AN G ++ 1 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3

THIRD EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
8 Roark KAH Y PJ Y 8 JMD Y 8 yes 8
75 Bolte KAH Y DR Y 8 GTM Y 8 yes 8
17 Balon KAH R JMD R 11 DR R 11 yes 11
69 Eriksson app R not com R 11 plete R 11 yes 11

--
SS


"KARL DEWEY" wrote:

So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.
You lost me here.
Post sample data - maybe 5 records.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Whoo hoo!! I had spaces, so as soon as I added those, it worked. Thanks!!

Next question, earlier in this message I mentioned I had three columns and
you had replied:
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

I had it set up that way because any 3 people may review any 3 documents and
give them a rating. So I have Rating 1 and Reviewed 1, Rating 2 and Reviewed
2, etc. Rating is the G/Y/R and Reviewed is the reviewer's initials.

Now, what you helped me with worked great on the first column and it worked
on the second and third, but it filtered out any ratings that didn't match.
So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.

I still want to be able to see all 78 records for each reviewer. Is this
possible? Hopefully this makes sense...
--
SS


"KARL DEWEY" wrote:

It works for me. Does one or the other have a space between the letter and
math signs by change?

Post the SQL of your query. Open in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Oohh, this is very close. It's replacing numbers for text for just G, Y, and
R, but not the text with the various ++'s and --'s. Any ideas?

Also, you asked if I wanted this to store the info back into my table. I
don't think so as long as I can generate my report with the letters, but
using the numbers to rank them. (I did this last year by exporting/importing
this info into Excel and then back again. I'm hoping I don't have to keep
doing that.)

Thanks for your help, if this works, my life just got easier :~)
--
SS


"KARL DEWEY" wrote:

I have three separate columns that will be populated
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

As I said you have too many to use nested IIFs.
A translation table will work for you. The translation table would look
like this --
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5


Then to use it in design view of your query add the translation table --
Fields: Field1 Field2 Data Value
Table: Main Main Trans Trans
Criteria: Main.Field3

Main.Field3 is the field of your table that comtains the Gxx information.
The Value field will output the numerical of the G++.

I seem to get the impression you want to store this translation back into
your table. Is that correct?

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I didn't understand what you were asking me to do, so I thought I'd clarify a
little and explain the purpose of these fields.

I work with documents that are rated on the following scale: G++, G+, G, G-,
G--, Y++, Y+, Y, Y-, Y--, R (G++ is the best, R is the worst). Three
different people will rate the same document so I have three separate columns
that will be populated with any of the above.

I want to convert the letters to: 1, 2, 3, 4, 5 ,6, 7, 8 ,9, 10, 11 (1 is
the best, 11 is the worst). After which I will average the 3 number values
to rank the documents best to worst. (I have that formula already.)

I managed to get the following formula to almost work:
Field1: IIf([Document 1]="G++",1)
except it won't populate any numbers, everything is blank, even if I change
the number value to 20, it won't populate.

I appreciate any additional help with this. Thanks.
--
SS


"KARL DEWEY" wrote:

You can use nested IIFs but if you list is very long then a translation table
would work better.
Data Value

You rock!!! It worked beautifully and gave me what I needed to finish this
project. THANK YOU SO MUCH!!!
--
SS


"KARL DEWEY" wrote:

I think this will get you there --
SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].[Reviewed 1], Translation.[Value 1] AS Value_1,
[Application Ratings].[Reviewed 2], Translation_1.[Value 1] AS Value_2,
[Application Ratings].[Reviewed 3], Translation_2.[Value 1] AS Value_3,
[Application Ratings].[Rating Complete]
FROM (([Application Ratings] LEFT JOIN [Translation] ON [Application
Ratings].[App Rating 1] = Translation.[Data 1]) LEFT JOIN [Translation] AS
Translation_1 ON [Application Ratings].[App Rating 2] = Translation_1.[Data
1]) LEFT JOIN [Translation] AS Translation_2 ON [Application Ratings].[App
Rating 3] = Translation_2.[Data 1]
WHERE (((Translation.[Data 1])=[Application Ratings]![App Rating 1])) OR
(((Translation_1.[Data 1])=[Application Ratings]![App Rating 2])) OR
(((Translation_2.[Data 1])=[Application Ratings]![App Rating 3]));

It would be easier if you only had one set of review fields as I suggested ---
SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].Reviewed, Translation.[Value 1] AS Value_1,
[Application Ratings].[Rating Complete], [Application Ratings].Review
FROM [Application Ratings], [Translation]
WHERE (((Translation.[Data 1])=[Application Ratings].[App Rating]));


--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Here you go. . .

SELECT [Application Ratings].Key, [Application Ratings].[First Name],
[Application Ratings].[Middle Initial], [Application Ratings].[Last Name],
[Application Ratings].[Reviewed 1], [Application Ratings].[App Rating 1],
[Application Ratings].Field1, [Application Ratings].[Reviewed 2],
[Application Ratings].[App Rating 2], [Application Ratings].Field2,
[Application Ratings].[Reviewed 3], [Application Ratings].[App Rating 3],
[Application Ratings].Field3, [Application Ratings].[Rating Complete],
Translation.[Value 1]
FROM [Application Ratings], [Translation]
WHERE (((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2]) Or
((Translation.[Data 1])=[Application Ratings]![App Rating 1] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 2] Or
(Translation.[Data 1])=[Application Ratings]![App Rating 3])));

--
SS


"KARL DEWEY" wrote:

Post your SQL. Open the query in design view, click on menu VIEW - SQL View,
highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

OK, that worked, but there seems to be a filter? of some sort. If you look
at the example, it's still combining duplicate values. I would still like to
see all three lines for each app.

Graham, has 2 records because two entries were G
Roark, has 1 records because all three entries were Y
Kington, is displayed the way I would like all records displayed

Key Last Name Reviewed 1 App Rating 1 Field1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 3
7 Graham KAH G 3 JMD G 3 PJ G - 4 yes 4
8 Roark KAH Y 8 PJ Y 8 JMD Y 8 yes 8
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 2
9 Graham KAH G + 2 JMD G 3 PJ G 3 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 3
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 7
10 Kington KAH G 3 PJ Y + 7 GTM Y 8 yes 8

--
SS


"KARL DEWEY" wrote:

Place the criteria for the second one on second criteria row. Place criteria
for third on the third row.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

First example with App Rating 1 working. I have 78 total records, this
displayed all of them.

Second example with App Rating 1 and App Rating 2 working. I have 78 total
records, this displayed 29 of them. You can see that it dropped Fridman
since their second record didn't match their first.

Third example with App Rating 1, App Rating 2 and App Rating 3 working. I
have 78 total records, this displayed 9 of them. At this point only the
record that have the same three ratings are displayed.

These copied over to excel and were legible if that helps. Thanks.

FIRST EXAMPLE

Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
70 Fridman KAH G ++ EOM/JMD G/Y 3 PJ G 3 yes 1
4 Wolff KAH G + VC G 3 AN G 3 yes 2
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
40 Kantamneni KAH G + PJ G 3 JMD G 3 yes 2
28 Kestner KAH G + PJ G 3 JMD Y ++ 6 yes 2
9 Graham KAH G + JMD G 3 PJ G 3 yes 2

SECOND EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
61 Greenburg KAH G ++ JMD G ++ 1 PJ G 3 yes 1
20 Dahlbeck KAH G + DR G + 2 JMD Y 8 Yes 2
13 Brown KAH G MO G 3 JMD Y 8 yes 3
32 Wiede KAH G JMD G 3 AN G ++ 1 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3

THIRD EXAMPLE
Key Last Name Reviewed 1 App Rating 1 Reviewed 2 App Rating
2 Field2 Reviewed 3 App Rating 3 Field3 Rating Complete Value 1
59 Shah KAH G MB G 3 PJ G 3 yes 3
42 Ethington KAH G JMD G 3 PJ G 3 yes 3
64 Gaffey KAH G JMD G 3 PJ G 3 yes 3
8 Roark KAH Y PJ Y 8 JMD Y 8 yes 8
75 Bolte KAH Y DR Y 8 GTM Y 8 yes 8
17 Balon KAH R JMD R 11 DR R 11 yes 11
69 Eriksson app R not com R 11 plete R 11 yes 11

--
SS


"KARL DEWEY" wrote:

So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.
You lost me here.
Post sample data - maybe 5 records.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Whoo hoo!! I had spaces, so as soon as I added those, it worked. Thanks!!

Next question, earlier in this message I mentioned I had three columns and
you had replied:
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

I had it set up that way because any 3 people may review any 3 documents and
give them a rating. So I have Rating 1 and Reviewed 1, Rating 2 and Reviewed
2, etc. Rating is the G/Y/R and Reviewed is the reviewer's initials.

Now, what you helped me with worked great on the first column and it worked
on the second and third, but it filtered out any ratings that didn't match.
So I went from 78 entries, to 29, to 9 with the final result being that all
three ratings were the same.

I still want to be able to see all 78 records for each reviewer. Is this
possible? Hopefully this makes sense...
--
SS


"KARL DEWEY" wrote:

It works for me. Does one or the other have a space between the letter and
math signs by change?

Post the SQL of your query. Open in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

Oohh, this is very close. It's replacing numbers for text for just G, Y, and
R, but not the text with the various ++'s and --'s. Any ideas?

Also, you asked if I wanted this to store the info back into my table. I
don't think so as long as I can generate my report with the letters, but
using the numbers to rank them. (I did this last year by exporting/importing
this info into Excel and then back again. I'm hoping I don't have to keep
doing that.)

Thanks for your help, if this works, my life just got easier :~)
--
SS


"KARL DEWEY" wrote:

I have three separate columns that will be populated
First your table structure is wrong as you are using a spreadsheet format.
Instead of three fields for grading use one to identify who did it and a
second for grade.

As I said you have too many to use nested IIFs.
A translation table will work for you. The translation table would look
like this --
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5


Then to use it in design view of your query add the translation table --
Fields: Field1 Field2 Data Value
Table: Main Main Trans Trans
Criteria: Main.Field3

Main.Field3 is the field of your table that comtains the Gxx information.
The Value field will output the numerical of the G++.

I seem to get the impression you want to store this translation back into
your table. Is that correct?

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote:

I didn't understand what you were asking me to do, so I thought I'd clarify a
little and explain the purpose of these fields.

I work with documents that are rated on the following scale: G++, G+, G, G-,
G--, Y++, Y+, Y, Y-, Y--, R (G++ is the best, R is the worst). Three
different people will rate the same document so I have three separate columns
that will be populated with any of the above.

I want to convert the letters to: 1, 2, 3, 4, 5 ,6, 7, 8 ,9, 10, 11 (1 is
the best, 11 is the worst). After which I will average the 3 number values
to rank the documents best to worst. (I have that formula already.)

I managed to get the following formula to almost work:
Field1: IIf([Document 1]="G++",1)
except it won't populate any numbers, everything is blank, even if I change
the number value to 20, it won't populate.

I appreciate any additional help with this. Thanks.
--
SS


"KARL DEWEY" wrote:

You can use nested IIFs but if you list is very long then a translation table
would work better.
Data Value
G++ 1
G+ 2
G 3
G- 4
G-- 5

Then to use it in design view --
Fields: Field1 Field2 Data Value
Criteria: Field3

Field3 is the field of your table that comtains the Gxx information.

--
KARL DEWEY
Build a little - Test a little


"Stephanie" wrote: