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

ClimoC

ClimoC

Well-known Member
Joined
Aug 21, 2009
Messages
584
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)

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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
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.
Click to expand...
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.
 
Upvote 0
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.

Code: 
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
 
Upvote 0
...similarly, this:

Code: 
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?
 
Upvote 0
I'm a sql server really, but I'll take a punt:

Code: 
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
 
Upvote 0
Kyle123 said:
I'm a sql server really, but I'll take a punt:

Code: 
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
Click to expand...

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...
 
Upvote 0
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>

 
Upvote 0
Kyle123 said:
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


Click to expand...

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'...
 
Upvote 0
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.
 
Upvote 0
Did you try doing it as a set of two separate queries, like I mentioned in my post?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Creating a recordset via VBA SQL and selecting the vales of a value list column
2
Replies
15
Views
12K
Cudaboy
C
A
  • Question
Add a Date Range to a Select Distinct Query
Replies
1
Views
917
Joe4
Joe4
ClimoC
  • Question
SQL SELECT [UNIQUE/DISTINCT] with nonclustered index in a read-only table
2
Replies
13
Views
4K
ClimoC
ClimoC

Forum statistics

Threads
1,214,642
Messages
6,120,698
Members
448,979
Latest member
DET4492

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top