[X]My Assistant

[dashiellx2000]

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.

[theDBguy]

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

[dashiellx2000]

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

[theDBguy]

Oh, I see. I got tired typing those long names too. Can you post a sample db instead to make this easier/faster? Thanks.

[dashiellx2000]

This is all three tables with confidential info removed.

Thanks.

Attached File(s)

 ERLevel_Example.zip ( 273.53K )Number of downloads: 1

 

[theDBguy]

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

Attached File(s)

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

 

[projecttoday]

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

[dashiellx2000]

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.

[dashiellx2000]

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.

[theDBguy]

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.

[dashiellx2000]

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)

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