Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011

    Unanswered: Summing Fields based on criteria


    I am working on a database and am currently having an issue with one last section of it.

    I currently have a query that sums up a field, groups it by name, and has a date criteria on what is summed up. Unfortunately this means that if the person named has no entries for that date then their name is not pulled down to the list.

    I want their name to still populate the list just entering 0's in the summed fields.

    Any clue on how to do this?

    Currently the Access/SQL query looks like this:

    SELECT DISTINCTROW Prestige_Log.[Member Name], Sum(Prestige_Log.[General Prestige]) AS [Sum Of General Prestige], Sum(Prestige_Log.[Regional Prestige]) AS [Sum Of Regional Prestige], Sum(Prestige_Log.[National Prestige]) AS [Sum Of National Prestige] INTO Prestige_TrackingNew
    FROM Prestige_Log
    WHERE (((IIf(Not IsNull([Prestige_Log]![Month Of Report]),[Prestige_Log]![Month Of Report],[Prestige_Log]![Date Awarded]))=[Date]))
    GROUP BY Prestige_Log.[Member Name];

    Thank you

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    The WHERE clause of your query tries to eliminate Null values and now you want to take them in account under certain circumstances. I guess that these circumstances are that both [Month Of Report] and [Date Awarded] be Null. If this is correct you can complete the WHERE clause with (pay attention to the parentheses):
    OR (([Month Of Report] IS NULL) AND ([Date Awarded] IS NULL))
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts