Is it possible to Stop FIRST Aggregate Function from ignoring null values?
I have not been able to figure it out
Is it possible to Stop FIRST Aggregate Function from ignoring null values?
I have not been able to figure it out
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.
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];
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.
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.
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.
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.
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?
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.
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...
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.
I tried the above SQL. It worked, however, there is no mention of Dates or Cities.