SQL Help

[businesshippieRH]

I need help creating a query. I'm trying to show only the largest Revision number for each "trimmed" RecordName (the last bit of each Record Name is Rev. 1.0, 1.1, etc- hence the trimming).

Using this query as "Query2":

SELECT tbl_Records.RecordName, (Left(tbl_Records.RecordName,17)) AS TrimmedName, tbl_Records.Revision
FROM tbl_Records;

And this as "Query3" works great.

SELECT Query2.RecordName, Query2.TrimmedName, Query2.Revision
FROM Query2
INNER JOIN
(SELECT TrimmedName, Max(Revision) As MaxRev
FROM Query2
GROUP BY TrimmedName) As Max
ON Max.TrimmedName = Query2.TrimmedName
AND Max.MaxRev = Query2.Revision

However, I'm trying to write this to further cut-down another query that builds itself via VBA inputs from multiple comboboxes. As such, I'm trying to figure out how to properly write this without having to rely on a saved query. For some reason, the following does not work:

SELECT tbl_Records.RecordName, (Left(tbl_Records.RecordName,17)) AS TrimmedName, tbl_Records.Revision
FROM tbl_Records
INNER JOIN
(SELECT TrimmedName, Max(Revision) As MaxRev
FROM tbl_Records
GROUP BY TrimmedName) As Max
ON Max.TrimmedName = tbl_Records.TrimmedName
AND Max.MaxRev = tbl_Records.Revision

The error says, "The specified field 'tbl_Records.TrimmedName' could refer to more than one table listed in the FROM clause of your SQL statement. Thanks in advance!

 

[jdraw]

You could try this (untested) - just using an alias in the subquery

SELECT tbl_Records.RecordName, (Left(tbl_Records.RecordName,17)) AS TrimmedName, tbl_Records.Revision
FROM tbl_Records
INNER JOIN
(SELECT TrimmedName, Max(Revision) As MaxRev
FROM tbl_Records as X
GROUP BY TrimmedName) As Max
ON X.TrimmedName = tbl_Records.TrimmedName
AND X.MaxRev = tbl_Records.Revision

 

[businesshippieRH]

I have already given the subquery the alias "Max". Adding another alias where you have it returns an "Error in FROM clause".

It won't allow me to alias the first statement without a "syntax error in union".

 

[jdraw]

I don't understand the error ...union??

Can you post a copy of the database ---don't need all records, and remove anything personal /confidential?

 

[businesshippieRH]

Unfortunately, I can't post the database due to size (even after zipping it). I've pulled out the queries discussed and tables for you to look at (which should be everything necessary for this problem).
Thanks!

 

[jdraw]

Try this

SELECT  Left(RecordName,17) AS TrimmedName, Max(tbl_Records.revision) AS MaxRev
FROM tbl_Records
GROUP BY   Left(RecordName,17)

Also all tables should have a Primary Key.

 

[businesshippieRH]

That works. However, I'll also need a "whole" RecordName to relate it to the other queries. I've found I can make it work by using,

SELECT First(tbl_Records.RecordName) AS RecordName,...

But that simply using doesn't work.

SELECT tbl_Records.RecordName,...

Could you explain this difference so that I can avoid this issue in the future... I just can't wrap my head around why this matters (I'm new to SQL and learning "as I go"...).

Thanks again!

 

[jdraw]

Here's the sql to give you all fields

select
 recordname
, left(recordname,17) as TrimmedName
 , revision
 from tbl_records
 where
revision [COLOR="Blue"][B]IN (select max(revision) from tbl_records  group by Left(recordName,17))[/B][/COLOR]

The IN subquery identifies those Revision values that are the Max(revision) for each Group.

see w3chools sql tutorial for lots of samples and you can try adjusting samples on your own

 

[businesshippieRH]

Thanks again. That syntax is exactly what I was looking for! I'll certainly look into working through the w3chools tutorials in my "free time" (If I can manage to find it first).

 

[jdraw]

Update to your sql issue. probably too late, but I had time to review the requirement and have it working.

I created a table definition with same structure as your table, and I added an autonumber primary key field called MyID.

I then set up a series of SQL statements to get the full recordname, the trimmed name and the MaxRevision per group (trimmedName). It works with your database.

SELECT  Left(recordname,17) AS trimmedName,*
FROM  [tbl_Records] where MyID In
(
SELECT Max(XX.MyId) AS MaxOfMyId
FROM 
  (SELECT Left([RecordName],17) AS Trimd
  , Max([tbl_Records].Revision) AS MaxOfRevision
  , [tbl_Records].MyID
  FROM [tbl_Records]
  GROUP BY Left([RecordName],17)
  ,[tbl_Records].MyID
  )  AS XX
GROUP BY XX.trimd
);

Here are some of the results: (see attached graphic for more readable version) I have tried to anonymize the data displayed here

trimmedName RecordName RecordDistinction RecordDateMONTH RecordDateYEAR RecMoYr RecordNumber Revision RevisionNotes Title Author Description OrigFileLoc ProjectManager ChargeCode PrimeContractNumber TaskOrder UploadDate CancelDate CancelNotes Site Name MyID
TADC-TR-2014-0001 TADC-TR-2014-0001, Rev. 1.0 TR 06 2014 06/01/2014 0001 1.0 Final Data Gap Sampling Report XXX WWWWWW "VVVV Environmental Associates, Inc. (VVVV) is working under subcontract to Trity Analysis Group
& Development Corp. (TRITY), who has been retained by the .......

All tables should have a primary key.


Good luck.

 

[businesshippieRH]

Sorry for the slow response: I've caught a bit of a cold and haven't been able to devote enough brain power to this problem. Thanks again. That works beautifully and actually gives me insight into why I was having the issues I was in the first place. The table now has a primary key (advice I will be taking to heart), and the SQL does as intended. Thank you for all your help.