Calculated columns conditional on calculated columns multiple tables

• Bridgette.hartsell

  Newbie

  Points: 5

  More actions

  April 20, 2014 at 8:11 pm

  #306613

  I have 4 tables involved here. The priority table is TABLE1:

  NAMEID TRANDATE TRANAMT RMPROPID TOTBAL

  000001235 04/14/2014 335 A0A00 605

  000001234 04/14/2014 243 A0A01 243

  000001236 04/14/2014 425 A0A02 500

  TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.

  The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE

  TABLE2

  NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT

  000001234 04/01/2014 400 A0A01 ARC 0

  000001234 04/05/2014 -142 A0A01 ARC 228

  000001234 04/10/2014 15 A0A01 ALT 15

  000001235 04/01/2014 400 A0A00 ARC 400

  000001235 04/05/2014 50 A0A00 ALT 50

  000001235 04/10/2014 105 A0A00 ACF 105

  000001235 04/11/2014 50 A0A00 ADR 50

  000001236 04/01/2014 500 A0A02 ARC 500

  The other table stores the priority order of the charge codes

  TABLE3

  CHGCODE PRIORITY DESCRPTN

  ACF 1 Court fee

  ALT 2 Late fee

  ANS 3 NSF fee

  ARC 4 Rent

  ADR 5 Repair

  AUR 6 Utility

  While the forth stores the customer data:

  TABLE4

  NAMEID RMPROPID FIRSTNAME LASTNAME NAMEGROUP

  000001234 A0A01 Jane Doe 000001234

  000001235 A0A00 John White 000001235

  000001236 A0A02 John Smith 000001236

  000001237 A0A02 Jennifer Smith 000001236

  This table's importance comes by the inclusion of the NAMEGROUP. This way if an account has multiple NAMEIDs, it can be kept straight by their shared NAMEGROUP.

  I am trying to create a report using queries that will: A) calculate the sum of the OPENAMT per NAMEGROUP per DISTINCT CHGCODE B) count the number of records (DISTINCT CHGCODEs) per DISTINCT NAMEID in ORDER by the CHGCODE PRIORITY Then C) calculate a case query whereas:

  CASE WHERE

  TABLE1.TRANAMT=> the calculated sum of the highest priority CHGCODE THEN 'TABLE1.TRANAMT'

  ELSE WHERE TABLE1.TRANAMT <= the calculated sum of the highest priority CHGCODE

  THEN 'the calculated sum of the highest priority CHGCODE'

  ...then...

  CASE WHERE

  TABLE1.TRANAMT less the sum of the highest priority CHGCODE => the calculated sum of the 2nd priority CHGCODE (if not null)

  THEN

  'TABLE1.TRANAMT less the sum of the highest priority CHGCODE'

  ELSE

  WHERE TABLE1.TRANAMT less the sum of the highest priority CHGCODE <= the calculated sum of the 2nd priority CHGCODE

  THEN

  'the calculated sum of the 2nd priority CHGCODE'

  The results should be something like:

  NAMEID TRANDATE TRANAMT RMPROPID CHGCODE APPLAMT

  000001235 04/14/2014 335 A0A00 ACF 105

  000001235 04/14/2014 335 A0A00 ALT 15

  000001235 04/14/2014 335 A0A00 ARC 215

  000001234 04/14/2014 243 A0A01 ALT 15

  000001234 04/14/2014 243 A0A01 ARC 228

  000001236 04/14/2014 425 A0A02 ARC 425

  Also with a remaining balance (per CHGCODE) column.

  Unless there's an alternative solution that someone could suggest that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries. I've been working on this forever & I'm stuck.