Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    sconnors is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    10

    Question Help with running total

    Hello,
    I am new here.
    I have built a database with two tables (Vendors & Purchase Orders). The Venor table has the following fields and data types:
    ID - AutoNumber primary key
    Vendor Name - Text
    Vencor Number - Text


    The purchase order table has the following fields and data types:
    ID - AutoNumber Primary Key
    Requisition Number - Number
    Vendor Name - Number
    Amount - Currency
    Org - Text
    Obj - Text
    PO Number - Number
    Invoice Number - Text
    Invoice Date - Date/Time
    Blanket/Normal - Text
    Voucher Signed - Yes/No


    Check Number - Number
    Check Date - Date/Time
    Notes - Text
    PO Closed - Yes/No


    What I want to do but am not sure how to do is have a running total on my purchase orders form which will tell me how much money is left on each Purchase Order.


    Each Purchase Order can have numerous enteries throughout the year with money coming off and I would like to be able to see at a glance how much is left on each but am unsure on how to proceed.


    Any help would be greatly appreciated as I'm new to this

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    First, you need to separate out the payments from this table and put them in their own table. The reason for this is that multiple payments can be made against one PO. Then it will be a simple matter to create a query which sums the payments made and compares it to the original purchase order total.

    Also, usually PO's have more than one item, in which case you will need a PO header table as well as a PO item table.

  3. #3
    sconnors is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    10
    Quote Originally Posted by aytee111 View Post
    First, you need to separate out the payments from this table and put them in their own table. The reason for this is that multiple payments can be made against one PO. Then it will be a simple matter to create a query which sums the payments made and compares it to the original purchase order total.

    Also, usually PO's have more than one item, in which case you will need a PO header table as well as a PO item table.
    Thanks for your input. I'm not concerned with the item associated with the PO only the amount and keeping a running total. When I create my PO table what fields should I have in it (I'm thinking PO number and PO amount)? Also I currently have the PO numbers in the Purchase Order table, should I just leave that there?
    Thank you as I said I'm new and appreciate your help.

  4. #4
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    Payments would be in the separate table aytee suggested. Your running total would be the sum of all payments to that PO as queried from the separate table.

    One table would have PO number and PO amount, and any other PO level info (PO Date, Vendor, etc.)
    The other table would have PO Number, Payment Date, and Payment Amount.
    You would build an aggregate query summing Payment Amount grouped by PO Number, and relate your PO table to that query, resulting in PO Number, PO Amount, Sum of PO Payments. You could calculate Amount Due as PO Amount minut Sum of PO Payments.

    Also his note about separate items on the PO. If you have line items on your POs, you would also have a PODetail table with PO Number, PO Line Number, description, etc., and PO Line Item Amount. Your total PO Amount in that case would be the sum of PO Line Item Amounts from that table, working the same wway as the PO Payments table.

  5. #5
    sconnors is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    10
    Thanks for the reply. I've created a PO table and Payment table as suggested above with those fields and added the data in. I now have four tables in my database: Payments, PO, Purchase Orders, Vendors. Questions:
    1. What should the relationships be between all my tables?
    2. In my original Purchase Order table do I need to delete any fields since creating the new tables? My fields in the Purchase Order table are:
    ID - AutoNumber Primary Key
    Requisition Number - Number
    Vendor Name - Number
    Amount - Currency
    Org - Text
    Obj - Text
    PO Number - Number
    Invoice Number - Text
    Invoice Date - Date/Time
    Blanket/Normal - Text
    Voucher Signed - Yes/No
    Check Number - Number
    Check Date - Date/Time
    Notes - Text
    PO Closed - Yes/No

  6. #6
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    Check Number and Check Date move to the Payments table.

    Could you explain to me what PO is?

  7. #7
    sconnors is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    10
    PO is the table it was suggested I create above it has PO NUMBER, PO AMOUNT, PO DATE, PO VENDOR.

  8. #8
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    Okay, so that replaces your Purchase Orders table?

    Look at your tables as collections.

    PO would be a collection of all fields related to the Purchase Order itself, as long as there can only be one value for any give PO#. For example, if your POs don't have line item detail, PO Amount would be a member of that collection because you can have only one PO Amount.

    Payments would be all fields related to a single payment, like check number, amount, and the PO # that payment is for.

    Vendors would be all fields related to a single vendor, like their name, address, terms, etc.

    If your PO has line items, you would also need a PO Line Items or PO Detail table (same thing, just a naming preference). That would contain PO #, Line Item Number, Item Descrip, Item Qty, Item Extended Price, etc. If you have blanket POs that cover multiple invoices, an Invoices table would serve the same purpose as the Po Detail table. If you only ever have one Invoice per PO, the Invoice fields are fine in our PO table.

  9. #9
    sconnors is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    10
    So I've attached pictures of how my database looks so far. Not sure what my relationship needs to be between the payments table and purchase order table. Please take a look and see if I'm on the right track.
    Last edited by sconnors; 07-14-2017 at 11:03 AM.

  10. #10
    sconnors is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    10
    So I've attached pictures of how my database looks so far. Not sure what my relationship needs to be between the payments table and purchase order table. Please take a look and see if I'm on the right track.
    Click image for larger version. 

Name:	Access1.PNG 
Views:	23 
Size:	10.0 KB 
ID:	29424Click image for larger version. 

Name:	Access2.PNG 
Views:	23 
Size:	8.9 KB 
ID:	29425Click image for larger version. 

Name:	Access3.PNG 
Views:	23 
Size:	13.5 KB 
ID:	29426Click image for larger version. 

Name:	Access4.PNG 
Views:	23 
Size:	7.0 KB 
ID:	29427

  11. #11
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    One Purchase Order record to many Payment records. PO Number to PO Number.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Running total in query is not easy. It is simple to do in report because textbox has RunningSum property.

    Advise no spaces or punctuation/special characters (underscore only exception) in object or field names.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    sconnors is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    10

    Relationship

    Quote Originally Posted by Preston View Post
    One Purchase Order record to many Payment records. PO Number to PO Number.
    When I attempt that this is what I get
    Click image for larger version. 

Name:	Capture.PNG 
Views:	22 
Size:	33.5 KB 
ID:	29483

  14. #14
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    You are trying to relate your autonumber ID field in p.o. to p.o. number in payments. When you use auto identity fields, they should be independent of the data so there would be no relationship. Relate p.o. number to p.o. number

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Looks like you removed spaces in some field names but not in Payments table. Advice not to use spaces in names also applies to tables/queries/forms/reports.

    If PurchaseOrderID is the PK it should be saved as FK in Payments [PO Number]. And if PurchaseOrderID is autonumber, [PO Number] must be Long Integer type.

    If you save PONumber into Payments [PO Number] then PONumber should be defined as PK and the PurchaseOrderID would not really serve any purpose.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-14-2015, 06:05 PM
  2. Running total
    By edwardcga in forum Reports
    Replies: 1
    Last Post: 11-28-2013, 12:04 PM
  3. Running Total Query??
    By kwooten in forum Queries
    Replies: 8
    Last Post: 06-15-2012, 06:10 AM
  4. Running Total (Cumulative)
    By jamesborne in forum Queries
    Replies: 3
    Last Post: 12-26-2011, 09:30 PM
  5. Running total
    By lololthis in forum Queries
    Replies: 5
    Last Post: 06-21-2011, 04:14 PM

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