INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL query in Access

SQL query in Access

(OP)
Hi
ACCESS 2003
I don't normally work with access and having a slight problem
Have a table that 5 sections that needs to be report on as one session.

I have manage to use a union statement but need to be able which session is being reported on.

need see session 1, session 2, etc

there is no field for session number but there are subject fields
called [Evaluation Data].S1_Subject as lesson, [Evaluation Data].S2_Subject as lesson, etc  I would normally use a case statement but you cannot use case in access sql, any ideas would be most appreciated

find enclosed union statement

SELECT [Evaluation Data].[Unique ID] as id, [Evaluation Data].Date as date1, [Evaluation Data].Time as time1, [Evaluation Data].[Client Name] as name, [Evaluation Data].Score as score, [Evaluation Data].S1_Subject as lesson, [Evaluation Data].S1_Yr_Group as group1, [Evaluation Data].S1_Act_Desc as desc1, [Evaluation Data].S1_Strengths as strenghts, [Evaluation Data].S1_Improvements as improvements, [Evaluation Data].S1_Future_Use as future_use
FROM [Evaluation Data] ;

union
SELECT [Evaluation Data].[Unique ID] as id, [Evaluation Data].Date as date1, [Evaluation Data].Time as time1, [Evaluation Data].[Client Name] as name, [Evaluation Data].Score as score, [Evaluation Data].S2_Subject as lesson, [Evaluation Data].S2_Yr_Group as group1, [Evaluation Data].S2_Act_Desc as desc1, [Evaluation Data].S2_Strengths as strenghts, [Evaluation Data].S2_Improvements as improvements, [Evaluation Data].S2_Future_Use as future_use
FROM [Evaluation Data]
;

union
SELECT [Evaluation Data].[Unique ID] as id, [Evaluation Data].Date as date1, [Evaluation Data].Time as time1, [Evaluation Data].[Client Name] as name, [Evaluation Data].Score as score, [Evaluation Data].S3_Subject as lesson, [Evaluation Data].S3_Yr_Group as group1, [Evaluation Data].S3_Act_Desc as desc1, [Evaluation Data].S3_Strengths as strenghts, [Evaluation Data].S3_Improvements as improvements, [Evaluation Data].S3_Future_Use as future_use
FROM [Evaluation Data];


Union

SELECT [Evaluation Data].[Unique ID] as id, [Evaluation Data].Date as date1, [Evaluation Data].Time as time1, [Evaluation Data].[Client Name] as name, [Evaluation Data].Score as score, [Evaluation Data].S4_Subject as lesson, [Evaluation Data].S4_Yr_Group as group1, [Evaluation Data].S4_Act_Desc as desc1, [Evaluation Data].S4_Strengths as strenghts, [Evaluation Data].S4_Improvements as improvements, [Evaluation Data].S4_Future_Use as future_use
FROM [Evaluation Data] ;


UNION SELECT [Evaluation Data].[Unique ID] as id, [Evaluation Data].Date as date1, [Evaluation Data].Time as time1, [Evaluation Data].[Client Name] as name, [Evaluation Data].Score as score, [Evaluation Data].S5_Subject as lesson, [Evaluation Data].S5_Yr_Group as group1, [Evaluation Data].S5_Act_Desc as desc1, [Evaluation Data].S5_Strengths as strenghts, [Evaluation Data].S5_Improvements as improvements, [Evaluation Data].S5_Future_Use as future_use
FROM [Evaluation Data] ;
  

RE: SQL query in Access




hi,

Quote:

normally use a case statement
Use the IIF() function.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: SQL query in Access



Post whatever CASE statement you would use, if you could.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: SQL query in Access

Your SQL can't include multiple semi-colons. You don't need to continually alias the columns after the first select. You should not name anything name. I also corrected the spelling of Strengths.

I think you might be looking for something like the following but like Skip, can't tell for sure.

CODE

SELECT 1 as SessionNum, [Unique ID] as id, Date as date1, Time as time1, [Client Name] as ClientName, Score,
S1_Subject as lesson, S1_Yr_Group as group1, S1_Act_Desc as desc1, S1_Strengths as strengths,
S1_Improvements as improvements, S1_Future_Use as future_use
FROM [Evaluation Data]
Union
SELECT 2, [Unique ID], Date, Time, [Client Name], Score, S2_Subject, S2_Yr_Group, S2_Act_Desc, S2_Strengths,
S2_Improvements, S2_Future_Use
FROM [Evaluation Data]
union
SELECT 3, [Unique ID], Date, Time, [Client Name], Score, S3_Subject, S3_Yr_Group, S3_Act_Desc, S3_Strengths,
S3_Improvements, S3_Future_Use
FROM [Evaluation Data]
Union
SELECT 4, [Unique ID], Date, Time, [Client Name], Score, S4_Subject, S4_Yr_Group, S4_Act_Desc, S4_Strengths,
S4_Improvements, S4_Future_Use
FROM [Evaluation Data]
UNION
SELECT 5, [Unique ID], Date, Time, [Client Name], Score, S5_Subject, S5_Yr_Group, S5_Act_Desc, S5_Strengths,
S5_Improvements, S5_Future_Use
FROM [Evaluation Data] ;

Duane
Hook'D on Access
MS Access MVP

RE: SQL query in Access

(OP)
Thank you very much, just what the doctor order.
Like to say a big thank for all your tiem and effort
Ralph

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close