Trouble with Aggregate FIRST function and query (1 Viewer)

Hello All.

I am trying to run a query, but am coming across an anomaly. Before I begin, I would like to ask one thing. I understand that my database may not be designed the best. And I sincerely do appreciate the advice. However, I am not here to make my database fit the proper standardized parameters. I, frankly, am not allowed to do this. Therefore, I am trying to get around issues by creating complicated queries. So while the advice on how the database should be is appreciated, please do not tell me that my database design is flawed.

Moving on.. 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.


<see attachment in the zipped folder>







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:

<please see other zipped folder for pic of my query>


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.

Other image attached

First and Last should die in an industrial fire. Slowly. And painfully. Never use them. They don't do what people think they do.

Instead, you most likely need a subquery to find the "First" value using Min instead. To help you with that, I would need better example data--you lost me in your long explanation and screenshots. Instead post data into the forum like so:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
Sally, 17, 2/4/2016
David, 34, 2/7/2016


I will need 2 sets of data:

A. Starting sample data from your table(s). Include table and field name like above and enough sample data to cover all cases.

B. Expected results of A. Show me what data you want to end up with when you feed your query the data in A. Don't explain it to me, post the exact data to show me.

tblMain
Event Num, Date, Additional Num
12, Jan1, 999
12, Jan1, 999
13, Sep4, <null>
14, Sep30, 82
14, Sep30, 82
15, Jun4, <null>

tblEvent
Event Num, Event Type, City1, City2, City3
12, Alpha, London, Beijing, New York
12, Beta, London, <null>, <null>
13, Gamma, London, <null>, <null>
14, Alpha, Madrid, <null>, <null>
14, Beta, Madrid, London, <nill>
15, Beta, Seattle, London

This is an example of both tables.

I will now explain my query.

My criteria is “London” I want a query to give me the following:

Event Num, Date, Event Type, City1, City2, City3
12, Jan1, Alpha, London, Beijing, New York
13, Sep4, Gamma, London, <null>, <null>
15, Jun4, Beta, Seattle, London, <null>


As you can see, EventNum”14” is NOT included in my results above because I am not concerned with London being linked to a Beta-Type-Event. I care only if it is Alpha. I use the MIN function because Alpha comes before Beta and Gamma alphaebetically.


----------

I really hope this makes sense. I feel like its even more confusing like this.

No it doesn't make sense. Why is 14 excluded from the original results?

1. It has an Alpha event.

2. I can see no logic that would include #12 but exclude #14.

3. #15 has just a Beta event, yet its expected in the results.

14 is excluded from the results because “London” is attached to the “Beta” EventType. NOT the “Alpha Event” type.

#15 is expected because the MIN(Event Type) = “Beta”. There is no “Alpha” to come alphabetically first before “Beta”

Ok, thanks, that makes sense. It is going to require a subquery to get the minimum event type:



Paste that into a new query and call it 'sub1'. Then to get your data use this query:


Of course this might produce unexpected results when you have distinct records in tblMain for and Event Num.