Results 1 to 10 of 10
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    Query detail totals vs. Summary Totals do not match?

    I can't figure out why the total numbers are not reconciling?

    SELECT [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season, IIf(Sum(IIf((DatePart("m",[PaymentDate])>=6 Or DatePart("m",[PaymentDate])<=7) And DatePart("m",[paymentdate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
    FROM 02_Collegiate_Details
    GROUP BY [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season;

    MemberType Season Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    Collegiate
    2016 520 617 716 795 822 836 856 874 887 889 889 889
    Collegiate 2017 537 622 694 815 857 871 903 923 933 943 943 943
    Collegiate 2018 638 732 839 900 948 984 1032 1054 1062 1067 1067 1067
    Collegiate 2019 696 779 871 970 1016 1032 1032 1032 1032 1032 1032 1032

    The above code is producing 520 however if you total the records from the 02_Collegiate_Details, I get 507 for the date range Jun-Jul




    Thoughts and suggestions?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Note I removed CODE tags. SQL statements are harder to read within CODE tags.

    Is your db multi-year? Shouldn't the SQL consider year when summarizing?

    Can't see anything else of possible issue in the query. Must be something in the data.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Hi June7,
    It has historical data going back many years.
    Collegiate memberships are 4 years long, and it is under the Season grouping.

    The details query is pulling based on this criteria:
    SELECT dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear AS Season, dbo_v030mbrshp02Collegiates.MembershipNumber, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate
    FROM dbo_v030mbrshp02Collegiates
    WHERE (((dbo_v030mbrshp02Collegiates.MemberTypeID)=3) AND ((dbo_v030mbrshp02Collegiates.EndYear) Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4))
    GROUP BY dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear, dbo_v030mbrshp02Collegiates.MembershipNumber, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate
    HAVING (((dbo_v030mbrshp02Collegiates.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,6,1),DateSerial(Year(Date())-3,6,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),Month (Date()),0),DateSerial(Year(Date()),Month(Date()), 0))))
    ORDER BY dbo_v030mbrshp02Collegiates.EndYear, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate;


    Then the summary query, 02_Collegiate, is the one from the original post

    See attached sample data.

    NOTE: the query named 02_Collegiate_Details_LIVE is the one that links to the live SQL table where the above code is used.
    I dumped the data results from this to 02_Collegiate_Details table so you can see the detail data set.Collegiate.zip
    Last edited by aellistechsupport; 01-13-2016 at 11:05 AM. Reason: Add db attachment

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The 02_Collegiate_Details table is a dump from the second query?

    Now I see something odd about expressions in original query.

    IIf(DatePart("m",[PaymentDate])>=6 And 7 Or

    And 7 what? This makes no sense.
    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.

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Two queries:
    1. 02_Collegiate_Details
    2. 02_Collegiate where it summarizes using the details query

    Data was dumped from the results from the 02_Collegiate_Details query

    <= June and July

  6. #6
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Also I have this that sums up through end of last month and the totals are also off

    INSERT INTO Comparison ( MemberTypeID, MemberGroup, Type, CreateDate, ReportingMonth )
    SELECT 3 AS MemberTypeID, "Regular Members" AS MemberGroup, [02_Collegiate_Details].MemberType, DateSerial(Year(Date()),Month(Date()),0) AS LastMonth, IIf(Month(Date())<=7,IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])=Month(Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])=Month(Date()),1,0))),IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])=Month(Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])=Month(Date()),1,0)))) AS ReportingMonth
    FROM 02_Collegiate_Details
    GROUP BY 3, [02_Collegiate_Details].MemberType;


    This shows 3823 instead of 3723 from last Jun - Dec

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That expression still does not make sense.

    Did you mean:

    DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7

    or maybe

    DatePart("m",[PaymentDate]) BETWEEN 5 AND 7
    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.

  8. #8
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    I'll try that although the 02_Collegiate_Details reconcile itself out on the totals?

    NOTE: the last code (copy and paste it into a new query in the uploaded file) is the one that is off now.
    This also uses the 02_Collegiate_Details

    I went and verified numbers from Jun - Dec and they are correct on the 02_Collegiate query after all.

  9. #9
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    so just ended up replacing the = to < and the numbers now match

    INSERT INTO Comparison ( MemberTypeID, MemberGroup, Type, CreateDate, ReportingMonth )
    SELECT 3 AS MemberTypeID, "Regular Members" AS MemberGroup, [02_Collegiate_Details].MemberType, DateSerial(Year(Date()),Month(Date()),0) AS LastMonth, IIf(Month(Date())<=7,IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<Month(Date()),1,0))=0,Null,Sum(IIf(DatePart("m" ,[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<Month(Date()),1,0))),IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<Month(Date()),1,0))=0,Null,Sum(IIf(DatePart("m" ,[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<Month(Date()),1,0)))) AS ReportingMonth
    FROM 02_Collegiate_Details
    GROUP BY 3, [02_Collegiate_Details].MemberType;

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay but I still think the "And 7" is nonsense and I don't understand how the SQL is processing this. Maybe it is ignored. What happens if it is removed?
    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.

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

Similar Threads

  1. Summary report with totals Each field
    By gildezio in forum Reports
    Replies: 1
    Last Post: 05-18-2015, 05:29 AM
  2. Replies: 2
    Last Post: 04-30-2014, 03:01 PM
  3. Cumulative totals for summary payments
    By maxmaggot in forum Forms
    Replies: 2
    Last Post: 04-06-2014, 12:50 PM
  4. Replies: 4
    Last Post: 08-02-2013, 12:00 PM
  5. Report totals don't match
    By RayMilhon in forum Reports
    Replies: 7
    Last Post: 11-30-2012, 10:44 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