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
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];
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))