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;

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;

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;

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;

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;

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.

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;

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

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.

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