Brain explosion - Access SQL Select with: MIN date value, grouped by another field, BUT return ALL rows

[ClimoC]

Hey hey hey

This has had me flustered for nearly the whole day and I'm at my wits' end.

A table has multiple 'titleids' (just a number column), with some different dates and some the same (so you could see the same titleid for twice on the 1st Jan, once on the 2nd Jan, once on the 5th Jan)

I need to get the single earliest (by date) occurrence of each titleid - though I need to return the other 20 odd columns in the table as well.


This works (I think... looks okay) for returning the earliest occurrence of each titleid, uniquely. But I cannot for the life of me get the JOIN to work (so far as in that I've given up even trying)

SELECT ti.mydate as 'My_Date', ti.titleid AS 'TITLE_ID' FROM (SELECT DISTINCT rp.titleid, rp.mydate FROM [work] rp ORDER BY rp.mydate ASC) ti

But I can't get the JOIN going!!!

If I can't use a 'SELECT *' in the join, for the sake of an example/pointing in the right direction, a couple of the field anmes will be 'status', 'assigned', 'archive'

Can anyone help? Despite being a VBA guru (relatively) around my place, and being handy with ORACLE SQL, I can't get access to behave

Thanks
C

 

[Joe4]

I need to get the single earliest (by date) occurrence of each titleid - though I need to return the other 20 odd columns in the table as well.

Here is an easy way:

First create an Aggregate Query to get the earliest date for each titleid.
That just entails creating a query with just those two field, titleid and your date field.
Make it an Aggregate Query, grouping on titleid and taking the minimum date value.

Then, create a second query joining your original table to the query you just created, joining on both the titleid and date fields, and return whatever fields you want.

If you really want it in one single query instead of using two queries to do it, just nest the SQL code from the first inside the second.

 

[ClimoC]

Thanks Joe - but that's basically what my question is - I'm trying to do that, but the JOINs never work. I do them all the time in ORACLE, and though I've read stuff about parentheses on multiple JOINs, I'm only doing one, and I get all sorts of errors ranging from 'error in FROM syntax' to '[field] is not an aggregate' etc

e.g.

SELECT min(ti.txdate) as 'TX_Date', min(ti.titleid) AS 'TITLE_ID' FROM (SELECT DISTINCT rp.titleid, rp.txdate FROM [work] rp ORDER BY rp.txdate ASC) ti JOIN (SELECT pp.titleid, pp.title, pp.status, pp.assigned, pp.allocated, pp.archive FROM [work] pp) ON pp.titleid = ti.titleid

...gives 'Syntax error in FROM clause'

And it kinda does need to be in one query - it's for a linked pivot table

 

[ClimoC]

...similarly, this:

SELECT DISTINCT min(ti.txdate) as TX_Date, ti.titleid as TitleID, pp.titleid, pp.status, pp.title, pp.assigned FROM [work] ti LEFT JOIN [work] pp ON pp.titleid = ti.titleid

...returns: "You tried to execute a query that does not include the specified expression 'titleid' as part of an aggregate function'

But I don't want ​aggregate that... do I?

 

[Kyle123]

I'm a sql server really, but I'll take a punt:

SELECT DISTINCT min(ti.txdate) as TX_Date, ti.titleid as TitleID, pp.titleid, pp.status, pp.title, pp.assigned FROM [work] ti LEFT JOIN [work] pp ON pp.titleid = ti.titleid GROUP BY ti.titleid,  pp.status, pp.title, pp.assigned

 

[ClimoC]

I'm a sql server really, but I'll take a punt:

SELECT DISTINCT min(ti.txdate) as TX_Date, ti.titleid as TitleID, pp.titleid, pp.status, pp.title, pp.assigned FROM [work] ti LEFT JOIN [work] pp ON pp.titleid = ti.titleid GROUP BY ti.titleid,  pp.status, pp.title, pp.assigned

Thanks Kyle, but returns "You tried to execute a query that does not include the specified expression 'titleid' as part of an aggregate function"

I seem to get that error a lot...

 

[Kyle123]

Oops missed one out of the group by clause, see if you have any luck with this:

<font face="Courier New" size="2">
<font color = "blue">SELECT</font> <font color = "blue">DISTINCT</font> <font color = "#FF0080"><b>Min</b></font><font color = "maroon">(</font><font color = "maroon">ti</font><font color = "silver">.</font><font color = "maroon">txdate</font><font color = "maroon">)</font> <font color = "blue">AS</font> <font color = "maroon">TX_Date</font><font color = "silver">,</font>
<br/>                <font color = "maroon">ti</font><font color = "silver">.</font><font color = "maroon">titleid</font>     <font color = "blue">AS</font> <font color = "maroon">TitleID</font><font color = "silver">,</font>
<br/>                <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">titleid</font><font color = "silver">,</font>
<br/>                <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">status</font><font color = "silver">,</font>
<br/>                <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">title</font><font color = "silver">,</font>
<br/>                <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">assigned</font>
<br/><font color = "blue">FROM</font>   <font color = "maroon">[work]</font> <font color = "maroon">ti</font>
<br/>       <font color = "blue">LEFT</font> <font color = "blue">JOIN</font> <font color = "maroon">[work]</font> <font color = "maroon">pp</font>
<br/>              <font color = "blue">ON</font> <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">titleid</font> <font color = "silver">=</font> <font color = "maroon">ti</font><font color = "silver">.</font><font color = "maroon">titleid</font>
<br/><font color = "blue">GROUP</font>  <font color = "blue">BY</font> <font color = "maroon">ti</font><font color = "silver">.</font><font color = "maroon">titleid</font><font color = "silver">,</font>
<br/>          <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">status</font><font color = "silver">,</font>
<br/>          <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">title</font><font color = "silver">,</font>
<br/>          <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">titleid</font><font color = "silver">,</font>
<br/>          <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">assigned</font> 
</font>

 

[ClimoC]

Oops missed one out of the group by clause, see if you have any luck with this:


SELECT DISTINCT Min(ti.txdate) AS TX_Date,

ti.titleid AS TitleID,

pp.titleid,

pp.status,

pp.title,

pp.assigned

FROM [work] ti

LEFT JOIN [work] pp

ON pp.titleid = ti.titleid

GROUP BY ti.titleid,

pp.status,

pp.title,

pp.titleid,

pp.assigned

Cheers Kyle

It works as a query, but doesn't return what I need.

My feeling, or at least the behaviour, suggests that Access is treating the whole SELECT statement as DISTINCT - The only two fields that DISTINCT should be applied to are txdate and titleid - but the returned set from the above brings back multiples because on the same txdate, there are 3 of the same titleid, but with different statuses - so they are all returned because the DISTINCT and GROUP BY statements make them as such. Removing these from the GROUP BY statement throws the 'blah blah blah not part of an aggregate query...' thing again. I think I need to NEST a SELECT statement here somewhere, and then do the JOIN.... don't I?

The other thing I'll need to throw in here at some point is the line 'WHERE archive = 0'...

 

[Kyle123]

The aggregation forces the DISTINCT, otherwise there'd be nothing to aggregate. So in T-SQL which will be closer to access than oracle sql the query would look like this (if it helps):

<font face="Courier New" size="2">
<font color = "blue">SELECT</font> <font color = "maroon">ti</font><font color = "silver">.</font><font color = "maroon">tx_date</font><font color = "silver">,</font>
<br/>       <font color = "maroon">ti</font><font color = "silver">.</font><font color = "maroon">titleid</font><font color = "silver">,</font>
<br/>       <font color = "maroon">PP</font><font color = "silver">.</font><font color = "silver">*</font>
<br/><font color = "blue">FROM</font>   <font color = "maroon">(</font><font color = "blue">SELECT</font> <font color = "fuchsia"><i>Min</i></font><font color = "maroon">(</font><font color = "maroon">txdate</font><font color = "maroon">)</font><font color = "silver">,</font>
<br/>               <font color = "maroon">titleid</font>
<br/>        <font color = "blue">FROM</font>   <font color = "maroon">[work]</font>
<br/>        <font color = "blue">GROUP</font>  <font color = "blue">BY</font> <font color = "maroon">titleid</font><font color = "maroon">)</font> <font color = "maroon">ti</font>
<br/>       <font color = "blue">LEFT</font> <font color = "blue">JOIN</font> <font color = "maroon">[work]</font> <font color = "maroon">pp</font>
<br/>              <font color = "blue">ON</font> <font color = "maroon">pp</font><font color = "silver">.</font><font color = "maroon">titleid</font> <font color = "silver">=</font> <font color = "maroon">ti</font><font color = "silver">.</font><font color = "maroon">titleid</font> 
</font>

I don't know about access sql though, it's nasty and gets fussy about parenthesis.

 

[Joe4]

Did you try doing it as a set of two separate queries, like I mentioned in my post?