My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() 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" |
![]() 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 (untested) Probably have the brackets wrong...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) -------------------- 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 |
![]() 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" |
![]() 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 |
![]() 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) -------------------- 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" |
![]() 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) -------------------- 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 |
![]() 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 |
![]() 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" |
![]() 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" |
![]() 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 |
![]() 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) -------------------- 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" |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 6th December 2019 - 10:40 PM |