Query to return only records with highest value?

M

Marc T

Hi All,

I have a query to return documentation info (doc number, revision etc). What
I'd like to do is have a query that only returns the latest revisions of the
documents. The field that gives this is a numerical field - REV_NO that is a
revision counter, but I'm completely stumped as a relative newbie to access!

Here's my current SQL query:

SELECT I.DOC_CLASS, I.DOC_NO, I.DOC_REV, T.TITLE, I.DOC_REV_TEXT, I.REV_NO,
I.DOC_SHEET, I.DT_PLANNED_FINISH, I.DT_PLANNED_START, I.DT_RELEASED
FROM User_Selection, IFSAPP_DOC_ISSUE As I INNER JOIN IFSAPP_DOC_TITLE As T
ON I.DOC_NO = T.DOC_NO
WHERE (((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
ORDER BY I.DOC_NO;


Any advice would be much appreciated!
Marc
 
M

Marc T

Forgot to add this example

Current query:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 A 1
A 0001 B 2
A 0002 A 1
A 0002 B 2
A 0002 C 3

Ideally I'd like:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 B 2
A 0002 C 3

Cheers!
 
D

Duane Hookom

Try something like:
SELECT I.DOC_CLASS, I.DOC_NO, I.DOC_REV, T.TITLE, I.DOC_REV_TEXT, I.REV_NO,
I.DOC_SHEET, I.DT_PLANNED_FINISH, I.DT_PLANNED_START, I.DT_RELEASED
FROM User_Selection, IFSAPP_DOC_ISSUE As I INNER JOIN IFSAPP_DOC_TITLE As T
ON I.DOC_NO = T.DOC_NO
WHERE (((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
AND Rev_No = (SELECT Max(Rev_No) FROM IFSAPP_DOC_ISSUE I2 WHERE I.DOC_NO =
I2.DOC_NO)
ORDER BY I.DOC_NO;

--
Duane Hookom
Microsoft Access MVP


Marc T said:
Forgot to add this example

Current query:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 A 1
A 0001 B 2
A 0002 A 1
A 0002 B 2
A 0002 C 3

Ideally I'd like:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 B 2
A 0002 C 3

Cheers!

Marc T said:
Hi All,

I have a query to return documentation info (doc number, revision etc). What
I'd like to do is have a query that only returns the latest revisions of the
documents. The field that gives this is a numerical field - REV_NO that is a
revision counter, but I'm completely stumped as a relative newbie to access!

Here's my current SQL query:

SELECT I.DOC_CLASS, I.DOC_NO, I.DOC_REV, T.TITLE, I.DOC_REV_TEXT, I.REV_NO,
I.DOC_SHEET, I.DT_PLANNED_FINISH, I.DT_PLANNED_START, I.DT_RELEASED
FROM User_Selection, IFSAPP_DOC_ISSUE As I INNER JOIN IFSAPP_DOC_TITLE As T
ON I.DOC_NO = T.DOC_NO
WHERE (((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
ORDER BY I.DOC_NO;


Any advice would be much appreciated!
Marc
Click to expand...
Click to expand...
 
M

Marc T

Hi Duane,

thanks for reply! The query works, but there's a bit of an issue sadly (my
fault). I forgot to mention in the original post that my query returns circa
8000 records. Adding the sub query as you've shown locks my system up for a
long time as it performs the query (running sub query for each record
returned by the main query?)

Are there any alternatives that might not need the sub query?

Thanks for the help,
Marc

Duane Hookom said:
Try something like:
SELECT I.DOC_CLASS, I.DOC_NO, I.DOC_REV, T.TITLE, I.DOC_REV_TEXT, I.REV_NO,
I.DOC_SHEET, I.DT_PLANNED_FINISH, I.DT_PLANNED_START, I.DT_RELEASED
FROM User_Selection, IFSAPP_DOC_ISSUE As I INNER JOIN IFSAPP_DOC_TITLE As T
ON I.DOC_NO = T.DOC_NO
WHERE (((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
AND Rev_No = (SELECT Max(Rev_No) FROM IFSAPP_DOC_ISSUE I2 WHERE I.DOC_NO =
I2.DOC_NO)
ORDER BY I.DOC_NO;

--
Duane Hookom
Microsoft Access MVP


Marc T said:
Forgot to add this example

Current query:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 A 1
A 0001 B 2
A 0002 A 1
A 0002 B 2
A 0002 C 3

Ideally I'd like:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 B 2
A 0002 C 3

Cheers!

Marc T said:
Hi All,

I have a query to return documentation info (doc number, revision etc). What
I'd like to do is have a query that only returns the latest revisions of the
documents. The field that gives this is a numerical field - REV_NO that is a
revision counter, but I'm completely stumped as a relative newbie to access!

Here's my current SQL query:

SELECT I.DOC_CLASS, I.DOC_NO, I.DOC_REV, T.TITLE, I.DOC_REV_TEXT, I.REV_NO,
I.DOC_SHEET, I.DT_PLANNED_FINISH, I.DT_PLANNED_START, I.DT_RELEASED
FROM User_Selection, IFSAPP_DOC_ISSUE As I INNER JOIN IFSAPP_DOC_TITLE As T
ON I.DOC_NO = T.DOC_NO
WHERE (((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
ORDER BY I.DOC_NO;


Any advice would be much appreciated!
Marc
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Make sure Rev_No is indexed as well as DOC_NO and Proj_ID.

I would also change:
(((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
to
(((Left([I.DOC_NO],5))=[User_Selection].[Proj_ID]))


--
Duane Hookom
Microsoft Access MVP


Marc T said:
Hi Duane,

thanks for reply! The query works, but there's a bit of an issue sadly (my
fault). I forgot to mention in the original post that my query returns circa
8000 records. Adding the sub query as you've shown locks my system up for a
long time as it performs the query (running sub query for each record
returned by the main query?)

Are there any alternatives that might not need the sub query?

Thanks for the help,
Marc

Duane Hookom said:
Try something like:
SELECT I.DOC_CLASS, I.DOC_NO, I.DOC_REV, T.TITLE, I.DOC_REV_TEXT, I.REV_NO,
I.DOC_SHEET, I.DT_PLANNED_FINISH, I.DT_PLANNED_START, I.DT_RELEASED
FROM User_Selection, IFSAPP_DOC_ISSUE As I INNER JOIN IFSAPP_DOC_TITLE As T
ON I.DOC_NO = T.DOC_NO
WHERE (((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
AND Rev_No = (SELECT Max(Rev_No) FROM IFSAPP_DOC_ISSUE I2 WHERE I.DOC_NO =
I2.DOC_NO)
ORDER BY I.DOC_NO;

--
Duane Hookom
Microsoft Access MVP


Marc T said:
Forgot to add this example

Current query:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 A 1
A 0001 B 2
A 0002 A 1
A 0002 B 2
A 0002 C 3

Ideally I'd like:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 B 2
A 0002 C 3

Cheers!

:

Hi All,

I have a query to return documentation info (doc number, revision etc). What
I'd like to do is have a query that only returns the latest revisions of the
documents. The field that gives this is a numerical field - REV_NO that is a
revision counter, but I'm completely stumped as a relative newbie to access!

Here's my current SQL query:

SELECT I.DOC_CLASS, I.DOC_NO, I.DOC_REV, T.TITLE, I.DOC_REV_TEXT, I.REV_NO,
I.DOC_SHEET, I.DT_PLANNED_FINISH, I.DT_PLANNED_START, I.DT_RELEASED
FROM User_Selection, IFSAPP_DOC_ISSUE As I INNER JOIN IFSAPP_DOC_TITLE As T
ON I.DOC_NO = T.DOC_NO
WHERE (((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
ORDER BY I.DOC_NO;


Any advice would be much appreciated!
Marc
Click to expand...
Click to expand...
Click to expand...
 
M

Marc T

Thanks for the help Duane, I think I have this all figured out now!

I wasn't able to add indexes to the linked tables, but instead turned my
original query into a make table query, then used your tip below to add
indexes and grab latest revisions.

Marc

Duane Hookom said:
Make sure Rev_No is indexed as well as DOC_NO and Proj_ID.

I would also change:
(((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
to
(((Left([I.DOC_NO],5))=[User_Selection].[Proj_ID]))


--
Duane Hookom
Microsoft Access MVP


Marc T said:
Hi Duane,

thanks for reply! The query works, but there's a bit of an issue sadly (my
fault). I forgot to mention in the original post that my query returns circa
8000 records. Adding the sub query as you've shown locks my system up for a
long time as it performs the query (running sub query for each record
returned by the main query?)

Are there any alternatives that might not need the sub query?

Thanks for the help,
Marc

Duane Hookom said:
Try something like:
SELECT I.DOC_CLASS, I.DOC_NO, I.DOC_REV, T.TITLE, I.DOC_REV_TEXT, I.REV_NO,
I.DOC_SHEET, I.DT_PLANNED_FINISH, I.DT_PLANNED_START, I.DT_RELEASED
FROM User_Selection, IFSAPP_DOC_ISSUE As I INNER JOIN IFSAPP_DOC_TITLE As T
ON I.DOC_NO = T.DOC_NO
WHERE (((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
AND Rev_No = (SELECT Max(Rev_No) FROM IFSAPP_DOC_ISSUE I2 WHERE I.DOC_NO =
I2.DOC_NO)
ORDER BY I.DOC_NO;

--
Duane Hookom
Microsoft Access MVP


:

Forgot to add this example

Current query:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 A 1
A 0001 B 2
A 0002 A 1
A 0002 B 2
A 0002 C 3

Ideally I'd like:

DOC_CLASS DOC_NO DOC_REV REV_NO
A 0001 B 2
A 0002 C 3

Cheers!

:

Hi All,

I have a query to return documentation info (doc number, revision etc). What
I'd like to do is have a query that only returns the latest revisions of the
documents. The field that gives this is a numerical field - REV_NO that is a
revision counter, but I'm completely stumped as a relative newbie to access!

Here's my current SQL query:

SELECT I.DOC_CLASS, I.DOC_NO, I.DOC_REV, T.TITLE, I.DOC_REV_TEXT, I.REV_NO,
I.DOC_SHEET, I.DT_PLANNED_FINISH, I.DT_PLANNED_START, I.DT_RELEASED
FROM User_Selection, IFSAPP_DOC_ISSUE As I INNER JOIN IFSAPP_DOC_TITLE As T
ON I.DOC_NO = T.DOC_NO
WHERE (((Mid([I.DOC_NO],1,5))=[User_Selection].[Proj_ID]))
ORDER BY I.DOC_NO;


Any advice would be much appreciated!
Marc
Click to expand...
Click to expand...
Click to expand...
 
Top