Results 1 to 9 of 9
  1. #1
    TxTcher is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    9

    How Do I Write This Query?

    I have a DB that tracks Judgments and payments. In the main table [Referral], I have the judgment broke down into two fields as it consists of two separate parts - Civil Penalty Amt and Fee Amt. In the form I have a txt box that combines these amounts to display the Total Judgment.



    Also in the form I have a subform to display Payments. The payments table breaks down the payment into two fields: AppliedCP Amt and AppliedFee Amt.

    In the main form I added another txt box to display Total Payments, and then another txt box to display Remaining Balance ([Total Judgment]-[Total Payments]).

    My question is how do I filter the form to display: All, Paid in Full and Remaining Balance?

    I thought about adding a new field to the Referral table called "Closed" and have the user insert a date value when the Judgment is paid in full. Then I could filter the form using this field with a Null or NotNull value.

    But, still wonder if there was a better way. I'd rather not use the "closed" option.

    Thanks in advance.

  2. #2
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I would probably use a different table structure. Since a record in your referral table can have multiple judgement amounts, that describes a one-to-many relationship, so the judgements values should be separate records in a related table. Additionally, the payments against those judgements can techically be considered as a negative fee. In other words, have a table that holds all of the transactions related to a referral.

    Your Referral table
    -referralID primary key, autonumber


    tblReferralTransactions
    -pkRefTransID primary key, autonumber
    -fkReferralID foreign key to your Referral table
    -dteTrans (date of the transaction)
    -fkTransTypeID foreign key to tblTransTypes
    -currTransAmount (+ for judgements, - for payments)

    tblTransTypes (records that describe the transaction: Payment, Civil Penalty Amt, Fee Amt etc.
    -pkTransTypesID primary key, autonumber
    -txtTransTypes

    You can then display the details of the judgements/payments in a subform to your main referral form or you can use some domain aggregate functions in controls of your referral form to display the sum of the judgement, sum of the payments and then use a calculated field to show the remaining balance.

  3. #3
    TxTcher is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    9
    Question:

    For the 3rd table: tblTransTypes
    The Payment is split between the Civil Penalty Amt and Fee Amt. So my table would have 4 recs:

    -CPAmt
    -FeeAmt
    -CPAppliedAmt
    -FeeAppliedAmt

    Will this work?

    I want to be able to see total Judgment (CP+Fee) and Total Mthly Payment (CPAppliedAmt+FeeAppliedAmt).

    I'm a little lost on how to do this. But, I'll start working on it and see if I can make it work.

    I'll post back if I can get it figured out.

  4. #4
    TxTcher is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    9
    Okay I started to redesign this and go stuck.

    Each Referral only has one Judgment. The Judgment consists of 2 parts: CP Amt and Fee Amt. (Actually 3 parts - there's the date of the Judgment.) So I started to create a separate Judgment table and then realized that that is a 1-1 relationship with the Referral table and I don't need to do that, right? I can leave Judgment fields in the Referral Table, can't I?

    Each Referral has many Payments. The Payments consists of two parts: CPApplied Amt and FeeApplied Amt.

    So with this information, do you still recommend breaking it up the way you did? I can't seem to get that worked out.

  5. #5
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    That could work, but a better way might be something like this. I'll let you assign more useful table/field names.

    tblCategory (Fee, CP)
    -pkCategoryID primary key, autonumber
    -txtCategory


    tblType (charge, payment)
    -pkTypeID primary key, autonumber
    -txtType

    tblTransTypes (a junction table to handle the combinations of categories and types)
    -pkTransTypeID primary key, autonumber
    -fkCategoryID foreign key to tblCategory
    -fkTypeID foreign key to tblType



    tblReferralTransactions (stays the same)
    blReferralTransactions
    -pkRefTransID primary key, autonumber
    -fkReferralID foreign key to your Referral table
    -dteTrans (date of the transaction)
    -fkTransTypeID foreign key to tblTransTypes
    -currTransAmount

    Since you now have delineated charges and payments via fkTransTypeID, you technically can use all positive values for currTransAmount

    I would then create a query that brings together the transaction and the type (charge or payment)

    query name: qryReferralTransactionsandTypes
    SELECT tblReferralTransactions.fkReferralID, tblTransTypes.fkTypeID, tblReferralTransactions.currTransAmount
    FROM tblReferralTransactions INNER JOIN tblTypes ON tblReferralTransactions.fkTransTypeID=tblTransType s.pkTransTypeID

    Now use that query to get your totals by trans type

    query name: qrySumByReferralTransType

    SELECT qryReferralTransactionsandTypes.fkReferralID, qryReferralTransactionsandTypes.fkTypeID, Sum(qryReferralTransactionsandTypes.currTransAmoun t) as MySum
    FROM qryReferralTransactionsandTypes
    GROUP BY qryReferralTransactionsandTypes.fkReferralID, qryReferralTransactionsandTypes.fkTypeID

    On your form you can then use a DLookup() function to return the data you need. Assuming that fkTypeID=1 for charges and fkTypeID =2 for payments, the Dlookup() for charges would look something like this:

    =DLookup("MySum","qrySumByReferralTransType","fkTy peID=1 and fkReferralID=" & forms!yourformname!controlholdingReferralID)

    and for payments:

    =DLookup("MySum","qrySumByReferralTransType","fkTy peID=2 and fkReferralID=" & forms!yourformname!controlholdingReferralID)

  6. #6
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I can leave Judgment fields in the Referral Table, can't I?
    Yes, you can leave the judgement fields in the referral table

    For each referral/judgement, you still have many money transactions (charges and payments), so you still need the transaction table.

  7. #7
    TxTcher is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    9
    Quote Originally Posted by jzwp11 View Post
    Yes, you can leave the judgement fields in the referral table

    For each referral/judgement, you still have many money transactions (charges and payments), so you still need the transaction table.
    Okay I'm still stumped. Please don't give up on me

    As stated, each Referral has one Judgment. The fields related to the "judgment" are Date, CP Amt and Fee Amt. These fields currently reside in the main table - the Referral Table. (FYI, The amounts do not change once the Judgment is issued.)

    So, question: When you say:
    "tblCategory (Fee, CP)
    -pkCategoryID primary key, autonumber
    -txtCategory"

    Are you saying take the CP and Fee fields (but leave Date field) out of the Main table and put them into the Category table?


    "tblType (charge, payment)
    -pkTypeID primary key, autonumber
    -txtType"

    Is this table for the Payment fields of "CPApplied Amt" and "FeeApplied Amt?" Because I have a payment table consisting of:

    PaymentID (Primary key)
    ReferralID (Foreign Key)
    CPApplied Amt
    FeeApplied Amt
    PaymentDate

    (I have a feeling I'm standing at the brink of maybe finally getting this . . . stay with me )

  8. #8
    TxTcher is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    9
    Disregard my last post. I finally figured out what you were trying to tell me. I am attempting to work through the redesign and will post back if I have any further questions.

  9. #9
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Actually, when a judgement occurs you have a judgement date and it sounds like the CP and Fees are charged/assessed on that date, so the dteTrans will be the same as the judgement date. You need the date field in the transaction table to be able to record when payments occur.

    The CP and Fee amounts will actually become 2 records in the transaction table. The CP and Fee names will be 2 category records in tblCategories. This will allow you more flexibility if other charges are added in the future. I put together a quick database that illustrates my approach; it is attached.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Some Can Read; Some Can Write
    By cassidym in forum Security
    Replies: 3
    Last Post: 08-19-2010, 02:19 PM
  2. How would I write this query?
    By tigers in forum Queries
    Replies: 1
    Last Post: 02-08-2010, 08:29 AM
  3. Write Conflict Error
    By botts121 in forum Access
    Replies: 5
    Last Post: 09-25-2009, 12:11 PM
  4. ADO.Recordset - Write
    By Marvin in forum Programming
    Replies: 0
    Last Post: 07-17-2009, 07:56 AM
  5. write a query how to???
    By flamingo in forum Queries
    Replies: 0
    Last Post: 01-13-2009, 03:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums