How to have fields show up as aliases

[error123]

Hi everyone and Happy New Year!

...I hope I am posting this new post on a correct place...

I am trying to create a cool looking Alias, however it just doesn't want to happen.
I have a category field,a CAB field,Valve type of procedures(4 of them)field in the table.
I believe that whom created this query before used the logic, that if the Mitral Procedure field in the table cointained Replacement for the patient than that was aliased MVR. If Reconstruction was done without Annuloplasty it was aliased MVP. If CAB + Reconstruction without Annuloplasty was done it was aliased as CAB + MVR On the report the Mitral procedure clm header was Aliased as Category.
So it would look like:
Category
CAB + MVP
CAB + MVP
CAB + MVR
MVR
MVR <----depends on what type of procedure the patient
had, these are the aliases within the Category
(Aliased)CLM heading.
MVP

Someone else created these aliases and I am trying to remake it, but maybe another query was created before on valves than that was used with the CAB field.

Would you be so kind to let me know the best way of doing this from Access?

This shows how the actual category field looks in the table:

SELECT Demographics.Last_Name, Event_STS.Discharge_Date, Event_STS.Surgery_Date, STS_ValveSurgery.Mitral_Procedure AS CATEGORY, Event_STS.CABG AS CABWASDONE
FROM (Demographics INNER JOIN Event_STS ON Demographics.SS_Patient_ID = Event_STS.SS_Patient_ID) INNER JOIN (STS_252 INNER JOIN STS_ValveSurgery ON STS_252.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) ON (Event_STS.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) AND (Event_STS.SS_Event_STS_ID = STS_252.SS_Event_STS_ID)
WHERE (((Event_STS.Discharge_Date) Between [Start_Date] And [End_Date]) AND ((STS_ValveSurgery.Mitral_Procedure) Not Like "No") AND ((Event_STS.Valve)=1)) OR (((STS_ValveSurgery.Mitral_Procedure) Like "Replacemen AS MVR")) OR (((STS_ValveSurgery.Mitral_Procedure) Like "Reconstruction with Annuloplasty AS MVP"))
ORDER BY Demographics.Last_Name, Event_STS.Surgery_Date;

This is the 4 valve types:
SELECT Demographics.Last_Name, Event_STS.Discharge_Date, Event_STS.Surgery_Date, STS_ValveSurgery.Mitral_Procedure, Event_STS.CABG, STS_ValveSurgery.Aortic_Procedure, STS_ValveSurgery.Tricuspid_Procedure, STS_ValveSurgery.Pulmonic_Procedure, Event_STS.Valve
FROM ((Demographics INNER JOIN Event_STS ON Demographics.SS_Patient_ID = Event_STS.SS_Patient_ID) INNER JOIN (STS_252 INNER JOIN STS_ValveSurgery ON STS_252.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) ON (Event_STS.SS_Event_STS_ID = STS_252.SS_Event_STS_ID) AND (Event_STS.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID)) INNER JOIN STS_PostOp ON STS_252.SS_Event_STS_ID = STS_PostOp.SS_Event_STS_ID
WHERE (((Event_STS.Discharge_Date) Between [Start_Date] And [End_Date]) AND ((STS_ValveSurgery.Mitral_Procedure) Not Like "No") AND ((Event_STS.CABG) Not Like 0) AND ((Event_STS.Valve)=1))
ORDER BY Demographics.Last_Name, Event_STS.Surgery_Date;

And this is my humble code for looking for the Mitral Procedure clm Aliased as Category and within the CLM the fields aliased as CAB+MVR;CAB+MVP;MVP;MVR.

SELECT Demographics.Last_Name, Event_STS.Discharge_Date, Event_STS.Surgery_Date, STS_ValveSurgery.Mitral_Procedure, Event_STS.CABG, STS_252.RoboticTechAssist, STS_ValveSurgery.Aortic_Procedure, STS_ValveSurgery.Tricuspid_Procedure, STS_ValveSurgery.Pulmonic_Procedure, Event_STS.Valve
FROM ((Demographics INNER JOIN Event_STS ON Demographics.SS_Patient_ID = Event_STS.SS_Patient_ID) INNER JOIN (STS_252 INNER JOIN STS_ValveSurgery ON STS_252.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) ON (Event_STS.SS_Event_STS_ID = STS_252.SS_Event_STS_ID) AND (Event_STS.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID)) INNER JOIN STS_PostOp ON STS_252.SS_Event_STS_ID = STS_PostOp.SS_Event_STS_ID
WHERE (((Event_STS.Discharge_Date) Between [Start_Date] And [End_Date]) AND ((STS_ValveSurgery.Mitral_Procedure) Not Like "No") AND ((Event_STS.Valve)=1))
ORDER BY Demographics.Last_Name, Event_STS.Surgery_Date;

Thank you for your time on this.

 

[rac2]

that if the Mitral Procedure field in the table cointained Replacement for the patient than that was aliased MVR.

If you mean to recode a value to another value

SELECT IIf(Mitral_Procedure = "Replacement", "MVR", "Mitral_Procedure") AS "Category"
FROM STS_ValveSurgery

The IIf() function can be used to recode values.

The expression IIf(Mitral_Procedure = "Replacement", "MVR", "Mitral_Procedure") can be given an alias by following it with AS "Category". In other words Category is an alias for the expression.

Do you wish to recode the values for the procedures and show the results of the query using the column heading Category?

 

[error123]

Hi! and thank you for responding to me.

I have created this query meanwhile. I am one step closer.
I would like to Alias my field within the category Column.
Right now it looks like this:
category CLM Mitral_Procedure CLM
CAB+Valve Reconstruction with Annuloplasty
CAB+Valve Reconstruction without Annuloplasty
Valve Replacement

SELECT STS_ValveSurgery.Mitral_Procedure, Event_STS.Valve, Event_STS.Category
FROM (Demographics INNER JOIN Event_STS ON Demographics.SS_Patient_ID = Event_STS.SS_Patient_ID) INNER JOIN (STS_252 INNER JOIN STS_ValveSurgery ON STS_252.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) ON (Event_STS.SS_Event_STS_ID = STS_ValveSurgery.SS_Event_STS_ID) AND (Event_STS.SS_Event_STS_ID = STS_252.SS_Event_STS_ID)
GROUP BY STS_ValveSurgery.Mitral_Procedure, Event_STS.Valve, Event_STS.Category
HAVING (((STS_ValveSurgery.Mitral_Procedure) Like "Reconstruction%") AND ((Event_STS.Category) Like "CAB + Valve")) OR (((STS_ValveSurgery.Mitral_Procedure) Like "Replacement")) AND ((Event_STS.Category) Like "Valve")
ORDER BY STS_ValveSurgery.Mitral_Procedure;

After Aliasing I would cal every Reconstruction As MVP
and every replacement MVR.

Thank you!!

 

[error123]

..I tried the code in Access2000 ...Like "CAB + Valve" AS CAB+MVP )
and ..Like "Replacement" As MVR))...
but it did not work.It gave me a syntax error.

Thank you!

 

[lespaul]

Do you mean that if the value of the field MitralProcedures contains the word "REPLACEMENT" you want the query to display "MVR" and if the value of the field MitralProcedures contains the word "Replacement" you want the query to display "MVP"? Does the MitralProcedures field ALWAYS contain one of those two words? If not, what do you want to display if the value doesn't have either of those words in it?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

I would cal every Reconstruction As MVP and every replacement MVR
You wanted something like this (JetSQL syntax) ?
SELECT IIf(S.Mitral_Procedure Like 'Reconstruction*', S.Mitral_Procedure, Null) AS MVP
, IIf(S.Mitral_Procedure = 'Replacement', S.Mitral_Procedure, Null) AS MVR
, E.Valve, E.Category
FROM ((Demographics AS D
INNER JOIN Event_STS AS E ON D.SS_Patient_ID = E.SS_Patient_ID)
INNER JOIN STS_252 AS T ON E.SS_Event_STS_ID = T.SS_Event_STS_ID)
INNER JOIN STS_ValveSurgery AS S ON T.SS_Event_STS_ID = S.SS_Event_STS_ID
WHERE (S.Mitral_Procedure Like 'Reconstruction*' AND E.Category = 'CAB + Valve')
OR (S.Mitral_Procedure = 'Replacement' AND E.Category = 'Valve')
ORDER BY S.Mitral_Procedure;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[error123]

Thank you PH.
It is great I am getting closer and closer..
I got it down to the point where I have these colums:

MVP MVR Category
Reconstruction with Annuloplasty CAB + Valve
Reconstruction without Annuloplasty CAB + Valve
Replacement Valve

I did hide the Valve=1 column.

Can I make it look like that my 2 column the MVP and MVR would become Category2(Aliasing it)and it would show only the words MVP or MVR (following the criteria, that MVP if Reconstruction with or without A.. and MVR if it is a Replacemet?
so it owuld look like:

Category2 Category
MVP CAB+Valve
MVP CAB+Valve
MVR Valve <--And if we can do this, is that possible to do this way, too:

Only one category field all aliased:
Category
CAB+MVP
CAB+MVP
MVR
MVP...

Thank you!!!

--Leslie, the link you have sent me was great,too!--

 

[PHV]

Well, infact you want values, not aliases !
A starting point:
SELECT IIf(S.Mitral_Procedure Like 'Reconstruction*', 'CAB+MVP'
, IIf(S.Mitral_Procedure = 'Replacement', 'MVR'
, '?')) AS AS Category2
FROM ((Demographics AS D
INNER JOIN Event_STS AS E ON D.SS_Patient_ID = E.SS_Patient_ID)
INNER JOIN STS_252 AS T ON E.SS_Event_STS_ID = T.SS_Event_STS_ID)
INNER JOIN STS_ValveSurgery AS S ON T.SS_Event_STS_ID = S.SS_Event_STS_ID
WHERE (S.Mitral_Procedure Like 'Reconstruction*' AND E.Category = 'CAB + Valve')
OR (S.Mitral_Procedure = 'Replacement' AND E.Category = 'Valve')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

for future reference, here is a FIELD alias and a TABLE alias:

SELECT Field1 As SomeOtherName FROM TableName As T

SomeOtherName is a Field alias and T is a Table alias

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[error123]

Hi Guys,

I tried many things, and I'm not getting anywhere with this...
PHV, I don't know what should I put instead of the , '?'))
What did you mean by that "?"

Thank you!

 

[lespaul]

The '?' is there as the last argument for the IIF statement, in case none of the previous conditions are true.

 

[error123]

Thanks.
I am just not sure what shoud l I put there...

 

[lespaul]

what do you want to display if Mitral_Procedure DOESN'T contain Reconstruction or Replacement?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[error123]

Hi Leslie,

I would put MVP as the Alias than, because every Mitral Procedure that is a Replacement aliased as MVP.
The other criteria never change that they all have to be CAB+Valve as a Category or Valve.
And I wanted tomake this CAB + Valve look like
Category
CAB+MVP --since it was a mitral valve surgery with CAB
CAB+MVR --since it was a mitral valve surgery with CAB but is was a Replacement
MVP --since it was mitral valve with or wothout reconstruction, and no CAB
MVR --since it was a mitral valvewith ReplacementnoCAB.

Thank you.