If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Converting text to a number in query
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 |
#2
|
|||
|
|||
Converting text to a number in query
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 |
#3
|
|||
|
|||
Converting text to a number in query
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 |
#4
|
|||
|
|||
Converting text to a number in query
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 |
#5
|
|||
|
|||
Converting text to a number in query
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 |
#6
|
|||
|
|||
Converting text to a number in query
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 |
#7
|
|||
|
|||
Converting text to a number in query
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 |
#8
|
|||
|
|||
Converting text to a number in query
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 |
#9
|
|||
|
|||
Converting text to a number in query
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 |
#10
|
|||
|
|||
Converting text to a number in query
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 |
|
Thread Tools | |
Display Modes | |
|
|