Query for Acess 1

[error123]

Hi,

Can you tell me how to create a query that calculates the total number and percentage of filed_test1?

I started with the last_name field than I added another field where I wanted to use the count(inserted a Total), but than I got this error msg: "You tired to execute a query that does not include the specified expression 'Last Name ' as part of the aggregate function.

Thank you a bunch!

 

[PHV]

What is your actual SQL code ?

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

 

[kjv1611]

error123,

To get the SQL code, (if you do not already know):
While your query is pulled up, select "SQL View" from where you would normally switch between "Design View" and "Datasheet View". You'll have to click the drop down arrow to get it to show. Then simply paste what shows in that screen here in your thread, assuming there would be nothing considered confidential in your query (the fields themselves, that is).

 

[error123]

SELECT Count(Event_Cath.Cath_Number) AS CountOfCath_Number, Demographics.Patient_ID, Cath_ACC.PCIProcedure, Event_Cath.Cath_Attending, Event_Cath.PreProcPhysioLVEFPercent
FROM (Cath_ACC INNER JOIN Cath_Registry_Common_2 ON Cath_ACC.SS_Event_Cath_ID = Cath_Registry_Common_2.SS_Event_Cath_ID) INNER JOIN (Demographics INNER JOIN Event_Cath ON Demographics.SS_Patient_ID = Event_Cath.SS_Patient_ID) ON Cath_Registry_Common_2.SS_Event_Cath_ID = Event_Cath.SS_Event_Cath_ID
WHERE (((Cath_ACC.EFMeasured)>0))
GROUP BY Demographics.Patient_ID, Cath_ACC.PCIProcedure, Cath_Registry_Common_2.ACCEFMethod, Event_Cath.Cath_Attending, Event_Cath.PreProcPhysioLVEFPercent
HAVING (((Cath_ACC.PCIProcedure)=1));


--And I can not see the actual "count how many events I had" neither.
Thank you!

 

[lespaul]

[tt]
SELECT Count(Event_Cath.Cath_Number) AS CountOfCath_Number, Demographics.Patient_ID, Cath_ACC.PCIProcedure, Event_Cath.Cath_Attending, Event_Cath.PreProcPhysioLVEFPercent
FROM (Cath_ACC INNER JOIN Cath_Registry_Common_2 ON Cath_ACC.SS_Event_Cath_ID = Cath_Registry_Common_2.SS_Event_Cath_ID) INNER JOIN (Demographics INNER JOIN Event_Cath ON Demographics.SS_Patient_ID = Event_Cath.SS_Patient_ID) ON Cath_Registry_Common_2.SS_Event_Cath_ID = Event_Cath.SS_Event_Cath_ID
WHERE Cath_ACC.EFMeasured>0 AND Cath_ACC.PCIProcedure=1
GROUP BY Demographics.Patient_ID, Cath_ACC.PCIProcedure, Event_Cath.Cath_Attending, Event_Cath.PreProcPhysioLVEFPercent;[/tt]

Here are explanations of GROUP BY and HAVING clauses:
Thread701-1258387 (GROUP BY)
Thread701-1262760 (HAVING)

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]

Thank you!!!!!
It worked!

 

[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.

 

[Golom]

error123

This is a new question so it should be in a new thread.

Click "Start New Thread" at the top of the thread list, give it a title, and repost your issue there.

 

[error123]

Thank you, I did that.