Results 1 to 11 of 11
  1. #1
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182

    Stop FIRST Aggregate Function from ignoring null values.

    Is it possible to Stop FIRST Aggregate Function from ignoring null values?

    I have not been able to figure it out

  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
    Post your query SQL statement for analysis.

    Use Nz(field, alternate value) or IIf(IsNull(field),this,else this) to handle null and give an alternate value.
    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
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Code:
    SELECT [TblAlpha].[CaseNum], [TblAlpha].[Date of Event], Min([Suspected Event].[ Event]) AS [MinOfEvent], First([Suspected Event].[City]) AS [FirstOfCity], [TblAlpha].Followup
     
    FROM [TblAlpha] INNER JOIN [Suspected Event] ON [TblAlpha].[CaseNum] = [Suspected Event].[CaseNum]
     
    GROUP BY [TblAlpha].[CaseNum], [TblAlpha].[Date of Incident], [TblAlpha].Followup
     
    HAVING ((([TblAlpha].[Date of Incident])>=[forms]![frmQR]![txtDateStart] And ([TblAlpha].[Date of Incident])<=[forms]![frmQR]![txtDateEnd]) AND ((First([Suspected Event].[City]))=[forms]![frmQR]![txtCity]) AND (([TblAlpha].Followup) Is Null))
     
    ORDER BY [TblAlpha].[CaseNum];

  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
    Maybe:

    First(Nz([Suspected Event].[City],""))
    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
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    I tried that. It did not seem to work. Let me try to explain the issue better...



    First of all, let me explain my tables.

    I have two tables in my query. (These are not the actual names fyi). tblMain & tblEvent.

    tblMain consists of the fields: “EventNum”, “Date”, and “AdditionalNum”. The primary key is “EventNum”


    TblEvent consists of the fields: “EventNum”, “EventType”, “City1”, “City2”, “City3”. The primary key is a compound key comprising of EventNum and EventType.

    Please see an example of a form I use below. The pictures are of the same EventNum, but two separate records.


    Click image for larger version. 

Name:	temp.png 
Views:	15 
Size:	13.2 KB 
ID:	28459Click image for larger version. 

Name:	temp2.png 
Views:	15 
Size:	13.6 KB 
ID:	28458



    I want to run a query that will, in effect, totally ignore the “Record 2 of 2”. In order to do this I use the following aggregate functions in my query:

    Field
    Event Num
    Date
    Add Num
    Event Type
    City 1
    City 2
    City 3
    Table tblMain tblMain tblMain tblEvent tblEvent tblEvent tblEvent
    Total Group By Group By MIN FIRST FIRST FIRST
    Sort Ascending
    Show
    Criteria Is Null
    Or Is Null
    Is Null



    I then enter criteria into the “Date” and “City” columns. Note, I only search for one “City” at a time.


    I use the MIN Function because “Alpha” also supersedes “Beta.” Therefore in my query, I will have “Alpha” returned, and not “Beta” as “Alpha” comes fist alphabetically.


    I use the FIRST function for City 1, 2, and 3 because I only want what ever city that appears first in my records to be counted. For example, lets look at the two pictures above. I want my query to return the following:

    City1 = New York
    City 2 = London
    City 3 = Null

    Say for example I am searching for Beijing using the query above. I DO NOT want my query to show me Event Num “12” because Beijing is NOT the First City2 that is mentioned.

    Likewise, if I search for Madrid, I DO NOT want to see Event Num “12” because the First City3 mentioned IS “null.”

    My problem is strange. Sometimes it happens, sometimes it doesn’t.

    Basically, my query appears to override the fact that I only care about City1, 2, or 3 AS LONG AS it is the FIRST identified city.

    Additionally, my query appears to be ignoring the fact that City3 is null. It just goes straight to Madrid.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your query references tblAlpha and [Suspected Event] tables. Your narrative references tblMain and tbEvent. How should I reconcile this - which is which? Is tblAlpha synonymous with tblMain? And the fieldnames are different. You say you have 3 city fields (City1, City2, City3) but the query references only City.

    You say 'did not seem to work' so what happens - error, wrong results, nothing?

    Still not making much sense to me.

    First and Last aggregate functions cannot be relied on to give desired results because records have no natural set order as you find in a spreadsheet. If you really care about what value is returned, don't use them. I have found their use okay in CROSSTAB when I know there is only 1 value that could be returned but otherwise I usually avoid.

    If you want to return data from other fields of the record that has the Min() value, will probably need a subquery or use of domain aggregate function. Maybe TOP N will work for you, review http://allenbrowne.com/subquery-01.html#TopN
    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.

  7. #7
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Sorry. I made the narrative referencing tblMain and tblEvent as I thought itd be easier to start fresh and let me explain myself from the beginning. Ignore my original post.

    And I have heard people tell me that First and Last Agg Functions cannot be relied on because records have no natural set order. I am a little confused as to what this means. My understanding is if I have a table with a column of cities, the very first record I enter, which would be Record#1, would be returned if I made a query with a First Agg Func. For example:
    City:
    New York
    Bejeing
    London
    Madrid
    Austin

    If I did a query to find FIRST(City), I would have a return of "New York."


    And can you explain more about needing a subquery? Are you suggesting I have a query with the only function being MIN([Event Type]), City1, City2, City3, [Event Num], Date, & [Add Num]. This would be qryXXX
    And then on another query based on qryXXX called qryYYY. This qry would then have [MinofEvent Type], FIRST(City1), FIRST(City2), FIRST(City3).
    Is this what you are suggesting?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, will probably usually return "New York". Tables are 'buckets'. Records are bolts tossed into the bucket. Don't rely on the order of record creation to be the order records will always have. Records can be filtered and sorted. "New York" may not always be in the first record.

    Did you review the Allen Browne link? If you want to return a single record with all its fields for each group of a particular entity based on the Min(), Max() (or whatever) value in a field, will need subquery or Domain Aggregate function.
    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.

  9. #9
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    Yes. That is exactly what I want. I only want the fields for the record where the MIN was returned.

    And Yes. I did check out the link. The domain aggregate function was confusing to me... Ive tried using multiple queries based on one another (which I belive is what you mean by subquery). However I have not been able to get it right...

  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
    As far as I can figure from your posted query, something like:

    SELECT * FROM TblAlpha WHERE CaseNum & Event IN (SELECT TOP 1 CaseNum & Event FROM TblAlpha AS Dupe WHERE Dupe.CaseNum = TblAlpha.CaseNum ORDER BY Dupe.CaseNum, Dupe.Event DESC);

    If you still need help, provide sample raw data.

    Cross-posted https://www.access-programmers.co.uk...d.php?t=293463
    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.

  11. #11
    zashaikh is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    182
    I tried the above SQL. It worked, however, there is no mention of Dates or Cities.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-13-2014, 06:55 PM
  2. Cannot Have Aggregate function
    By vitordf in forum Queries
    Replies: 4
    Last Post: 08-13-2013, 07:24 AM
  3. ignoring null value in recordset
    By nickblitz in forum Access
    Replies: 10
    Last Post: 11-16-2012, 02:38 AM
  4. Replies: 11
    Last Post: 11-09-2012, 08:17 AM
  5. Aggregate function returns duplicate values
    By lokiluke in forum Queries
    Replies: 3
    Last Post: 09-16-2011, 09:40 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