Results 1 to 11 of 11
  1. #1
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19

    Query is eliminating duplicate records when not desired

    Hello,



    I am trying to write a query that puts invoice amounts in columns, by invoice, named "Jan", "Feb", "Mar", etc. based on the right two characters of a "Period to Post" field. When I do this, the results are erroneously excluding amounts that are identical even though I have Unique Values and Unique Records set to "No". For example, if I have four charges for postage of $0.44 each in February, the query is only returning $0.44 once. Here is the syntax that I've used for each month:

    Jan: IIf(Right([GL Quick Query].[Period to Post],2)="01",[Debit Amount],0)
    Feb: IIf(Right([GL Quick Query].[Period to Post],2)="02",[Debit Amount],0)
    Mar: IIf(Right([GL Quick Query].[Period to Post],2)="03",[Debit Amount],0)

    I do have my query set up as a "Sum" (sorry, but I don't know the right terminology here) using "Group By" as the Total function. I thought this might be why I was having trouble but when I set it up without a "Sum", and used "unique values", I'm finding the same result. I am not experienced at all with Access and was hoping that someone might be able to help. Thanks.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Please change your query to SQL View, and copy and paste the SQL code of your query here so we can see it and analyze it.

  3. #3
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Sorry...it's kind of a long string due to all of the months. Here it is:


    SELECT IIf([Vendor/Customer ID]="","GL",[Vendor/Customer ID]) AS [Vendor Name], IIf([Journal Type]="GJ",[Reference Number],[External Reference Number]) AS [Invoice #], [GL Quick Query].Account AS Acct, [GL Quick Query].Subaccount AS Dept, IIf([Journal Type]="GJ",[Transaction Description],[DocNote]) AS [Description 1], PSJ.TranNote, IIf(Right([GL Quick Query].[Period to Post],2)="01",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Jan, IIf(Right([GL Quick Query].[Period to Post],2)="02",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Feb, IIf(Right([GL Quick Query].[Period to Post],2)="03",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Mar, IIf(Right([GL Quick Query].[Period to Post],2)="04",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Apr, IIf(Right([GL Quick Query].[Period to Post],2)="05",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS May, IIf(Right([GL Quick Query].[Period to Post],2)="06",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Jun, IIf(Right([GL Quick Query].[Period to Post],2)="07",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Jul, IIf(Right([GL Quick Query].[Period to Post],2)="08",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Aug, IIf(Right([GL Quick Query].[Period to Post],2)="09",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Sep, IIf(Right([GL Quick Query].[Period to Post],2)="10",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Oct, IIf(Right([GL Quick Query].[Period to Post],2)="11",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Nov, IIf(Right([GL Quick Query].[Period to Post],2)="12",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS [Dec], [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec] AS YTD
    FROM [GL Quick Query] LEFT JOIN PSJ ON ([GL Quick Query].[External Reference Number] = PSJ.[Invoice Number]) AND ([GL Quick Query].[Account] = PSJ.[Account])
    GROUP BY IIf([Vendor/Customer ID]="","GL",[Vendor/Customer ID]), IIf([Journal Type]="GJ",[Reference Number],[External Reference Number]), [GL Quick Query].Account, [GL Quick Query].Subaccount, IIf([Journal Type]="GJ",[Transaction Description],[DocNote]), PSJ.TranNote, IIf(Right([GL Quick Query].[Period to Post],2)="01",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="02",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="03",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="04",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="05",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="06",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="07",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="08",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="09",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="10",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="11",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), IIf(Right([GL Quick Query].[Period to Post],2)="12",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0), [GL Quick Query].[Period to Post]
    HAVING ((([GL Quick Query].Account)=[Which account?]))
    ORDER BY [GL Quick Query].Account;


    Thank you.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yeah, that is pretty messy and hard to decipher.

    But I think the issue may be with your grouping. You do NOT want to group by the calculated amounts. Those should only be in the SELECT clause of your query (should NOT be in the GROUP BY section of the query), and they should have the SUM function applied to them.

  5. #5
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Okay, I simplified by removing the Group By and taking out all months other than Jan. I had to use "Yes" for Unique Values to duplicate the results but now I'm wondering if that is the problem, which is that I have four records with same dollar amount and invoice # and need to have them all listed. For what it's worth, if I make Unique Values "No" I get a much crazier result (lots of duplicated lines). Thanks.


    SELECT DISTINCT IIf([Vendor/Customer ID]="","GL",[Vendor/Customer ID]) AS [Vendor Name], IIf([Journal Type]="GJ",[Reference Number],[External Reference Number]) AS [Invoice #], [GL Quick Query].Account AS Acct, [GL Quick Query].Subaccount AS Dept, IIf([Journal Type]="GJ",[Transaction Description],[DocNote]) AS [Description 1], PSJ.TranNote, IIf(Right([GL Quick Query].[Period to Post],2)="01",IIf([Debit Amount]=0,-[Credit Amount],[Debit Amount]),0) AS Jan
    FROM [GL Quick Query] LEFT JOIN PSJ ON ([GL Quick Query].[External Reference Number] = PSJ.[Invoice Number]) AND ([GL Quick Query].[Account] = PSJ.[Account])
    WHERE ((([GL Quick Query].Account)=[Which account?]))
    ORDER BY [GL Quick Query].Account;

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It seems to me that you are trying to kill two birds with one stone here - you are trying to get data and also trying to get the display at the same time. Violating the KISS principle!

    First, make a simple query that has all the data correct. Include a field that has Right([Period to Post],2), also the debit and credit amounts with and additional amount field using the IIF statement (if debit is 0, use credit). Now you have all the data that you want.

    Next, group and sum - group by the main fields and sum the amount.

    Once all of this looks correct (ignore unique values property) you can now worry about how to display it. A crosstab query will work.

  7. #7
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    I love this idea (before your post I didn't even know what a crosstab query was) but I think I'm missing something key even before this. I simplified the statement even further and still cannot seem to get all four instances of the charge to come through. The problem seems to be that the vendor name, invoice number, description and amount are the same for four lines. If I set unique values to "No", I get 10 repeated instances for each invoice (i.e. 100 lines with same amount 10 times each). If I set unique values to "Yes", I get seven instances...one for each unique amount, but this excludes the other three that I happen to need. Is there a way to summarize the data before trying to do what I am doing? In other words, if I have four instances of an invoice for $0.44 with the same vendor, invoice number and description, I'm perfectly happy making this $1.76 ($0.44 x 4) before the rest of my query goes to work...something akin to pivoting the data before using it. Any thoughts? Thank you.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Go back to square one and start over, that's my advice. You are getting deeper and deeper into confusion!

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    My advice is to start simple - with one month, get that working, and build from there.
    Note that if you go with the original idea, I think you WILL need Grouping, you just don't want the month calculation in the Grouping. If you select "DISTINCT", you are removing all the similar records you want to add up! You do not want that, you want to group on the similar feeds, and SUM your calculated amount field.

    Also note that your JOINS may cause problem if not done correctly, or if you have a one-to-many or many-to-many relationship.

    As aytee111 suggested, don't try to do too much in one query. It can get real confusing real fast! Don't be afraid to break it up into multiple queries, where the next queries uses the previous query as its source.

    If you run into trouble, you can always upload a copy of your database here (be sure to remove any sensitive data first!). Seeing all the relationships, table structure, data, and queries helps us determine what is going on.

  10. #10
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Thanks for both of your help. As it turns out, my SQL was okay, it was the data that was driving the issues because there wasn't enough uniqueness to the information. Once I made some adjustments using a concatenated unique digit, all worked okay. We now have to change our behavior internally to accommodate the situation but that's easy to do. I'm marking this as resolved. Thanks for your help.

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome. Glad you were able to get it all sorted out.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-17-2015, 10:23 PM
  2. Access Query not returning desired records
    By NaomiC in forum Queries
    Replies: 2
    Last Post: 01-10-2015, 01:18 PM
  3. Replies: 4
    Last Post: 04-22-2014, 08:23 AM
  4. Replies: 1
    Last Post: 08-25-2013, 11:39 AM
  5. Replies: 2
    Last Post: 01-27-2012, 09:49 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