SQL query in Access 1

[FB1]

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] ;

 

[SkipVought]

hi,

normally use a case statement

Use the IIF() function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

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

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

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.

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

 

[FB1]

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