do-if select statement

  • Thread starter Thread starter harried-edu
  • Start date Start date
H

harried-edu

I have two tables I need to pull data from; 1 has student
demographic info and the other has course requests (This
is not my database, so I cannot restructure these files).
Both tables have a unique link field so I can create a
join.The coures file lists each request as a new record
(sequence 1 - 9) for each student. I need to run a query
that returns the student info once with all nine course
requests returned as a singlr record. I know this is
probably very simple but the solution is eluding me and
unfortunately, this is education and I am multi-tasked
out. Thanks for the help.
 
I think you can use a crosstab query where the Sequence is used as the
column heading. I would use something like:
"S" & [Sequence]
 
Thanks Duane, but that does not give me the value in the
course request field, it shows the digit 1 to indicate a
course request but not what the course request is; I'm
sure I've done it wrong...what does the "S" stand for?
Student?
-----Original Message-----
I think you can use a crosstab query where the Sequence is used as the
column heading. I would use something like:
"S" & [Sequence]

--
Duane Hookom
MS Access MVP
--

I have two tables I need to pull data from; 1 has student
demographic info and the other has course requests (This
is not my database, so I cannot restructure these files).
Both tables have a unique link field so I can create a
join.The coures file lists each request as a new record
(sequence 1 - 9) for each student. I need to run a query
that returns the student info once with all nine course
requests returned as a singlr record. I know this is
probably very simple but the solution is eluding me and
unfortunately, this is education and I am multi-tasked
out. Thanks for the help.
Click to expand...


.
Click to expand...
 
The "S" is just so that you don't generate field/column names that are just
numbers. What is the SQL of the crosstab you created? Did you place the
course title or course code as the Value?

--
Duane Hookom
MS Access MVP
--

Thanks Duane, but that does not give me the value in the
course request field, it shows the digit 1 to indicate a
course request but not what the course request is; I'm
sure I've done it wrong...what does the "S" stand for?
Student?
-----Original Message-----
I think you can use a crosstab query where the Sequence is used as the
column heading. I would use something like:
"S" & [Sequence]

--
Duane Hookom
MS Access MVP
--

I have two tables I need to pull data from; 1 has student
demographic info and the other has course requests (This
is not my database, so I cannot restructure these files).
Both tables have a unique link field so I can create a
join.The coures file lists each request as a new record
(sequence 1 - 9) for each student. I need to run a query
that returns the student info once with all nine course
requests returned as a singlr record. I know this is
probably very simple but the solution is eluding me and
unfortunately, this is education and I am multi-tasked
out. Thanks for the help.
Click to expand...


.
Click to expand...
Click to expand...
 
This is the SQL for the crosstab, the problem is the
function I don't want a count I want the display,
obviously I can't figure out how to do that....
TRANSFORM Count(ASSS4006.CRSREQ) AS CountOfCRSREQ
SELECT ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
FROM ASSS4006 INNER JOIN ASTU4006 ON ASSS4006.STULINK =
ASTU4006.STULINK
GROUP BY ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
PIVOT ASSS4006.SEQUENCE;

This was my first query a select or and that's when I
thought maybe a do-if-then based on a different sequence
value, again I couldn't find script that worked...
SELECT ASTU4006.LASTNAME, ASTU4006.FIRSTNAME,
ASSS4006.CRSREQ, ASSS4006.CRSREQ, ASSS4006.CRSREQ
FROM ASSS4006 INNER JOIN ASTU4006 ON ASSS4006.STULINK =
ASTU4006.STULINK
WHERE (([ASSS4006]![SEQUENCE]='1')) OR (([ASSS4006]!
[SEQUENCE]="2")) OR (([ASSS4006]![SEQUENCE]="3"));

Thanks very much for your time and imput...

-----Original Message-----
The "S" is just so that you don't generate field/column names that are just
numbers. What is the SQL of the crosstab you created? Did you place the
course title or course code as the Value?

--
Duane Hookom
MS Access MVP
--

Thanks Duane, but that does not give me the value in the
course request field, it shows the digit 1 to indicate a
course request but not what the course request is; I'm
sure I've done it wrong...what does the "S" stand for?
Student?
-----Original Message-----
I think you can use a crosstab query where the Sequence is used as the
column heading. I would use something like:
"S" & [Sequence]
Click to expand...
wrote
in message
I have two tables I need to pull data from; 1 has student
demographic info and the other has course requests (This
is not my database, so I cannot restructure these files).
Both tables have a unique link field so I can create a
join.The coures file lists each request as a new record
(sequence 1 - 9) for each student. I need to run a query
that returns the student info once with all nine course
requests returned as a singlr record. I know this is
probably very simple but the solution is eluding me and
unfortunately, this is education and I am multi- tasked
out. Thanks for the help.


.
Click to expand...
Click to expand...


.
Click to expand...
 
TRANSFORM First(ASSS4006.CRSREQ) AS theValue
SELECT ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
FROM ASSS4006 INNER JOIN ASTU4006 ON ASSS4006.STULINK =
ASTU4006.STULINK
GROUP BY ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
PIVOT "S" & ASSS4006.SEQUENCE;


--
Duane Hookom
MS Access MVP


harried said:
This is the SQL for the crosstab, the problem is the
function I don't want a count I want the display,
obviously I can't figure out how to do that....
TRANSFORM Count(ASSS4006.CRSREQ) AS CountOfCRSREQ
SELECT ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
FROM ASSS4006 INNER JOIN ASTU4006 ON ASSS4006.STULINK =
ASTU4006.STULINK
GROUP BY ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
PIVOT ASSS4006.SEQUENCE;

This was my first query a select or and that's when I
thought maybe a do-if-then based on a different sequence
value, again I couldn't find script that worked...
SELECT ASTU4006.LASTNAME, ASTU4006.FIRSTNAME,
ASSS4006.CRSREQ, ASSS4006.CRSREQ, ASSS4006.CRSREQ
FROM ASSS4006 INNER JOIN ASTU4006 ON ASSS4006.STULINK =
ASTU4006.STULINK
WHERE (([ASSS4006]![SEQUENCE]='1')) OR (([ASSS4006]!
[SEQUENCE]="2")) OR (([ASSS4006]![SEQUENCE]="3"));

Thanks very much for your time and imput...

-----Original Message-----
The "S" is just so that you don't generate field/column names that are just
numbers. What is the SQL of the crosstab you created? Did you place the
course title or course code as the Value?

--
Duane Hookom
MS Access MVP
--

Thanks Duane, but that does not give me the value in the
course request field, it shows the digit 1 to indicate a
course request but not what the course request is; I'm
sure I've done it wrong...what does the "S" stand for?
Student?
-----Original Message-----
I think you can use a crosstab query where the Sequence
is used as the
column heading. I would use something like:
"S" & [Sequence]

--
Duane Hookom
MS Access MVP
--

in message
I have two tables I need to pull data from; 1 has
student
demographic info and the other has course requests (This
is not my database, so I cannot restructure these
files).
Both tables have a unique link field so I can create a
join.The coures file lists each request as a new record
(sequence 1 - 9) for each student. I need to run a query
that returns the student info once with all nine course
requests returned as a singlr record. I know this is
probably very simple but the solution is eluding me and
unfortunately, this is education and I am multi- tasked
out. Thanks for the help.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Thank you soooooooooooooooo much, that did the trick!
-----Original Message-----
TRANSFORM First(ASSS4006.CRSREQ) AS theValue
SELECT ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
FROM ASSS4006 INNER JOIN ASTU4006 ON ASSS4006.STULINK =
ASTU4006.STULINK
GROUP BY ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
PIVOT "S" & ASSS4006.SEQUENCE;


--
Duane Hookom
MS Access MVP


This is the SQL for the crosstab, the problem is the
function I don't want a count I want the display,
obviously I can't figure out how to do that....
TRANSFORM Count(ASSS4006.CRSREQ) AS CountOfCRSREQ
SELECT ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
FROM ASSS4006 INNER JOIN ASTU4006 ON ASSS4006.STULINK =
ASTU4006.STULINK
GROUP BY ASTU4006.LASTNAME, ASTU4006.FIRSTNAME
PIVOT ASSS4006.SEQUENCE;

This was my first query a select or and that's when I
thought maybe a do-if-then based on a different sequence
value, again I couldn't find script that worked...
SELECT ASTU4006.LASTNAME, ASTU4006.FIRSTNAME,
ASSS4006.CRSREQ, ASSS4006.CRSREQ, ASSS4006.CRSREQ
FROM ASSS4006 INNER JOIN ASTU4006 ON ASSS4006.STULINK =
ASTU4006.STULINK
WHERE (([ASSS4006]![SEQUENCE]='1')) OR (([ASSS4006]!
[SEQUENCE]="2")) OR (([ASSS4006]![SEQUENCE]="3"));

Thanks very much for your time and imput...

-----Original Message-----
The "S" is just so that you don't generate field/column names that are just
numbers. What is the SQL of the crosstab you created?
Click to expand...
Did
you place the
course title or course code as the Value?

--
Duane Hookom
MS Access MVP
--

Thanks Duane, but that does not give me the value in the
course request field, it shows the digit 1 to indicate a
course request but not what the course request is; I'm
sure I've done it wrong...what does the "S" stand for?
Student?
-----Original Message-----
I think you can use a crosstab query where the Sequence
is used as the
column heading. I would use something like:
"S" & [Sequence]

--
Duane Hookom
MS Access MVP
--

in message
I have two tables I need to pull data from; 1 has
student
demographic info and the other has course requests (This
is not my database, so I cannot restructure these
files).
Both tables have a unique link field so I can
Click to expand...
create
a
join.The coures file lists each request as a new record
(sequence 1 - 9) for each student. I need to run a query
that returns the student info once with all nine course
requests returned as a singlr record. I know this is
probably very simple but the solution is eluding
Click to expand...
me
and
unfortunately, this is education and I am multi- tasked
out. Thanks for the help.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top