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
> Stumped Once Again, Any Version    
 
   
dashiellx2000
post Nov 14 2019, 02:21 PM
Post#1


UtterAccess VIP
Posts: 9,839
Joined: 11-March 05
From: Maryland


Here are my tables:

tblEmergencyDepartmentEncounters
EmergencyDepartmentEncounterID
EmergencyDepartmentEncounter
EmergencyDepartmentEncounterName
etc....

tblEmergencyDepartmentVisitsCharges
EmergencyDepartmentVisitsID - AutoNum PK
EmergencyDepartmentEncounter - FK
ServiceUnits
EmergencyDepartmentVisitCharges - Currency
EmergencyDepartmentVisitCDM - FK

tluEmergencyRoomCDMs
EmergencyRoomCDMsID - AutoNum PK
EmergencyRoomCDM
EmergencyRoomCDMDescription
EmergencyRoomCDMRevCode
EmergencyRoomCDMLevel

For each Encounter in tblEmergencyDepartmentEncounters there will be one or more entries on tblEmergencyDepartmentVisitsCharges. Each entry on tblEmergencyDepartmentVisitsCharges has a matching record on tluEmergencyRoomCDMs that indicates the level of the charge.

What I need to return in a query is

Encounter
Sum of EmergencyDepartmentVisitCharges
Max EmergencyRoomCDMLevel
EmergencyRoomCDMDescription that matches the charge with the greatest EmergencyRoomCDMLevel

I can change the design of the tables if needed, so don't let that hold back your suggestions.

Thanks.


--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
theDBguy
post Nov 14 2019, 02:34 PM
Post#2


UA Moderator
Posts: 76,819
Joined: 19-June 07
From: SunnySandyEggo


Hi William. Just a guess, but maybe something like:
CODE
SELECT Q1.Encounter, Q1.Total, T3.EmergencyRoomCDMDescription
FROM
  ((SELECT T1.Encounter, Sum(T2.EmergencyDepartmentVisitCharges) As Total, Max(T2.EmergencyDepartmentVisitCDM) As MaxCDM
    FROM tblEmergencyDepartmentEncounters T1
    INNER JOIN tblEmergencyDepartmentVisitsCharges T2
      ON T1.EmergencyDepartmentEncounterID=T2.EmergencyDepartmentEncounter
    GROUP BY T1.Encounter) Q1
  INNER JOIN tblEmergencyRoomCDMs T3
    ON Q1.MaxCDM=T3.EmergencyRoomCDM)
(untested) Probably have the brackets wrong...

--------------------
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
 
dashiellx2000
post Nov 14 2019, 02:43 PM
Post#3


UtterAccess VIP
Posts: 9,839
Joined: 11-March 05
From: Maryland


I don't think the max can be done on the EmergencyDepartmentVisitCDM as these are random generated string that have not sort. The CDM for an ER Level 0 could actually be higher in a standard sorting than a CDM that corresponds to an ER level 5.

Also, the field EmergencyRoomCDM on table tluEmergencyRoomCDMs is actually EmergencyDepartmentVisitCDM, I just didn't type consistently in the table. (Bad William).

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
theDBguy
post Nov 14 2019, 03:08 PM
Post#4


UA Moderator
Posts: 76,819
Joined: 19-June 07
From: SunnySandyEggo


Oh, I see. I got tired typing those long names too. Can you post a sample db instead to make this easier/faster? 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
 
dashiellx2000
post Nov 14 2019, 03:29 PM
Post#5


UtterAccess VIP
Posts: 9,839
Joined: 11-March 05
From: Maryland


This is all three tables with confidential info removed.

Thanks.
Attached File(s)
Attached File  ERLevel_Example.zip ( 273.53K )Number of downloads: 1
 

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
theDBguy
post Nov 14 2019, 03:37 PM
Post#6


UA Moderator
Posts: 76,819
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not sure if the attached Query1 helps because I noticed your CDM levels are not unique.

Attached File(s)
Attached File  ERLevel_Example.zip ( 274.62K )Number of downloads: 3
 

--------------------
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
 
projecttoday
post Nov 14 2019, 04:04 PM
Post#7


UtterAccess VIP
Posts: 11,250
Joined: 10-February 04
From: South Charleston, WV


If you don't need your sum broken down you can use Dsum.

--------------------
Robert Crouser
Go to the top of the page
 
dashiellx2000
post Nov 15 2019, 08:31 AM
Post#8


UtterAccess VIP
Posts: 9,839
Joined: 11-March 05
From: Maryland


QUOTE
Hi. Not sure if the attached Query1 helps because I noticed your CDM levels are not unique.


That looks like it is correct, but can you expand on what you think is incorrect about my table structure? The ER Levels are 0-8 and do not actually link to any greater definition, it is almost like a weight for the service.

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
dashiellx2000
post Nov 15 2019, 08:33 AM
Post#9


UtterAccess VIP
Posts: 9,839
Joined: 11-March 05
From: Maryland


QUOTE
If you don't need your sum broken down you can use Dsum.


I'm thinking to do this as a query as there are over 225K records so far and this will only grow.

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 
theDBguy
post Nov 15 2019, 12:27 PM
Post#10


UA Moderator
Posts: 76,819
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (dashiellx2000)
That looks like it is correct, but can you expand on what you think is incorrect about my table structure? The ER Levels are 0-8 and do not actually link to any greater definition, it is almost like a weight for the service.
What I meant was that the CDM table has duplicate Level values but different descriptions. Further, the level and description can be duplicated by having different CDM. As a result, when you link the CDM table to your Visits table, you get duplicates or multiple rows for duplicate CDM levels. As an example, open up Query1 and filter the Encounter column to look for 112141939050. Now, tell us if that information is correct.

--------------------
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
 
dashiellx2000
post Nov 18 2019, 03:02 PM
Post#11


UtterAccess VIP
Posts: 9,839
Joined: 11-March 05
From: Maryland


QUOTE (theDBguy)
What I meant was that the CDM table has duplicate Level values but different descriptions. Further, the level and description can be duplicated by having different CDM. As a result, when you link the CDM table to your Visits table, you get duplicates or multiple rows for duplicate CDM levels. As an example, open up Query1 and filter the Encounter column to look for 112141939050. Now, tell us if that information is correct.


Yep. I see what you're saying. Got called away on another issue and didn't look that deep, but wanted to follow-up on your point.

So, I need to break down my tables further. Do you think this will work better:

tluEmergencyRoomCDMs
EmergencyRoomCDMID
EmergencyRoomCDM
EmergencyRoomCDMDescriptionID

tluEmergencyRoomCDMDescriptions
EmergencyRoomCDMDescriptionID
EmergencyRoomCDMDescription
EmergencyRoomCDMLevel

But then of course I'm still stuck on how to get the query correct. Updated table structure attached. If you have any other suggestions, please let me know and I appreciate the hand holding.

Attached File(s)
Attached File  ERLevel_Example.zip ( 277.2K )Number of downloads: 1
 

--------------------
William
“We're run by the Pentagon, we're run by Madison Avenue, we're run by television, and as long as we accept those things and don't revolt we'll have to go along with the stream to the eventual avalanche"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 08:10 PM