Page 5 of 5 FirstFirst 12345
Results 61 to 66 of 66
  1. #61
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    I will do what I can. I do not work in the department I am writing the database for. I work in IT, they work in Health. We actually had all our financial /payroll go through an AS400 system which they have just eliminated & went to a .net. When they went to the .net version it eliminated their cash receipts portion so they wanted this new database to track their receipts/budget lines etc.

    So they have the several sections of the Health department which produce a revenue that they have to track. The payments come in through the clerical area through checks, money orders & cash. Sometimes it is Mr. Joe Public coming in for a public health shot or coming in for a water test. They pay the person in the clerical area who needs to provide them with a receipt. The information behind the receipt will have to backup all their justification & documentation for grants & funding they receive, or to justify their positions that are county funded.



    Perhaps the reports out that they are looking for would help? Let me know & I can break down what they want out of it.

  2. #62
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    From what I have read so far, multiple payments are made against a receipt. The only thing missing are those payments applied to specific budget lines associated with the receipt or the receipt as a whole. If you can tell that from the reports you mention, that is all we would need to set up the payment part of the structure.

  3. #63
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    They want 5 different reports besides the actual receipt itself.

    Report 1 - Transaction Journal Summary(should be able to sort with a beginning / end date )
    Budget Line - all the way to extended description
    Total Amt of Transactions for that Budget Line
    Total number of Transactions for that Budget Line
    total Amount & total Transactions for that sorted time period.
    <these transactions will not include any voided receipts.

    Report 2 - Transaction Journal Detail (Should be able to sort with a beginning / end date)
    After the date sort should be sorted by budget line & list the following:
    Transaction Date
    Receipt Number
    Employee
    Payor
    Detail (comment area)
    Transaction Amount
    Would like a total for each budget line & a grand total for the sorted period.
    <these transactions will not include any voided receipts>

    Report 3 - Detail of Transactions (Should be able to sort with a beginning / end date)
    After the date sort will be detailed by Employee & will list the following fields:
    Void
    Receipt Number
    Payor
    (I was originally breaking down these as there own sort after employee - listing and totally the receipts in each payment type.)
    Cash Amt
    Check Amt
    Other Amt
    Then a total per employee & a grand total for the transaction period

    < if you aren't sure what I am talking about it was basically set up the following way:
    For date period 1/1/11 - 1/31/11
    Employee Jane Smith
    Cash Payments
    void, receipt number, from, amt
    "" "" "" "" ""
    total cash payments total
    Check Payments
    void, receipt number, from, amt
    "" "" "" "" ""
    total check payments total
    I am sure there is probably a more efficient way of doing this>

    Report #4 was a Receipt Distribution which is first sorted with the beginning / end date then by budget line.
    So after the 2 sorts it will group the budget lines & list the following details
    transaction date
    receipt number
    payor
    then the break down on -
    cash payments
    check payments
    other payments
    We need a total for each type of payment and a total for each budget line
    Then a grand total for the time period.

    Report #5 would just be a report of Voided receipts for a specific period with the following fields
    Employee (sort/group by)
    Receipt number
    payor
    cash amt
    check amt
    other amt
    totals per payment type & employee for time period

    It sounds totally confusing when I am trying to explain it but I am looking at the reports they used to receive and they basically crossed off what they didn't need & put on there what they thought was necessary.

  4. #64
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Unfortunately the reports don't clearly define how the payments are applied, so I'll give you both structures & you will have to work with your "client" to figure out which one to use. Both of the following assume multiple payments/receipt or item can occur.

    Assuming that payments are applied to the receipt as a whole:

    tblReceiptPayments
    -pkPayID primary key, autonumber
    -fkReceiptID foreign key to ReceiptOutput table
    -fkPaymentTypeID foreign key to PaymentType table
    -currAmtPaid
    -dtePaid (date paid)

    Assuming that payments are applied to budget line items of the receipt

    tblReceiptBLPayments
    -pkReceiptBLPaymentID primary key, autonumber
    -fkReceiptBudgetLineID foreign key to tblReceiptBudgetLines
    -fkPaymentTypeID foreign key to PaymentType table
    -currAmtPaid
    -dtePaid (date paid)

  5. #65
    dwright is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2011
    Posts
    35
    I'm working on setting this up - with the real data. Do you have an email address that I can send you a zipped file to look at?

  6. #66
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    My e-mail address is available for friends via my profile, so I sent you a friend request.

Page 5 of 5 FirstFirst 12345
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Formatted Excel Output
    By jerryrs in forum Import/Export Data
    Replies: 6
    Last Post: 02-26-2011, 11:58 PM
  2. Replies: 3
    Last Post: 01-10-2011, 10:31 AM
  3. Replies: 4
    Last Post: 10-03-2010, 09:54 PM
  4. Help combining two quieries to output
    By AccessUser1234 in forum Access
    Replies: 3
    Last Post: 04-01-2010, 01:59 PM
  5. Insert Query output into a table
    By ammu_sridhar in forum Programming
    Replies: 1
    Last Post: 06-12-2009, 01:09 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