Distinguishing Pre-op vs Postop Patients with Subqueries more Elegantly

[TJ1010F]

I will preface this by saying I have a medical background and am a major novice at access but am trying my hand at making a database.



The context of this query is I have a database of patients where every record is a visit of theirs to a clinic where they get measurements. I need to distinguish patients that have 1) only pre operative measurements (before surgery) and 2) those with pre operative measurements AND post operative measurements (after surgery). I need to do this for patients who have had a particular diagnosis and surgery (as indicated by checkboxes) and pull all the records/visits of those patients.

Table: PtList
Preoperative Patients: Indicated by having checkbox (DiagnosisX = True) AND (SurgeryY = False). So the patient has the diagnosis I want but they haven't had surgery yet
Postoperative Patients: Indicated by having checkbox (DiagnosisX = True) AND (SurgeryY = False)
Patients are identified by "PatientID". They can have multiple visits that aren't necessarily a progression from Preop to Postop (ie they can have multiple Preop visits and not had surgery)

Basically, what I need is a way to query all the other records that are tied to the record that meets my criteria.

So far what I've come up with is just the logical flow from it, I don't yet know the proper syntax. However, it seems cumbersome and I am wondering if there is a better way. If not I am hoping for help with the code itself

For 1) Pre op only visits
Select * From PtList Where PatientID =/= True IN 'selects patients who do NOT have surgery checked off (does this work referencing "T" which appears within the statment?) from a subset of patients I already know I have a preop visit
Select PatientID From PtList As CC Where SurgeryY = True IN 'finds patients with a post op visit
Select PatientID From PtList As BB Where PatientID IN 'finds all visits from those patients that have a preop visit
Select PatientID From PtList As AA Where DiagnosisX = True AND SurgeryY = False 'finds patients with a preop visit

For 2) Pre and Post op visits:

Select * From PtList Where PatientID IN
Select PatientID From PTList As CC Where DiagosisX = True AND SurgeryY = True IN ' finds patient with a post op visit
Select PatientID From PtList As BB Where PatientID IN 'finds all visits from those patients that have a preop visit
(Select PatientID From PtList As AA Where DiagnosisX = True AND SurgeryY = False) 'finds patients with a preop visit

Also, I am a bit confused on where to us the "As" expression in this code but have put it where I think makes sense.

If all this can be answered with just some further reading on my end by all means point me in the right direction and no need to spend the time explaining.

Can not thank you guys enough for your help!

[ItsMe]

Sometimes it is easiest to apply WHERE criteria to a query from a form itself. There are a few approaches to get this done.

Are you using the Query Builder within Access to create your SQL? You can view the SQL generated by the Query Builder using the View button and selecting SQL. There should not be a need to us the AS keyword unless you need to create an Alias
http://www.w3schools.com/sql/sql_alias.asp

[TJ1010F]

Thanks for the tip on aliases.

The SQL I wrote above is basically a freestyle to convey the idea of what I need done but I was hoping for help on the specific coding to make it happen with the rough skeleton I have.

Does Query Builder = Expression builder?

[ItsMe]

...Does Query Builder = Expression builder?

Hmmm never thought about what it is called. It is not an expression because an expression is part of a statement. The operator and operand are combined to create an expression (= MyString). I would guess the Query Builder is an SQL Statement builder. But, it is more than that because it will create an Object that has a name and you can use this name, throughout your database application, to reference your Query Object. If you execute the Object, by name, you are executing the entire SQL statement saved within the object.

For your immediate purpose, I would use it to generate SQL. Use the grid at the bottom of the designer window to add things like Criteria to a SELECT statement. Use the buttons at the top of the window, within the Ribbon, to change your query to an Action Query, like a Totals query or an Append Query. Look at the SQL and identify the Operators, Keywords, and Clauses that are displayed in capital letters. Then you can do a google search on something like "FROM SQL w3Schools" or "SELECT SQL w3Schools" to learn more about the SQL you generated.

Also, when I look at your original post, I think to myself I might handle some of this at the Form level. But, if you are in the beginning stages, it is best to concentrate on your tables and queries. The first use of forms should be to test the waters at data entry. Then, you should revisit your tables and queries to do some redesigning.

Make sure your tables are in order and you have identified your entities.
https://www.youtube.com/watch?v=-fQ-bRllhXc


Like I said, it is probably too early for you to think about forms. But, here is some food for thought.
https://www.youtube.com/watch?v=N0X8Hg-Sm6A