If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
excluding values in a query
I am trying to identify a count of clients who were present in a facility as
of 6/30/08. Each client has one or more admission episodes defined by an admit_date and a discharge_date. One admission episode can have one or more records associated with it. Clients whose cases are open, i.e. who have not been discharged are assigned a discharge date of 6/6/2079. I believe the universe of records includes three scenarios: 1) To identify a count of clients in a facility on 6/30/08, I first created a query to exclude all admission episodes with a discharge_date less than 6/30/08 using the following criteria against the discharge_date: #6/30/2008#. 2) I also created a query to identify all episodes with a discharge of greater than 6/30/08 using the following criteria against the discharge_date field: =#7/1/2008# And #6/6/2079#. 3) I have not been successful identifying only those admission episodes with a discharge_date of 6/6/2079. How do I limit the query to only shows admission episodes with a discharge_date of 6/6/2079? I tried to skirt the problem by creating a query to only show admission episodes that were NOT equal to query 1. For instance, in a query I have added the field "ID Admissions Episode". I entered the following criteria for the field: Not [qry Discharges 6/30/08 #2]![Admission Episode] . Alternatively, I tried the same criteria with . Using one or the other criteria returned 0 results for the query. Any suggestions on how I can exclude Admissions episodes that have a discharge date of less than 6/30/08? Thanks, |
#2
|
|||
|
|||
excluding values in a query
Isn't it simply a question of identifying those rows where #6/30/2008# falls
between the admission and discharge dates? Count the distinct patients like so: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date); Ken Sheridan Stafford, England "Daniel Collison" wrote: I am trying to identify a count of clients who were present in a facility as of 6/30/08. Each client has one or more admission episodes defined by an admit_date and a discharge_date. One admission episode can have one or more records associated with it. Clients whose cases are open, i.e. who have not been discharged are assigned a discharge date of 6/6/2079. I believe the universe of records includes three scenarios: 1) To identify a count of clients in a facility on 6/30/08, I first created a query to exclude all admission episodes with a discharge_date less than 6/30/08 using the following criteria against the discharge_date: #6/30/2008#. 2) I also created a query to identify all episodes with a discharge of greater than 6/30/08 using the following criteria against the discharge_date field: =#7/1/2008# And #6/6/2079#. 3) I have not been successful identifying only those admission episodes with a discharge_date of 6/6/2079. How do I limit the query to only shows admission episodes with a discharge_date of 6/6/2079? I tried to skirt the problem by creating a query to only show admission episodes that were NOT equal to query 1. For instance, in a query I have added the field "ID Admissions Episode". I entered the following criteria for the field: Not [qry Discharges 6/30/08 #2]![Admission Episode] . Alternatively, I tried the same criteria with . Using one or the other criteria returned 0 results for the query. Any suggestions on how I can exclude Admissions episodes that have a discharge date of less than 6/30/08? Thanks, |
#3
|
|||
|
|||
excluding values in a query
Ken:
Thanks for your quick response. Unfortunately, it's not as simple as it appears. Every admission episode includes one or more records. The majority of those records include an "open" discharge date of 6/7/2079. For patients that have actually been discharged, a record is included which shows the actual discharge date, i.e. 5/4/08 or 6/2/08. The query that results from your sshows virtually all client records, because the vast majority of clients have admission episode records that include 6/6/2079. Any thoughts? "Ken Sheridan" wrote: Isn't it simply a question of identifying those rows where #6/30/2008# falls between the admission and discharge dates? Count the distinct patients like so: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date); Ken Sheridan Stafford, England "Daniel Collison" wrote: I am trying to identify a count of clients who were present in a facility as of 6/30/08. Each client has one or more admission episodes defined by an admit_date and a discharge_date. One admission episode can have one or more records associated with it. Clients whose cases are open, i.e. who have not been discharged are assigned a discharge date of 6/6/2079. I believe the universe of records includes three scenarios: 1) To identify a count of clients in a facility on 6/30/08, I first created a query to exclude all admission episodes with a discharge_date less than 6/30/08 using the following criteria against the discharge_date: #6/30/2008#. 2) I also created a query to identify all episodes with a discharge of greater than 6/30/08 using the following criteria against the discharge_date field: =#7/1/2008# And #6/6/2079#. 3) I have not been successful identifying only those admission episodes with a discharge_date of 6/6/2079. How do I limit the query to only shows admission episodes with a discharge_date of 6/6/2079? I tried to skirt the problem by creating a query to only show admission episodes that were NOT equal to query 1. For instance, in a query I have added the field "ID Admissions Episode". I entered the following criteria for the field: Not [qry Discharges 6/30/08 #2]![Admission Episode] . Alternatively, I tried the same criteria with . Using one or the other criteria returned 0 results for the query. Any suggestions on how I can exclude Admissions episodes that have a discharge date of less than 6/30/08? Thanks, |
#4
|
|||
|
|||
excluding values in a query
Would in not be better to update all rows for the patient in question where
the discharge date = the default of 7 June 2079 to the actual discharge date when a real one is entered? It would just need a simple update query executed in the form's AfterUpdate event procedure. But with the present practice don't you just need: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date AND discharge_date #06/7/2079#); Ken Sheridan Stafford, England "Daniel Collison" wrote: Ken: Thanks for your quick response. Unfortunately, it's not as simple as it appears. Every admission episode includes one or more records. The majority of those records include an "open" discharge date of 6/7/2079. For patients that have actually been discharged, a record is included which shows the actual discharge date, i.e. 5/4/08 or 6/2/08. The query that results from your sshows virtually all client records, because the vast majority of clients have admission episode records that include 6/6/2079. Any thoughts? "Ken Sheridan" wrote: Isn't it simply a question of identifying those rows where #6/30/2008# falls between the admission and discharge dates? Count the distinct patients like so: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date); Ken Sheridan Stafford, England "Daniel Collison" wrote: I am trying to identify a count of clients who were present in a facility as of 6/30/08. Each client has one or more admission episodes defined by an admit_date and a discharge_date. One admission episode can have one or more records associated with it. Clients whose cases are open, i.e. who have not been discharged are assigned a discharge date of 6/6/2079. I believe the universe of records includes three scenarios: 1) To identify a count of clients in a facility on 6/30/08, I first created a query to exclude all admission episodes with a discharge_date less than 6/30/08 using the following criteria against the discharge_date: #6/30/2008#. 2) I also created a query to identify all episodes with a discharge of greater than 6/30/08 using the following criteria against the discharge_date field: =#7/1/2008# And #6/6/2079#. 3) I have not been successful identifying only those admission episodes with a discharge_date of 6/6/2079. How do I limit the query to only shows admission episodes with a discharge_date of 6/6/2079? I tried to skirt the problem by creating a query to only show admission episodes that were NOT equal to query 1. For instance, in a query I have added the field "ID Admissions Episode". I entered the following criteria for the field: Not [qry Discharges 6/30/08 #2]![Admission Episode] . Alternatively, I tried the same criteria with . Using one or the other criteria returned 0 results for the query. Any suggestions on how I can exclude Admissions episodes that have a discharge date of less than 6/30/08? Thanks, |
#5
|
|||
|
|||
excluding values in a query
Ken:
Thanks again for your assistance. FYI, I've limited programming experience, so I'm struggling a bit with the SQL statements. The database includes no forms or reports. I created it merely to massage a dataset extract from another source. I am trying to obtain client counts associated with different points in time. For this particular scenario, I am trying to identify all clients who were hospitalized on 6/30/08. Perhaps it would make sense to create an update query to update all record discharge_dates associated with admission episode with the actual discharge_date...but I'm at a loss as to how to go about doing this. Thanks...any assistance would be appreciated. (By the way, I noticed that you live in England. I was in London for several days last November...and missed Patrick Stewart in a production of "Othello". Had a great time though. I'd like to return someday and hike along the some of the coastal trails! DC) "Ken Sheridan" wrote: Would in not be better to update all rows for the patient in question where the discharge date = the default of 7 June 2079 to the actual discharge date when a real one is entered? It would just need a simple update query executed in the form's AfterUpdate event procedure. But with the present practice don't you just need: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date AND discharge_date #06/7/2079#); Ken Sheridan Stafford, England "Daniel Collison" wrote: Ken: Thanks for your quick response. Unfortunately, it's not as simple as it appears. Every admission episode includes one or more records. The majority of those records include an "open" discharge date of 6/7/2079. For patients that have actually been discharged, a record is included which shows the actual discharge date, i.e. 5/4/08 or 6/2/08. The query that results from your sshows virtually all client records, because the vast majority of clients have admission episode records that include 6/6/2079. Any thoughts? "Ken Sheridan" wrote: Isn't it simply a question of identifying those rows where #6/30/2008# falls between the admission and discharge dates? Count the distinct patients like so: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date); Ken Sheridan Stafford, England "Daniel Collison" wrote: I am trying to identify a count of clients who were present in a facility as of 6/30/08. Each client has one or more admission episodes defined by an admit_date and a discharge_date. One admission episode can have one or more records associated with it. Clients whose cases are open, i.e. who have not been discharged are assigned a discharge date of 6/6/2079. I believe the universe of records includes three scenarios: 1) To identify a count of clients in a facility on 6/30/08, I first created a query to exclude all admission episodes with a discharge_date less than 6/30/08 using the following criteria against the discharge_date: #6/30/2008#. 2) I also created a query to identify all episodes with a discharge of greater than 6/30/08 using the following criteria against the discharge_date field: =#7/1/2008# And #6/6/2079#. 3) I have not been successful identifying only those admission episodes with a discharge_date of 6/6/2079. How do I limit the query to only shows admission episodes with a discharge_date of 6/6/2079? I tried to skirt the problem by creating a query to only show admission episodes that were NOT equal to query 1. For instance, in a query I have added the field "ID Admissions Episode". I entered the following criteria for the field: Not [qry Discharges 6/30/08 #2]![Admission Episode] . Alternatively, I tried the same criteria with . Using one or the other criteria returned 0 results for the query. Any suggestions on how I can exclude Admissions episodes that have a discharge date of less than 6/30/08? Thanks, |
#6
|
|||
|
|||
excluding values in a query
Daniel:
Its not advisable to work directly with tables; forms and reports should be the interfaces with the data. The former give you control, the latter enable better presentation. Access provides wizards for you to create simple forms and reports, and these can be adapted quite easily. We can always help of course. I think your problem essentially stems from flaws in the 'logical model'; that's the way the data is structured in related tables. As you are using data imported from elsewhere you probably can't do a lot to remedy that, however, but you can mitigate it. A relational database uses tables, each of which represents an 'entity type'. The tables' columns represent attributes of the entity type, and different entity types are related to each other via 'keys', columns with values which map rows in one table to another. I get the impression that you are dealing with one table; is that right? Obviously 'Patients' is one entity type. 'Admission Events' seems to be another. You say that there are multiple rows per admission event, so there is a third entity type; I'll call it 'Admission Sub-events' but I'm sure there's a more accurate descriptive term. A fundamental principle of the database relational model is that there is no 'redundancy', i.e. each 'fact' is contained once and once only in the database, e.g. the fact that patient 42's first name is Ken and last name is Sheridan would be stored once only, in a row in a Patients table. This would prevent inconsistent data being entered, e.g. me being Ken in one row and Kenneth in another (databases are pretty dumb so don't know that ken and Kenneth are one and the same). So in a properly structured database you'd have there tables Patients, AdmissionEvents and AdmissionSubEvents. The first two are related via PatientID columns, the latter two by AdmissionEventID columns. Now, as far as I can gather a discharge date is an attribute of an admission event, rather than of an admission sub-event. Do this would be a column in the AdmissionEvents table, not the AdmissionSubEvents table, i.e. this 'fact' is stored only once. I'm not sure from my limited knowledge of the reality behind your database which table admission date would be in; you can judge that for yourself. You could 'decompose' your table into three tables. Its not too difficult to do in fact via a few 'append' and 'update' queries. Or you could try and get the single table into a consistent sate, so you don't have any contradictory values in it. This is what I was suggesting by means of an update query to get all the discharge dates per patient per admission event the same. This would eliminate the contradictions of having some rows with the default discharge date of 7 June 2079, and some with the actual discharge date. To automate this you'd need to create a form based on your table (which I'm calling Admissions, so you'll need to change some things below to reflect the real name of the table). The form wizard will do this for you and create a nice attractive form to work with. What you then need to do is execute an update query which changes the discharge dates for the current patient when you update the data in the form, i.e. when you enter a real discharge date. Before we look at how that's done in the form lets first consider the update query. I'm going to give you this in SQL rather than try and describe how to do it in design view, so what you'll need to do to create the query is open the query designer in the usual way, don't add any table, switch to SQL view and then paste in the SQL below in place of what's there already. You'll then have to amend it so that the table and column (field) names are your actual ones. What the query will do is reference the form you created to get the patient and the discharge date, so I'm going to assume you've saved the form as frmAdmissions, that there's a text box control on it bound to a column called PatientID (table and column names are not case-sensitive BTW) in the Admissions table and that this is Text data type, and that there is also a text box bound to a column Discharge_Date in the same table and this is Date/Time data type. In each case the name of the text box is the same as the underlying column to which its bound. When a query references a control on a form this is a 'parameter'. Its always a good idea to declare parameters at the start of a query so there is no confusion over their data type (a date entered as 10/24/2008 could be misinterpreted as an arithmetical expression otherwise). So the update query would be like this: PARAMETERS Forms![frmAdmissions]![PatientID] TEXT, Forms![frmAdmissions]![Discharge_Date] DATETIME; UPDATE [Admissions] ([Discharge_Date]) SET [Discharge_Date] = Forms![frmAdmissions]![Discharge_Date] WHERE [PatientID] = Forms![frmAdmissions]![PatientID]; Amend the table/column names as necessary and save the query as qryDischargeDatesUpdate for instance. One qualm I have about this is that a patient could be represented more than once in the table, not as different admission sub-events, but as different admission events. If this were the case there would need to be some means of referencing the admission event rather than the patient so that the rows for that admission event only were updated not all rows for the patient. However, putting that to one side for the moment lets go back to the frmAdmissions form. All that's necessary here is to add some code to the form's AfterUpdate event procedure to update all rows for the patient if a date other than 7 June 2079 has been entered as the discharge date. To do this select open the form in design view and open its properties sheet if its not already open. Then select the form by clicking on the small black square in its top left corner. Then select the AfterUpdate event property in the properties sheet. Click on the 'build' button; that's the one on the right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA window will open at the event procedure with the first and last lines already in place. Enter the following lines of code between these two existing lines: On Error Goto Err_Handler ' temporarily turn off warning messages DoCmd.SetWarnings False ' execute the update query DoCmd.OpenQuery "qryDischargeDatesUpdate" Exit_He ' turn warning messages back on DoCmd.SetWarnings True Exit Sub Err_Handler MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here Once you have all the discharge dates per patient consistent in all rows then the first query I sent you should work in that it will return all patients where the date you are interested in falls between the admission and discharge dates. You can amend the query so that you can enter any date as a parameter at runtime rather than hard coding a date in the query: PARAMETERS [Enter date:] DATETIME; SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE [Enter date:] BETWEEN admit_date AND discharge_date); Create the query in the same way as I described above. Finally, before you try anything like this its absolutely imperative that you BACK UP the table until you are satisfied that its working properly. We are not too far from Stratford here, so can get to see the Royal Shakespeare Company performances relatively easily. In fact tourists occasionally confuse Stratford and Stafford and turn up here looking for Shakespeare's birthplace! Ken Sheridan Stafford, England "Daniel Collison" wrote: Ken: Thanks again for your assistance. FYI, I've limited programming experience, so I'm struggling a bit with the SQL statements. The database includes no forms or reports. I created it merely to massage a dataset extract from another source. I am trying to obtain client counts associated with different points in time. For this particular scenario, I am trying to identify all clients who were hospitalized on 6/30/08. Perhaps it would make sense to create an update query to update all record discharge_dates associated with admission episode with the actual discharge_date...but I'm at a loss as to how to go about doing this. Thanks...any assistance would be appreciated. (By the way, I noticed that you live in England. I was in London for several days last November...and missed Patrick Stewart in a production of "Othello". Had a great time though. I'd like to return someday and hike along the some of the coastal trails! DC) "Ken Sheridan" wrote: Would in not be better to update all rows for the patient in question where the discharge date = the default of 7 June 2079 to the actual discharge date when a real one is entered? It would just need a simple update query executed in the form's AfterUpdate event procedure. But with the present practice don't you just need: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date AND discharge_date #06/7/2079#); Ken Sheridan Stafford, England "Daniel Collison" wrote: Ken: Thanks for your quick response. Unfortunately, it's not as simple as it appears. Every admission episode includes one or more records. The majority of those records include an "open" discharge date of 6/7/2079. For patients that have actually been discharged, a record is included which shows the actual discharge date, i.e. 5/4/08 or 6/2/08. The query that results from your sshows virtually all client records, because the vast majority of clients have admission episode records that include 6/6/2079. Any thoughts? "Ken Sheridan" wrote: Isn't it simply a question of identifying those rows where #6/30/2008# falls between the admission and discharge dates? Count the distinct patients like so: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date); Ken Sheridan Stafford, England "Daniel Collison" wrote: I am trying to identify a count of clients who were present in a facility as of 6/30/08. Each client has one or more admission episodes defined by an admit_date and a discharge_date. One admission episode can have one or more records associated with it. Clients whose cases are open, i.e. who have not been discharged are assigned a discharge date of 6/6/2079. I believe the universe of records includes three scenarios: 1) To identify a count of clients in a facility on 6/30/08, I first created a query to exclude all admission episodes with a discharge_date less than 6/30/08 using the following criteria against the discharge_date: #6/30/2008#. 2) I also created a query to identify all episodes with a discharge of greater than 6/30/08 using the following criteria against the discharge_date field: =#7/1/2008# And #6/6/2079#. 3) I have not been successful identifying only those admission episodes with a discharge_date of 6/6/2079. How do I limit the query to only shows admission episodes with a discharge_date of 6/6/2079? I tried to skirt the problem by creating a query to only show admission episodes that were NOT equal to query 1. For instance, in a query I have added the field "ID Admissions Episode". I entered the following criteria for the field: Not [qry Discharges 6/30/08 #2]![Admission Episode] . Alternatively, I tried the same criteria with . Using one or the other criteria returned 0 results for the query. Any suggestions on how I can exclude Admissions episodes that have a discharge date of less than 6/30/08? Thanks, |
#7
|
|||
|
|||
excluding values in a query
Ken:
Thanks for your detailed reply. I greatly appreciate your efforts on this matter. The goal of this project is to provide summary totals against a dataset over which we have no control. The report related to this project is due in one week. I've spoken with my co-workers and we've come up with a workaround solution that 1) provides us with a high degree of confidence in the numbers; and 2) can be executed in a relatively short period of time. Again, thanks for your assistance...it's much appreciated. Daniel PS -- I look forward to getting back to England within the next year or so. DC "Ken Sheridan" wrote: Daniel: Its not advisable to work directly with tables; forms and reports should be the interfaces with the data. The former give you control, the latter enable better presentation. Access provides wizards for you to create simple forms and reports, and these can be adapted quite easily. We can always help of course. I think your problem essentially stems from flaws in the 'logical model'; that's the way the data is structured in related tables. As you are using data imported from elsewhere you probably can't do a lot to remedy that, however, but you can mitigate it. A relational database uses tables, each of which represents an 'entity type'. The tables' columns represent attributes of the entity type, and different entity types are related to each other via 'keys', columns with values which map rows in one table to another. I get the impression that you are dealing with one table; is that right? Obviously 'Patients' is one entity type. 'Admission Events' seems to be another. You say that there are multiple rows per admission event, so there is a third entity type; I'll call it 'Admission Sub-events' but I'm sure there's a more accurate descriptive term. A fundamental principle of the database relational model is that there is no 'redundancy', i.e. each 'fact' is contained once and once only in the database, e.g. the fact that patient 42's first name is Ken and last name is Sheridan would be stored once only, in a row in a Patients table. This would prevent inconsistent data being entered, e.g. me being Ken in one row and Kenneth in another (databases are pretty dumb so don't know that ken and Kenneth are one and the same). So in a properly structured database you'd have there tables Patients, AdmissionEvents and AdmissionSubEvents. The first two are related via PatientID columns, the latter two by AdmissionEventID columns. Now, as far as I can gather a discharge date is an attribute of an admission event, rather than of an admission sub-event. Do this would be a column in the AdmissionEvents table, not the AdmissionSubEvents table, i.e. this 'fact' is stored only once. I'm not sure from my limited knowledge of the reality behind your database which table admission date would be in; you can judge that for yourself. You could 'decompose' your table into three tables. Its not too difficult to do in fact via a few 'append' and 'update' queries. Or you could try and get the single table into a consistent sate, so you don't have any contradictory values in it. This is what I was suggesting by means of an update query to get all the discharge dates per patient per admission event the same. This would eliminate the contradictions of having some rows with the default discharge date of 7 June 2079, and some with the actual discharge date. To automate this you'd need to create a form based on your table (which I'm calling Admissions, so you'll need to change some things below to reflect the real name of the table). The form wizard will do this for you and create a nice attractive form to work with. What you then need to do is execute an update query which changes the discharge dates for the current patient when you update the data in the form, i.e. when you enter a real discharge date. Before we look at how that's done in the form lets first consider the update query. I'm going to give you this in SQL rather than try and describe how to do it in design view, so what you'll need to do to create the query is open the query designer in the usual way, don't add any table, switch to SQL view and then paste in the SQL below in place of what's there already. You'll then have to amend it so that the table and column (field) names are your actual ones. What the query will do is reference the form you created to get the patient and the discharge date, so I'm going to assume you've saved the form as frmAdmissions, that there's a text box control on it bound to a column called PatientID (table and column names are not case-sensitive BTW) in the Admissions table and that this is Text data type, and that there is also a text box bound to a column Discharge_Date in the same table and this is Date/Time data type. In each case the name of the text box is the same as the underlying column to which its bound. When a query references a control on a form this is a 'parameter'. Its always a good idea to declare parameters at the start of a query so there is no confusion over their data type (a date entered as 10/24/2008 could be misinterpreted as an arithmetical expression otherwise). So the update query would be like this: PARAMETERS Forms![frmAdmissions]![PatientID] TEXT, Forms![frmAdmissions]![Discharge_Date] DATETIME; UPDATE [Admissions] ([Discharge_Date]) SET [Discharge_Date] = Forms![frmAdmissions]![Discharge_Date] WHERE [PatientID] = Forms![frmAdmissions]![PatientID]; Amend the table/column names as necessary and save the query as qryDischargeDatesUpdate for instance. One qualm I have about this is that a patient could be represented more than once in the table, not as different admission sub-events, but as different admission events. If this were the case there would need to be some means of referencing the admission event rather than the patient so that the rows for that admission event only were updated not all rows for the patient. However, putting that to one side for the moment lets go back to the frmAdmissions form. All that's necessary here is to add some code to the form's AfterUpdate event procedure to update all rows for the patient if a date other than 7 June 2079 has been entered as the discharge date. To do this select open the form in design view and open its properties sheet if its not already open. Then select the form by clicking on the small black square in its top left corner. Then select the AfterUpdate event property in the properties sheet. Click on the 'build' button; that's the one on the right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA window will open at the event procedure with the first and last lines already in place. Enter the following lines of code between these two existing lines: On Error Goto Err_Handler ' temporarily turn off warning messages DoCmd.SetWarnings False ' execute the update query DoCmd.OpenQuery "qryDischargeDatesUpdate" Exit_He ' turn warning messages back on DoCmd.SetWarnings True Exit Sub Err_Handler MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here Once you have all the discharge dates per patient consistent in all rows then the first query I sent you should work in that it will return all patients where the date you are interested in falls between the admission and discharge dates. You can amend the query so that you can enter any date as a parameter at runtime rather than hard coding a date in the query: PARAMETERS [Enter date:] DATETIME; SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE [Enter date:] BETWEEN admit_date AND discharge_date); Create the query in the same way as I described above. Finally, before you try anything like this its absolutely imperative that you BACK UP the table until you are satisfied that its working properly. We are not too far from Stratford here, so can get to see the Royal Shakespeare Company performances relatively easily. In fact tourists occasionally confuse Stratford and Stafford and turn up here looking for Shakespeare's birthplace! Ken Sheridan Stafford, England "Daniel Collison" wrote: Ken: Thanks again for your assistance. FYI, I've limited programming experience, so I'm struggling a bit with the SQL statements. The database includes no forms or reports. I created it merely to massage a dataset extract from another source. I am trying to obtain client counts associated with different points in time. For this particular scenario, I am trying to identify all clients who were hospitalized on 6/30/08. Perhaps it would make sense to create an update query to update all record discharge_dates associated with admission episode with the actual discharge_date...but I'm at a loss as to how to go about doing this. Thanks...any assistance would be appreciated. (By the way, I noticed that you live in England. I was in London for several days last November...and missed Patrick Stewart in a production of "Othello". Had a great time though. I'd like to return someday and hike along the some of the coastal trails! DC) "Ken Sheridan" wrote: Would in not be better to update all rows for the patient in question where the discharge date = the default of 7 June 2079 to the actual discharge date when a real one is entered? It would just need a simple update query executed in the form's AfterUpdate event procedure. But with the present practice don't you just need: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date AND discharge_date #06/7/2079#); Ken Sheridan Stafford, England "Daniel Collison" wrote: Ken: Thanks for your quick response. Unfortunately, it's not as simple as it appears. Every admission episode includes one or more records. The majority of those records include an "open" discharge date of 6/7/2079. For patients that have actually been discharged, a record is included which shows the actual discharge date, i.e. 5/4/08 or 6/2/08. The query that results from your sshows virtually all client records, because the vast majority of clients have admission episode records that include 6/6/2079. Any thoughts? "Ken Sheridan" wrote: Isn't it simply a question of identifying those rows where #6/30/2008# falls between the admission and discharge dates? Count the distinct patients like so: SELECT COUNT(*) FROM (SELECT DISTINCT patientID FROM Admissions WHERE #6/30/2008# BETWEEN admit_date AND discharge_date); Ken Sheridan Stafford, England "Daniel Collison" wrote: I am trying to identify a count of clients who were present in a facility as of 6/30/08. Each client has one or more admission episodes defined by an admit_date and a discharge_date. One admission episode can have one or more records associated with it. Clients whose cases are open, i.e. who have not been discharged are assigned a discharge date of 6/6/2079. I believe the universe of records includes three scenarios: 1) To identify a count of clients in a facility on 6/30/08, I first created a query to exclude all admission episodes with a discharge_date less than 6/30/08 using the following criteria against the discharge_date: #6/30/2008#. 2) I also created a query to identify all episodes with a discharge of greater than 6/30/08 using the following criteria against the discharge_date field: =#7/1/2008# And #6/6/2079#. 3) I have not been successful identifying only those admission episodes with a discharge_date of 6/6/2079. How do I limit the query to only shows admission episodes with a discharge_date of 6/6/2079? I tried to skirt the problem by creating a query to only show admission episodes that were NOT equal to query 1. For instance, in a query I have added the field "ID Admissions Episode". I entered the following criteria for the field: Not [qry Discharges 6/30/08 #2]![Admission Episode] . Alternatively, I tried the same criteria with . Using one or the other criteria returned 0 results for the query. Any suggestions on how I can exclude Admissions episodes that have a discharge date of less than 6/30/08? Thanks, |
Thread Tools | |
Display Modes | |
|
|