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
|
|||
|
|||
Switch not working
So after trying about a million things, i figured out how to use the switch
function, but now for some reason it is excluding some records. When i run the switch query it comes up with 2464 records, and when i run the query just of that field i get 4786. I did include null entries and zeros and i just cant figure out what might be causing this. Is there a common error that i should be aware of? Here is my SQL for the 2 queries Returns 2464 records SELECT DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION, DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID WHERE (((DEMOGRAPHIC.WORK)="1")) OR (((DEMOGRAPHIC.WORK)="2")) OR (((DEMOGRAPHIC.WORK)="3")) OR (((DEMOGRAPHIC.WORK)="0")) OR (((DEMOGRAPHIC.WORK) Is Null)) OR (((DEMOGRAPHIC.WORK)=Switch(4,19,5,19,6,19,7,19,8, 19,8,19,9,19,10,19,11,19,12,19,13,19) And (DEMOGRAPHIC.WORK)=Switch(14,19,15,19,16,19,17,19, 18,19,19,19))); Here is the one that returns 4789 records SELECT DEMOGRAPHIC.STUDENT_ID, DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID; |
#2
|
|||
|
|||
Switch not working
The way you use Switch is not very common, You could have replace them with
the number 19. Furthermore, having: WHERE ... FieldName = something AND FieldName = somethingElse won't return any record at all, unless something = somethingElse (as it does, here, since your Switch-s both always return 19 ). Indeed, if the value in the field is equal to, say something = 21, then it cannot be ALSO equal to, say, something else = 22, because, well, if it is equal to 21, it cannot be equal to 22. What do you WANT, rather than HOW you did implement it? Vanderghast, Access MVP "Student Databaser" wrote in message news So after trying about a million things, i figured out how to use the switch function, but now for some reason it is excluding some records. When i run the switch query it comes up with 2464 records, and when i run the query just of that field i get 4786. I did include null entries and zeros and i just cant figure out what might be causing this. Is there a common error that i should be aware of? Here is my SQL for the 2 queries Returns 2464 records SELECT DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION, DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID WHERE (((DEMOGRAPHIC.WORK)="1")) OR (((DEMOGRAPHIC.WORK)="2")) OR (((DEMOGRAPHIC.WORK)="3")) OR (((DEMOGRAPHIC.WORK)="0")) OR (((DEMOGRAPHIC.WORK) Is Null)) OR (((DEMOGRAPHIC.WORK)=Switch(4,19,5,19,6,19,7,19,8, 19,8,19,9,19,10,19,11,19,12,19,13,19) And (DEMOGRAPHIC.WORK)=Switch(14,19,15,19,16,19,17,19, 18,19,19,19))); Here is the one that returns 4789 records SELECT DEMOGRAPHIC.STUDENT_ID, DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID; |
#3
|
|||
|
|||
Switch not working
I want to show records that have a 1,2, or 3, and i want records with 4-19 to
be displayed as 19. Is this even possible without creating another table? "Michel Walsh" wrote: The way you use Switch is not very common, You could have replace them with the number 19. Furthermore, having: WHERE ... FieldName = something AND FieldName = somethingElse won't return any record at all, unless something = somethingElse (as it does, here, since your Switch-s both always return 19 ). Indeed, if the value in the field is equal to, say something = 21, then it cannot be ALSO equal to, say, something else = 22, because, well, if it is equal to 21, it cannot be equal to 22. What do you WANT, rather than HOW you did implement it? Vanderghast, Access MVP "Student Databaser" wrote in message news So after trying about a million things, i figured out how to use the switch function, but now for some reason it is excluding some records. When i run the switch query it comes up with 2464 records, and when i run the query just of that field i get 4786. I did include null entries and zeros and i just cant figure out what might be causing this. Is there a common error that i should be aware of? Here is my SQL for the 2 queries Returns 2464 records SELECT DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION, DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID WHERE (((DEMOGRAPHIC.WORK)="1")) OR (((DEMOGRAPHIC.WORK)="2")) OR (((DEMOGRAPHIC.WORK)="3")) OR (((DEMOGRAPHIC.WORK)="0")) OR (((DEMOGRAPHIC.WORK) Is Null)) OR (((DEMOGRAPHIC.WORK)=Switch(4,19,5,19,6,19,7,19,8, 19,8,19,9,19,10,19,11,19,12,19,13,19) And (DEMOGRAPHIC.WORK)=Switch(14,19,15,19,16,19,17,19, 18,19,19,19))); Here is the one that returns 4789 records SELECT DEMOGRAPHIC.STUDENT_ID, DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID; |
#4
|
|||
|
|||
Switch not working
PERHAPS what you want is the following
SELECT DEMOGRAPHIC.WORK , STUDENT_COURSE.DATE_OF_REGISTRATION , DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID , IIF(Demographic.Work Between 4 and 19,19,Demographic.Work) as CalcValue FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Student Databaser wrote: So after trying about a million things, i figured out how to use the switch function, but now for some reason it is excluding some records. When i run the switch query it comes up with 2464 records, and when i run the query just of that field i get 4786. I did include null entries and zeros and i just cant figure out what might be causing this. Is there a common error that i should be aware of? Here is my SQL for the 2 queries Returns 2464 records SELECT DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION, DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID WHERE (((DEMOGRAPHIC.WORK)="1")) OR (((DEMOGRAPHIC.WORK)="2")) OR (((DEMOGRAPHIC.WORK)="3")) OR (((DEMOGRAPHIC.WORK)="0")) OR (((DEMOGRAPHIC.WORK) Is Null)) OR (((DEMOGRAPHIC.WORK)=Switch(4,19,5,19,6,19,7,19,8, 19,8,19,9,19,10,19,11,19,12,19,13,19) And (DEMOGRAPHIC.WORK)=Switch(14,19,15,19,16,19,17,19, 18,19,19,19))); Here is the one that returns 4789 records SELECT DEMOGRAPHIC.STUDENT_ID, DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID; |
#5
|
|||
|
|||
Switch not working
Thanks John, but unfortunately this isn't working either I don't think i
understand what you mean by CalcValue. "John Spencer" wrote: PERHAPS what you want is the following SELECT DEMOGRAPHIC.WORK , STUDENT_COURSE.DATE_OF_REGISTRATION , DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID , IIF(Demographic.Work Between 4 and 19,19,Demographic.Work) as CalcValue FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Student Databaser wrote: So after trying about a million things, i figured out how to use the switch function, but now for some reason it is excluding some records. When i run the switch query it comes up with 2464 records, and when i run the query just of that field i get 4786. I did include null entries and zeros and i just cant figure out what might be causing this. Is there a common error that i should be aware of? Here is my SQL for the 2 queries Returns 2464 records SELECT DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION, DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID WHERE (((DEMOGRAPHIC.WORK)="1")) OR (((DEMOGRAPHIC.WORK)="2")) OR (((DEMOGRAPHIC.WORK)="3")) OR (((DEMOGRAPHIC.WORK)="0")) OR (((DEMOGRAPHIC.WORK) Is Null)) OR (((DEMOGRAPHIC.WORK)=Switch(4,19,5,19,6,19,7,19,8, 19,8,19,9,19,10,19,11,19,12,19,13,19) And (DEMOGRAPHIC.WORK)=Switch(14,19,15,19,16,19,17,19, 18,19,19,19))); Here is the one that returns 4789 records SELECT DEMOGRAPHIC.STUDENT_ID, DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID; |
#6
|
|||
|
|||
Switch not working
OK, we don't understand what you mean by "not working". You are going to
have to be a lot clearer about what you want. The best way to do this is to show us a few rows of sample data (relevant fields only), followed by the rows of data you would like a successfully created query to return from that sample data. Student Databaser wrote: Thanks John, but unfortunately this isn't working either I don't think i understand what you mean by CalcValue. "John Spencer" wrote: PERHAPS what you want is the following SELECT DEMOGRAPHIC.WORK , STUDENT_COURSE.DATE_OF_REGISTRATION , DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID , IIF(Demographic.Work Between 4 and 19,19,Demographic.Work) as CalcValue FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Student Databaser wrote: So after trying about a million things, i figured out how to use the switch function, but now for some reason it is excluding some records. When i run the switch query it comes up with 2464 records, and when i run the query just of that field i get 4786. I did include null entries and zeros and i just cant figure out what might be causing this. Is there a common error that i should be aware of? Here is my SQL for the 2 queries Returns 2464 records SELECT DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION, DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID WHERE (((DEMOGRAPHIC.WORK)="1")) OR (((DEMOGRAPHIC.WORK)="2")) OR (((DEMOGRAPHIC.WORK)="3")) OR (((DEMOGRAPHIC.WORK)="0")) OR (((DEMOGRAPHIC.WORK) Is Null)) OR (((DEMOGRAPHIC.WORK)=Switch(4,19,5,19,6,19,7,19,8, 19,8,19,9,19,10,19,11, 19,12,19,13,19) And (DEMOGRAPHIC.WORK)=Switch(14,19,15,19,16,19,17,19, 18,19,19,19))); Here is the one that returns 4789 records SELECT DEMOGRAPHIC.STUDENT_ID, DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID; -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. |
#7
|
|||
|
|||
Switch not working
SELECT DEMOGRAPHIC.WORK,
iif( demographic.work BETWEEN 4 AND 19, 19, demographic.work) FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID WHERE DEMOGRAPHIC.WORK IN("1", "2", "3") should do. Vanderghast, Access MVP "Student Databaser" wrote in message ... I want to show records that have a 1,2, or 3, and i want records with 4-19 to be displayed as 19. Is this even possible without creating another table? "Michel Walsh" wrote: The way you use Switch is not very common, You could have replace them with the number 19. Furthermore, having: WHERE ... FieldName = something AND FieldName = somethingElse won't return any record at all, unless something = somethingElse (as it does, here, since your Switch-s both always return 19 ). Indeed, if the value in the field is equal to, say something = 21, then it cannot be ALSO equal to, say, something else = 22, because, well, if it is equal to 21, it cannot be equal to 22. What do you WANT, rather than HOW you did implement it? Vanderghast, Access MVP "Student Databaser" wrote in message news So after trying about a million things, i figured out how to use the switch function, but now for some reason it is excluding some records. When i run the switch query it comes up with 2464 records, and when i run the query just of that field i get 4786. I did include null entries and zeros and i just cant figure out what might be causing this. Is there a common error that i should be aware of? Here is my SQL for the 2 queries Returns 2464 records SELECT DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION, DEMOGRAPHIC.WORK, STUDENT.STUDENT_ID FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID WHERE (((DEMOGRAPHIC.WORK)="1")) OR (((DEMOGRAPHIC.WORK)="2")) OR (((DEMOGRAPHIC.WORK)="3")) OR (((DEMOGRAPHIC.WORK)="0")) OR (((DEMOGRAPHIC.WORK) Is Null)) OR (((DEMOGRAPHIC.WORK)=Switch(4,19,5,19,6,19,7,19,8, 19,8,19,9,19,10,19,11,19,12,19,13,19) And (DEMOGRAPHIC.WORK)=Switch(14,19,15,19,16,19,17,19, 18,19,19,19))); Here is the one that returns 4789 records SELECT DEMOGRAPHIC.STUDENT_ID, DEMOGRAPHIC.WORK, STUDENT_COURSE.DATE_OF_REGISTRATION FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID; |
#8
|
|||
|
|||
Switch not working
Ok, sorry i wasn't clear. Here are a few rows of what the query returned.
What didn't work is that it didn't make numbers 4-19 appear as 19 for the work field, and under CalcValue it says #Error. I assume this is because i should have entered in some other field or expression but i was sure what John meant. Thanks so much for your help! I am obviously a novice at this and these forums really help me to learn. Expr1000 DATE_OF_REGISTRATION WORK STUDENT_ID CalcValue 4 2/13/2008 4 aa0703go #Error 2 8/28/2007 2 ab0101ja #Error 2 9/10/2007 2 ab0101ja #Error 2 9/10/2007 2 ab0101ja #Error 8 7/18/2006 8 ab0109na #Error 18 10/17/2007 18 ab0601sa #Error |
#9
|
|||
|
|||
Switch not working
We also need to see what the data looks like in the table (before
running the query), and what you want it to look like when the query runs. Student Databaser wrote: Ok, sorry i wasn't clear. Here are a few rows of what the query returned. What didn't work is that it didn't make numbers 4-19 appear as 19 for the work field, and under CalcValue it says #Error. I assume this is because i should have entered in some other field or expression but i was sure what John meant. Thanks so much for your help! I am obviously a novice at this and these forums really help me to learn. Expr1000 DATE_OF_REGISTRATION WORK STUDENT_ID CalcValue 4 2/13/2008 4 aa0703go #Error 2 8/28/2007 2 ab0101ja #Error 2 9/10/2007 2 ab0101ja #Error 2 9/10/2007 2 ab0101ja #Error 8 7/18/2006 8 ab0109na #Error 18 10/17/2007 18 ab0601sa #Error -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. |
#10
|
|||
|
|||
Switch not working
Is demographic.Work a number field or a text field. In one place you treat it
as a number field 14 and elsewhere as a text field "1". I am going to guess it is a text field. SELECT DEMOGRAPHIC.WORK , STUDENT_COURSE.DATE_OF_REGISTRATION , DEMOGRAPHIC.WORK , STUDENT.STUDENT_ID , IIF(Demographic.Work in ("1","2","3") or Demographic.Work is null,Demographic.Work,"19") as CalcValue FROM (STUDENT INNER JOIN DEMOGRAPHIC ON STUDENT.STUDENT_ID = DEMOGRAPHIC.STUDENT_ID) INNER JOIN STUDENT_COURSE ON STUDENT.STUDENT_ID = STUDENT_COURSE.STUDENT_ID CalcValue is the name of the value that is calculated in the expression. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Student Databaser wrote: Ok, sorry i wasn't clear. Here are a few rows of what the query returned. What didn't work is that it didn't make numbers 4-19 appear as 19 for the work field, and under CalcValue it says #Error. I assume this is because i should have entered in some other field or expression but i was sure what John meant. Thanks so much for your help! I am obviously a novice at this and these forums really help me to learn. Expr1000 DATE_OF_REGISTRATION WORK STUDENT_ID CalcValue 4 2/13/2008 4 aa0703go #Error 2 8/28/2007 2 ab0101ja #Error 2 9/10/2007 2 ab0101ja #Error 2 9/10/2007 2 ab0101ja #Error 8 7/18/2006 8 ab0109na #Error 18 10/17/2007 18 ab0601sa #Error |
|
Thread Tools | |
Display Modes | |
|
|