Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    See the attached I now believe the Invoicefrm is giving you the correct result.

    I removed a number of Lookup Fields in your tables as these are a NO NO in Access.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  2. #17
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Delta729 View Post
    The 2nd and 3rd lines should be what is added. The top line is blank or empty for the SES.
    2nd line is $2K
    3rd line is $5K

    That $7K or did I forget how to add when I had my stroke?
    Try reading post 13 and pretend you do not know the system?
    How does that read to you now?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #18
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    The problem stems from bad table design. Use of Lookup fields in tables and linking Main Form to Subform using the wrong Controls.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  4. #19
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've not read the whole thread but, based on your last comments, you need three filter criteria

    SES Is Not Null AND PurchaseOrder = 4500100001 AND POLine =10
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #20
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Colin,
    I believe the OP needs to identify all PO and PO lines where SES is null
    Then sum all records that match the PO and PO lines BUT where the SES is not Null ?

    I would only be able to do that in two queries?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #21
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    Sorry Welshgasman, I did not see that you had provided an edit. Yes, it should add up the total for the same PO and POLIne where the SES is blank.

  7. #22
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Have you not checked my solution?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #23
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Further to comments on design in posts 4,7 and 18, I just noticed that in invoice table, PurchaseOrder is a number and it seems to me that you'd want to join that to POLineTbl.POLine - but that one is text. I tried and raised a type mismatch error. I'm thinking WTH? Wasn't obvious because the number field has been left justified so I thought both were text at first. There are design errors as previously noted. The PK ID field of PO should be the FK field in PO line, and invoices should have FK of either PO or PO line - depending on whether payments are made against PO's or PO lines. This might not ever be solvable unless (I think) the minimum of converting POLineTbl.POLine or vice versa on the other field. Probably will give that a try just for 'fun'.

    EDIT - will never work as one of them is a table level lookup (probably already mentioned). I'm afraid I'm out of steam on this one.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #24
    Delta729 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Nov 2010
    Location
    Los Angeles
    Posts
    95
    I'm creating a new database from scratch. I'm doing my best to try and get it set up the correct way. I'll try to get that posted in a few days, but I'm getting really confused trying to remember or to learn all the rules or standards for setting up a proper database. I'm going to throw that DSUM calculation in on the invoice form as well, but that will take me a few days to get there most likely. From what I was able to read, that sound like it should work. I was just having a real tough time following how to create that expression with the exception. I kept messing up everything I was trying to do, but when I redo the DB it will hopefully all fall in place. Like you were all saying, it is probably just an issue with me trying to take a short cut, that winds up taking 10 times the amount of time, LOL. My brain looses it thought pattern to easily after I suffered a stroke a couple years ago. I thought this would be a good way for me to start doing something, but my attempt at trying to save time and make shortcuts, has actually increased my stress. So, I'm just taking it real slow going forward.

    I'm closing out this thread and showing this as solved. If I was to ask you all to look at a fresh db structure, I would want that to be on a fresh post. I know this post was getting really confusing. I will do my best to incorporate all your notes in my new db.
    Thanks for your help.

  10. #25
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You DSum() would be along the lines of ...........
    Code:
    Dim strCriteria AS String
    
    strCriteria = "PurchaseOrder = " & Me.PurchaseOrder & " AND POLine = " & Me.POLine & " AND SES IS NOT Null") ' not too sure about the Null part :)
    Debug.Print strCriteria ' so as to see EXACTLY what is produced
    =DSum("ProcessedBalance","ProcessedQry",strCriteria)
    This is assuming that you change PUrchaseOrder and POLine to be numeric, else you will need to surround the form values with single quotes.

    So whatever line you are on, it should show the sum. Where you put that, is up to you.

    https://support.microsoft.com/en-us/...f-386056e61a32
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is assuming that you change PUrchaseOrder and POLine to be numeric,
    See my prior post (edited last night) - one field is a lookup field. As you know, it shows that the value is 10 but it is not. Delta729 I'll post all the links I usually do for those who might need it. These will save you a lot of grief. If any of them don't really speak to you, research the subject and find ones that do. In your new thread I'd suggest that you start with a link to this one and post a pic of your relationships before going much farther. I wouldn't bother with forms or queries before doing that. Make sure you understand normalization, especially with respect to your situation. If any questions on that, might be a good time to start the new thread?

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    - http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Naming conventions -
    - http://access.mvps.org/access/general/gen0012.htm
    - https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html

    About Auto Numbers
    - http://www.utteraccess.com/wiki/Autonumbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Exclude Column from Total Sum
    By ratcat in forum Access
    Replies: 4
    Last Post: 03-18-2019, 05:46 PM
  2. Total Column Not Summing Fields
    By janmack79 in forum Queries
    Replies: 2
    Last Post: 02-12-2019, 03:46 PM
  3. Formatting the total column of a crosstab
    By keith2511 in forum Queries
    Replies: 4
    Last Post: 11-23-2018, 03:35 PM
  4. Total Column in Query
    By spoolinaz in forum Access
    Replies: 10
    Last Post: 01-03-2014, 12:26 PM
  5. Total from subform column?
    By CyberRaptor in forum Forms
    Replies: 2
    Last Post: 01-17-2013, 05:25 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