Earliest Date... 1

[combs]

This thread (thread701-957119) came very close to answering my question, however, when I alter my query as directed, it only returns one record....

I need to return the earliest maintenance record from the database for each "section" ( a street section - if it matters...) Each street section has multiple maintenance records associated with it and I'm after the earliest. My original query would return all records (5,853). Here's a copy of the original query:

SELECT Branch.Name, Section.SectionID, Min([Work Tracking].DATE) AS MinOfDATE, Section.Comments, Section.[_SUNIQUEID]
FROM Branch INNER JOIN (([Section] INNER JOIN _Section ON Section.[_SUNIQUEID] = [_Section].[_SUNIQUEID]) INNER JOIN [Work Tracking] ON Section.[_SUNIQUEID] = [Work Tracking].[_SUNIQUEID]) ON Branch.[_BUNIQUEID] = Section.[_BUNIQUEID]
GROUP BY Branch.Name, Section.SectionID, Section.Comments, Section.[_SUNIQUEID]
ORDER BY Branch.Name, Section.SectionID;

The query that just returns one record (albiet the earliest record in the entire database) is:

SELECT Branch.Name, Section.SectionID, [Work Tracking].DATE, Section.Comments, Section.[_SUNIQUEID]
FROM Branch INNER JOIN ([Section] INNER JOIN [Work Tracking] ON Section.[_SUNIQUEID] = [Work Tracking].[_SUNIQUEID]) ON Branch.[_BUNIQUEID] = Section.[_BUNIQUEID]
WHERE ((([Work Tracking].DATE)=(SELECT Min([Work Tracking].DATE) FROM [Work Tracking])));

I'd appreciate any help that people can offer!!

 

[SkipVought]

Hi,

For any unique SectionID, are the Comments and/or [_SUNIQUEID] ALL the same???

Probably not, I'd guess.

You may want something like

SELECT Branch.Name, Section.SectionID, [Work Tracking].DATE, Section.Comments, Section.[_SUNIQUEID]
FROM Branch INNER JOIN ([Section] INNER JOIN [Work Tracking] ON Section.[_SUNIQUEID] = [Work Tracking].[_SUNIQUEID]) ON Branch.[_BUNIQUEID] = Section.[_BUNIQUEID]
WHERE (((Branch.Name&Section.SectionID&[Work Tracking].DATE)=(SELECT Branch.Name&Section.SectionID&Min([Work Tracking].DATE) FROM [Work Tracking])))
Group By Branch.Name, Section.SectionID;

Branch.Name, Section.SectionID, [Work Tracking].DATE, Section.Comments, Section.[_SUNIQUEID]

Skip,

_[red][/red]

 

[PHV]

Something like this ?
SELECT B.Name, S.SectionID, W.DATE, S.Comments, S.[_SUNIQUEID]
FROM ((Branch AS B
INNER JOIN [Section] AS S ON B.[_BUNIQUEID] = S.[_BUNIQUEID])
INNER JOIN [Work Tracking] AS W ON S.[_SUNIQUEID] = W.[_SUNIQUEID])
INNER JOIN (SELECT I.SectionID, Min(T.DATE) AS EarliestDate
FROM Section AS I INNER JOIN [Work Tracking] AS T ON I.[_SUNIQUEID] = T.[_SUNIQUEID]
GROUP BY I.SectionID) AS E ON S.SectionID = E.SectionID
WHERE W.DATE = E.EarliestDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[combs]

PH,

Thanks for your quick reply. I entered your query as written in the SQL View and it pops up an error message when I try to run it....

The error text is "Syntax error in JOIN operation." The same error message box comes up three times and then goes away and the query opens with no information in it but the field names are present....

I think we're close ... any idea what's causing the error message?

Thanks again for your help!

 

[PHV]

Which version of access ?
Where is the cursor in the SQL code when the error raises ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[combs]

PH,

It's Microsoft Office Access 2003 (11.6566.8036) SP2

The cursor appears on the "I" of S.SectionID:

SELECT B.Name, S.Section[b][COLOR=red]I[/color][/b]D, W.DATE, S.Comments, S.[_SUNIQUEID]
FROM ((Branch AS B INNER JOIN [Section] AS S ON B.[_BUNIQUEID] = S.[_BUNIQUEID]) INNER JOIN [Work Tracking] AS W ON S.[_SUNIQUEID] = W.[_SUNIQUEID]) INNER JOIN [SELECT I.SectionID, Min(T.DATE) AS EarliestDate
FROM Section AS I INNER JOIN [Work Tracking] AS T ON I.[_SUNIQUEID] = T.[_SUNIQUEID]
GROUP BY I.SectionID]. AS E ON S.SectionID = E.SectionID
WHERE W.DATE = E.EarliestDate;

 

[PHV]

... INNER JOIN [!]([/!]SELECT I.SectionID ... GROUP BY I.SectionID[!])[/!] AS E ...

This is a known bug of the query tool.
A workaround is to create a saved query named, say, qryEarliestDate:
SELECT I.SectionID, Min(T.DATE) AS EarliestDate
FROM Section AS I INNER JOIN [Work Tracking] AS T ON I.[_SUNIQUEID] = T.[_SUNIQUEID]
GROUP BY I.SectionID

And now your query:
SELECT B.Name, S.SectionID, W.DATE, S.Comments, S.[_SUNIQUEID]
FROM ((Branch AS B
INNER JOIN [Section] AS S ON B.[_BUNIQUEID] = S.[_BUNIQUEID])
INNER JOIN [Work Tracking] AS W ON S.[_SUNIQUEID] = W.[_SUNIQUEID])
INNER JOIN qryEarliestDate AS E ON S.SectionID = E.SectionID
WHERE W.DATE = E.EarliestDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[combs]

PH,

I took your suggestion and made the two queries. That works, but it now returns 648 records (which, I think, is the correct number of distinct branches) instead of the 2,634 sections and their earliest construction date....

So, it does the correct operation but on the Branches instead of the Sections... At least that's what I think is going on....

I have never fully grasped the whole INNER JOIN notation so I'm kind of hopeless once the SQL goes beyond SELECT .... FROM .... WHERE .... GROUP BY....

On that note, is there a good thread that explains, in depth, the extended query language??

Thanks again for your help on this - these forums are the best!

 

[PHV]

Perhaps this ?
qryEarliestDate :
SELECT [_SUNIQUEID], Min([DATE]) AS EarliestDate
FROM [Work Tracking]
GROUP BY [_SUNIQUEID]

And now your query:
SELECT B.Name, S.SectionID, W.DATE, S.Comments, S.[_SUNIQUEID]
FROM ((Branch AS B
INNER JOIN [Section] AS S ON B.[_BUNIQUEID] = S.[_BUNIQUEID])
INNER JOIN [Work Tracking] AS W ON S.[_SUNIQUEID] = W.[_SUNIQUEID])
INNER JOIN qryEarliestDate AS E ON W.[_SUNIQUEID] = E.[_SUNIQUEID] AND W.DATE = E.EarliestDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[combs]

PH,

Still only gets the 648 branches....

I started messing around with the 1st query (qryEarliestDate) and noticed that it was pulling the correct number of sections out... Modified that to add the other data (in Design View since the SQL View had lost me) and - for whatever reason - that now works beautifully!

Here's the code that works for me:

SELECT Branch.Name, Section.SectionID, [Work Tracking].[_SUNIQUEID], Min([Work Tracking].DATE) AS EarliestDate, UCASE(Section.Comments) As Comments
FROM Branch INNER JOIN ([Section] INNER JOIN [Work Tracking] ON Section.[_SUNIQUEID] = [Work Tracking].[_SUNIQUEID]) ON Branch.[_BUNIQUEID] = Section.[_BUNIQUEID]
GROUP BY Branch.Name, Section.SectionID, [Work Tracking].[_SUNIQUEID], Section.Comments;

Thanks for all your help, PH, I never could have gotten here from there without all your help (yet again)

A star for you!

 

[lespaul]

Check out the Understanding Joins article below...

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins