Stephanie said: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 said: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 said: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 said: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 said: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
:
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.
Stephanie said: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 said:First your table structure is wrong as you are using a spreadsheet format.I have three separate columns that will be populated
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 said: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
:
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
:
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.
KARL DEWEY said: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 said: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 said: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
:
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
:
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
:
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.
Stephanie said: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:
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 said: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 said: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
:
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
:
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
:
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
:
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.
KARL DEWEY said:three ratings were the same.
You lost me here.
Post sample data - maybe 5 records.
--
KARL DEWEY
Build a little - Test a little
Stephanie said: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 said: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
:
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
:
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
:
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
:
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
:
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.
Stephanie said: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 said:three ratings were the same.So I went from 78 entries, to 29, to 9 with the final result being that all
You lost me here.
Post sample data - maybe 5 records.
--
KARL DEWEY
Build a little - Test a little
Stephanie said: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
:
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
:
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
:
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
:
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
:
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
:
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.
KARL DEWEY said: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 said: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 said: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
:
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
:
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
:
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
:
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
:
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
:
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
:
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.
Stephanie said: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 said: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 said: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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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.
KARL DEWEY said: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 said: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 said: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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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.
Stephanie said: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 said: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 said: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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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.
KARL DEWEY said: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 said: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 said: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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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 said: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 said: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 said: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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
You can use nested IIFs but if you list is very long then a translation table
would work better.
Data Value
KARL DEWEY said: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 said: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 said: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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
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
:
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.