Databases
StackOverflow2013
Postsdbo
POMultiple Rows In Aggregate Function
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple Rows In Aggregate Function
    primarykey
    data
    text
    <p>I have spent a large part of the day browsing this website and many others and have yet to find a solution for my problem.</p> <p>I am currently trying to run a query on MS Access that will pull the following columns:</p> <p></p> <p>The problem is I need the milestone to be the <strong>most recent</strong> milestone. The due_date corresponds to this milestone.</p> <p>Each milestone is entered with an autonumber ID and I have been trying to use this ID to pull together my query.</p> <p>This query:</p> <pre><code>SELECT E.EID, E.[CN], E.[AN], E.Status, MAX(M.ID) AS Milestone_ID FROM [Pilot Milestone] AS M, [PP Engagements] AS E, [Pilot Milestone] AS D WHERE E.EID=M.EID GROUP BY M.EID, E.EID, E.[CN], E.[AN], E.Status, E.[Estimated Hours] </code></pre> <p>Leaves me with the most recent Milestone ID for each EID. However, if I add Due_Dates:</p> <pre><code>SELECT E.EID, E.[CN], E.[AN], E.Status, MAX(M.ID) AS Milestone_ID, M.(Due Date) FROM [Pilot Milestone] AS M, [PP Engagements] AS E, [Pilot Milestone] AS D WHERE E.EID=M.EID GROUP BY M.EID, E.EID, E.[CN], E.[AN], E.Status, E.[Estimated Hours], M.(Due Date) </code></pre> <p>I receive two additional records for EID 30. These are the <strong>only</strong> two records with due dates and <strong>neither</strong> reflect the most recent Milestone ID.</p> <p>Also each EID has multiple Milestones, thus if I enter the Milestone description in my query the same way as I did with Due Date I encounter a similar problem.</p> <p>Any help is greatly appreciated.</p> <p>Thanks</p> <p><strong>REVISED</strong></p> <p>The following query gives me results for my due_dates without any duplicates as I just tested in MS Access 2007. However, the query pulls the most recent date regardless if it is from the most recent milestone.</p> <pre><code>SELECT E.EID, E.[Client Name], E.[Audit Name], E.Status, MAX(M.ID) AS Milestone_ID, ( SELECT MAX ([Due Date]) FROM [Pilot Milestone] AS M2 WHERE M2.EID = M.EID) AS Due_Date FROM [Pilot Milestone] AS M, [Potential Pilot Engagements] AS E WHERE E.EID=M.EID GROUP BY M.EID, E.EID, E.[Client Name], E.[Audit Name], E.Status, E.[Estimated Hours] </code></pre> <p>Also, I am unsure how to add the Milestone description to the result without either adding it as part of the aggregate function (resulting in duplicates) or adding it in a subquery that results in more than one record and an error</p> <pre><code>SELECT E.EID, E.[Client Name], E.[Audit Name], E.Status, MAX(M.ID) AS Milestone_ID, ( SELECT MAX ([Due Date]) FROM [Pilot Milestone] AS M2 WHERE M2.EID = M.EID) AS Due_Date, ( SELECT (Milestone) FROM [Pilot Milestone] AS M3 WHERE M3.EID = M.EID) AS Milestone FROM [Pilot Milestone] AS M, [Potential Pilot Engagements] AS E WHERE E.EID=M.EID GROUP BY M.EID, E.EID, E.[Client Name], E.[Audit Name], E.Status, E.[Estimated Hours] </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USIan clark
    1. USIan clark
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COUnless you add a condition, you're getting a cartesian product due to the `D` reference to `Pilot Milestone`. This is invisible due to the `GROUP BY`, but is costing you in performance time (especially as you're not using the reference). This is why you should always explicitly qualify your joins, and not use the implicit (comma-separated `FROM` clause) syntax. Showing source tables/sample data and desired results may help us help you, as will the version of Access you're using. You also shouldn't need to group by both `M.EID` _and_ `E.EID`, given they'll be identical for the given row.
      singulars
      1. POMultiple Rows In Aggregate Function
      1. USClockwork-Muse
    2. COThank you, I accidently wrote down the query on here while looking at the wrong parts of my notepad resulting in the Pilot Milestone AS D mistake. I am just copying from Access (2007) now
      singulars
      1. POMultiple Rows In Aggregate Function
      1. USIan clark
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload