UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Simple Query Question, Access 2016    
 
   
jklinephd
post Jul 10 2019, 11:06 AM
Post#1



Posts: 75
Joined: 11-September 07
From: Missouri


I am working with a series of tables that are generated daily by a large state agency using that agency’s huge database. The tables are designed to make it easier for facilities to generate useful reports and data. So, these tables are created through joins, etc, to help the end user. I have no control over this. 
With that in mind. I am creating a simple query that uses two tables. Table 1 – [ClientData] which has the fields “ID”, “Name”, “Admission Date” “Discharge Date” and “Ward” (where they are staying). Table 2 = [Diagnosis] is joined by me, to [ClientData] using the “ID” field. Table [Diagnosis] has all the client’s diagnoses. It has 3 fields I am interested in: “ID”, “Diagnosis Long Description”, “Principal”, and “Diagnosis Present on Admission.”

So here are the tables

[Diagnosis]
“ID”
“Diagnosis Long Description” (Text)
“Principal” (Y or N) (while each “ID” can have multiple records with different diagnoses, only one of those diagnoses can be listed as “Y” for this field)
“Diagnosis Present on Admission.” (Text – “Principal Admitting” or “No” (while each “ID” can have multiple records with different diagnoses, only one of those diagnoses can be listed as “Principal Admitting” for this field)

[ClientData]
“ID”
“Name”
“Admission Date”
“Discharge Date”
“Ward” (Text like B02, B03, etc)

I can easily create a query that has every client admitted between two dates to “ward” B02. And I can easily list their Principal Diagnosis. A few clients have different “Principal” diagnosis than their “Diagnosis Present on Admission.” So I want my query result to look like this:
ID | Name | Admission Date | Discharge Date | Diagnosis Long Description | Principal | Diagnosis Present on Admission |

9999|John Smith|1/1/2019|3/1/2019|Depression|Y|No|Alcohol Use Disorder (the “diagnosis long description” of whichever diagnosis was marked as “Principal Diagnosis” at admission

So basically, some clients have a diagnosis that changes after they get here, and I want to list that in a separate field of the query or as a separate line of the query output. I tried to create an expression, but was unable to get it to work.

I am open to a query that lists a person twice if their diagnosis changed with their second listing being either the admitting diagnosis or the current principal diagnosis.
Like:

9999|John Smith|1/1/2019|3/1/2019|Depression|Y|No|
9999|John Smith|1/1/2019|3/1/2019|Alcohol Use Disorder|No|Principal Diagnosis|

Does this make sense? Suggestions?

Thanks

Jeff

Go to the top of the page
 
theDBguy
post Jul 10 2019, 11:15 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,081
Joined: 19-June 07
From: SunnySandyEggo


Hi Jeff. Would you be able to mock up a sample database with dummy data, so we can better understand what you're trying to accomplish? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jklinephd
post Jul 10 2019, 11:43 AM
Post#3



Posts: 75
Joined: 11-September 07
From: Missouri


I don't think so. I am linked to these tables through OBDC and none of the data is local (and I cannot download the data). I'll give it a shot, but I am not hopeful.
Go to the top of the page
 
theDBguy
post Jul 10 2019, 11:45 AM
Post#4


Access Wiki and Forums Moderator
Posts: 76,081
Joined: 19-June 07
From: SunnySandyEggo


Well, you don't have to use the same data from the actual database. You can just give us a sample db simulating the actual one using local tables with the same fields. That way, we can do some tests for you to find out the best way to get the result you want.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
jklinephd
post Jul 11 2019, 09:03 AM
Post#5



Posts: 75
Joined: 11-September 07
From: Missouri


I have attached a sample database that corresponds to the real database and my original post. It does not have every field that my actual database has, but it has all the ones that I need for the query.

Clients can have multiple diagnoses. Only one is currently principal (Field "Principal" = Y or N). Each client also has one diagnosis that is principal at admission, which can be different than the current principal diagnosis or the same. (Field "Diagnosis Present on Admission" = Principal Admitting or No)

The sample has a query in it. That is a start. I can easily create a query that has every client admitted between two dates to a selection of “wards” B02 and list their current principal diagnosis.

Here is my issue, some clients have a principal diagnosis that changes after they get here, and I want to list that in a separate field of the query or as a separate line of the query output. I tried to create an expression, but was unable to get it to work.

So I want my query result to look like this:

ID | Name | Admission Date | Discharge Date | Current Principal- "Diagnosis Long Description" | Principal "Diagnosis Long Description" Present on Admission if different than current principal diagnosis

9999|John Smith|1/1/2019|3/1/2019|Depression|Schizophrenia|
9998|Jim Doe|1/2/2019|3/2/2019|Schizophrenia|


John''s admitting diagnosis was Schizophrenia and his current principal diagnosis is Depression.
Jim's Admitting principal diagnosis and current principal diagnosis is the same.

I am open to a query that lists a person twice if their diagnosis changed with their second listing being either the admitting diagnosis or the current principal diagnosis.
Like:

9999|John Smith|1/1/2019|3/1/2019|Depression|
9999|John Smith|1/1/2019|3/1/2019|Schizophrenia|Principal At Admission

Make Sense?
This post has been edited by jklinephd: Jul 11 2019, 09:04 AM
Attached File(s)
Attached File  sampleDCT.zip ( 22.6K )Number of downloads: 4
 
Go to the top of the page
 
theDBguy
post Jul 11 2019, 10:04 AM
Post#6


Access Wiki and Forums Moderator
Posts: 76,081
Joined: 19-June 07
From: SunnySandyEggo


Hi. Is there any way you can explain it again using the data in your sample db rather than use John Smith and Jim Doe, who I don't see in the table, so I can't really follow how to translate what you just described into a matching result? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
theDBguy
post Jul 11 2019, 10:14 AM
Post#7


Access Wiki and Forums Moderator
Posts: 76,081
Joined: 19-June 07
From: SunnySandyEggo


Hi. I gave it a shot anyway. Try out this SQL and see if this is what you meant.
CODE
SELECT ClientData.ID, ClientData.[First Name], ClientData.[Last Name], ClientData.[Admission Date], ClientData.[Discharge Date], ClientData.Ward, Diagnosis.[Diagnosis Long Description], Diagnosis.Principal, Diagnosis.[Diagnosis Present on Admission], DLookUp("[Diagnosis Long Description]","Diagnosis","[Diagnosis Present on Admission]='Principal Admitting' AND ID=" & [ClientData].[ID]) AS Admission
FROM ClientData INNER JOIN Diagnosis ON ClientData.ID = Diagnosis.ID
WHERE (((ClientData.[Admission Date])>#1/1/2019#) AND ((ClientData.[Discharge Date]) Is Null) AND ((ClientData.Ward)="B05" Or (ClientData.Ward)="B06") AND ((Diagnosis.Principal)="Y"));

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Jeff B.
post Jul 11 2019, 11:03 AM
Post#8


UtterAccess VIP
Posts: 10,271
Joined: 30-April 10
From: Pacific NorthWet


Just a side mention … it appears your db is working with patient-related data. If so, there may be regulatory restraints (HIPAA in US) that apply.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
jklinephd
post Jul 11 2019, 11:16 AM
Post#9



Posts: 75
Joined: 11-September 07
From: Missouri


QUOTE
Just a side mention … it appears your db is working with patient-related data. If so, there may be regulatory restraints (HIPAA in US) that apply.


Jeff, thanks. I am very aware. The data i uploaded was entirely faked. I use a random name generator from the internet and then used Excel do create false data. That's why I was hesitant to create the sample DB to upload. It takes time.

Thanks again.

Jeff
Go to the top of the page
 
jklinephd
post Jul 11 2019, 11:18 AM
Post#10



Posts: 75
Joined: 11-September 07
From: Missouri


QUOTE
Hi. I gave it a shot anyway. Try out this SQL and see if this is what you meant.


That worked as intended in the sample database. I will now try to put it in the actual database and see if it works. Thanks!

Jeff
Go to the top of the page
 
theDBguy
post Jul 11 2019, 11:24 AM
Post#11


Access Wiki and Forums Moderator
Posts: 76,081
Joined: 19-June 07
From: SunnySandyEggo


QUOTE
That worked as intended in the sample database. I will now try to put it in the actual database and see if it works. Thanks!

Hi Jeff. Glad to hear it worked for you. I know generating dummy data may be a pain, but it does help speed things up a bit in finding the right solution. Otherwise, we could end up going back and forth all day trying out different things and still not get it correctly. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Jul 11 2019, 11:28 AM
Post#12



Posts: 2,553
Joined: 4-February 07
From: USA, Florida, Delray Beach


I gave it a shot myself based on the Sample Data, but I am not really sure if it is what you are looking for. Based on the Sample Data, my Results are (Duplication ignored for now):
IDFirst NameLast NameAdmission DateDischarge DateDiagnosis Long DescriptionPrincipal_at_Admission
1001StephanieWallace1/1/20191/6/2019DepressionAntisocial Personality Disorder
1002DianeClarkson1/2/20191/7/2019SchizophreniaBipolar Disorder
1003LiamLambert1/3/2019DepressionDepression
1004UnaDavies1/4/2019Bipolar DisorderSchizophrenia
1005MelanieKing1/5/2019Delusional DisorderDepression
1006JakeHenderson1/6/2019SchizophreniaSchizophrenia
1007StephenRoss1/7/2019SchizophreniaSchizophrenia
1008RachelThomson1/8/20191/13/2019DepressionDepression
1009YvonneMackenzie1/9/2019SchizophreniaSchizophrenia
1010ClaireSanderson1/10/2019DepressionDepression
1011JustinNewman1/10/2019SchizophreniaSchizophrenia
1012KylieMurray1/11/2019Delusional DisorderSchizophrenia
1013StevenAlsop1/12/2019Delusional DisorderDepression
1014AlexanderStewart1/12/2019SchizophreniaSchizophrenia
1015SoniaGray1/13/2019Antisocial Personality DisorderAntisocial Personality Disorder
1016CarolineBailey1/14/2019Delusional DisorderDelusional Disorder
1017MollyBlake1/15/2019SchizophreniaSchizophrenia
1018LilyBuckland1/16/2019SchizophreniaSchizophrenia
1019DianaPeters1/17/2019Delusional DisorderDelusional Disorder
1020JanTerry1/18/2019SchizophreniaSchizophrenia

Go to the top of the page
 
jklinephd
post Jul 12 2019, 08:05 AM
Post#13



Posts: 75
Joined: 11-September 07
From: Missouri


QUOTE
I gave it a shot myself based on the Sample Data, but I am not really sure if it is what you are looking for. Based on the Sample Data, my Results are (Duplication ignored for now):


ADezii, How did you get that result? I am interested in learning new ways of doing things.

Jeff
Go to the top of the page
 
jklinephd
post Jul 12 2019, 08:41 AM
Post#14



Posts: 75
Joined: 11-September 07
From: Missouri


theDBGuy,

I found a snag. My sample database had a field named differently (ID was supposed to be DMH ID). I was able to modify the DLookup and get it to work in the sample database with the added space in the field name. Then I applied it to my real database and I got an error. After digging around and changing things, I finally figured out that I had the wrong datatype for the field "DMH ID" In the sample database i had it as a number, in the Real Database it's Short Text. When I make that change in the sample database I get an error.

I also notice that the query takes a long time to generate in the real database, though I am not certain if that is due to the error or using a DLookup within a database with HUGE tables. When I run the query without the DLookup it loads in seconds. When I run it with the DLookup (with errors) it takes 45+ seconds.

I have attached V2 of the sample with changes to the field name (ID changed to DMH ID) and changes to the datatype. The query just gives me an error in the DLookup expression.

Thoughts?


Attached File(s)
Attached File  sampleDCTv2.zip ( 22.42K )Number of downloads: 1
 
Go to the top of the page
 
ADezii
post Jul 12 2019, 09:18 AM
Post#15



Posts: 2,553
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
ADezii, How did you get that result?

  1. The first, and most obvious question, is is it the correct Result?
  2. It is much easier to attach a Demo rather than explain how I arrived at my Result. Obviously, if the Logic is not correct, than neither is the Demo.

Attached File(s)
Attached File  sampleDCT_Revised.zip ( 34.87K )Number of downloads: 1
 
Go to the top of the page
 
jklinephd
post Jul 12 2019, 09:33 AM
Post#16



Posts: 75
Joined: 11-September 07
From: Missouri



ADezii,

Yes the result was correct. I need to add the code to my sample database to see if it works (see my post above).

Thanks
Go to the top of the page
 
ADezii
post Jul 12 2019, 09:50 AM
Post#17



Posts: 2,553
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif , let us know if you get stuck.
Go to the top of the page
 
jklinephd
post Jul 12 2019, 03:12 PM
Post#18



Posts: 75
Joined: 11-September 07
From: Missouri


ADezii,

It did not work. In v2 of my database, attached above, I made a correction. The ID field was supposed to be a "Short Text" field and in the original I uploaded I had it as a Number. The DLookup works if the field is a Number, it gives an Error if the field is Short Text.

Thoughts?

Jeff
Go to the top of the page
 
ADezii
post Jul 12 2019, 05:39 PM
Post#19



Posts: 2,553
Joined: 4-February 07
From: USA, Florida, Delray Beach


Several Revisions have now been made including establishing a Relationship between the two Tables, changing the ID Field to Text, modifying the DLookup() in the Calculated Field, etc. Try this on for size:
This post has been edited by ADezii: Jul 12 2019, 05:41 PM
Attached File(s)
Attached File  sampleDCTv2_Revised3.zip ( 28.4K )Number of downloads: 3
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th August 2019 - 07:08 AM