top 3 values Subquery problems

[bigbore50]

Problem with query and subquery

I want to find the top three dates of [qry_ audit-rep-all].COMPLETED

for each [qry_audits-needed-dlr-info].[5-Digit Dealership Number]

SELECT DISTINCT [qry_audits-needed-dlr-info].[5-Digit Dealership
Number], [qry_ audit-rep-all].[4-DIGIT], [qry_ audit-rep-all].[AUDIT
REP], [qry_ audit-rep-all].[Audit Rep 2], [qry_
audit-rep-all].COMPLETED
FROM [qry_ audit-rep-all] INNER JOIN [qry_audits-needed-dlr-info] ON
[qry_ audit-rep-all].PRIMARY_NUMBER =
[qry_audits-needed-dlr-info].[5-Digit Dealership Number]
ORDER BY [qry_ audit-rep-all].COMPLETED DESC;

This code shows all the previous completer dates

I know i need to form a subquery but i can't seem to get one to work...

This is what i want it to do

PDN Completed
12345 12/25/2006
12345 11/22/2006
12345 10/21/2006
23456 11/28/2006
23456 8/23/2006
23456 2/20/2006

I want it to do the above.... I can't seem to get it to sort the top 3
completed dates
of each PDN

p.s. there will be values in the middle but they don't matter for now


Does this make sense

 

[Marshall Barton]

Problem with query and subquery

I want to find the top three dates of [qry_ audit-rep-all].COMPLETED

for each [qry_audits-needed-dlr-info].[5-Digit Dealership Number]

SELECT DISTINCT [qry_audits-needed-dlr-info].[5-Digit Dealership
Number], [qry_ audit-rep-all].[4-DIGIT], [qry_ audit-rep-all].[AUDIT
REP], [qry_ audit-rep-all].[Audit Rep 2], [qry_
audit-rep-all].COMPLETED
FROM [qry_ audit-rep-all] INNER JOIN [qry_audits-needed-dlr-info] ON
[qry_ audit-rep-all].PRIMARY_NUMBER =
[qry_audits-needed-dlr-info].[5-Digit Dealership Number]
ORDER BY [qry_ audit-rep-all].COMPLETED DESC;

This code shows all the previous completer dates

I know i need to form a subquery but i can't seem to get one to work...

This is what i want it to do

PDN Completed
12345 12/25/2006
12345 11/22/2006
12345 10/21/2006
23456 11/28/2006
23456 8/23/2006
23456 2/20/2006

I want it to do the above.... I can't seem to get it to sort the top 3
completed dates of each PDN

It think(?) this will do what you asked:

SELECT D.[5-Digit Dealership Number],
A.[4-DIGIT], A.[AUDIT REP],
A.[Audit Rep 2], A.COMPLETED
FROM [qry_ audit-rep-all] As A
INNER JOIN [qry_audits-needed-dlr-info] As D
ON A.PRIMARY_NUMBER = D.[5-Digit Dealership Number]
WHERE A.COMPLETED IN(
SELECT TOP 3 XA.COMPLETED
FROM [qry_ audit-rep-all] As XA
INNER JOIN [qry_audits-needed-dlr-info] As XD
ON XA.PRIMARY_NUMBER = XD.[5-Digit Dealership Number]
WHERE XD.[5-Digit Dealership Number =
D.[5-Digit Dealership Number
ORDER BY XA.COMPLETED DESC)
ORDER BY D.[5-Digit Dealership Number], A.COMPLETED DESC

But I don't understand why you are joining to
[qry_audits-needed-dlr-info] just to pick up the value of
[5-Digit Dealership Number], which is the same as the
PRIMARY_NUMBER in [qry_ audit-rep-all] Maybe you have
something I don't understand going on here, but it seems
that this should produce the same dataset:

SELECT A.PRIMARY_NUMBER,
A.[4-DIGIT], A.[AUDIT REP],
A.[Audit Rep 2], A.COMPLETED
FROM [qry_ audit-rep-all] As A
WHERE A.COMPLETED IN(
SELECT TOP 3 XA.COMPLETED
FROM [qry_ audit-rep-all] As XA
WHERE A.PRIMARY_NUMBER = XA.PRIMARY_NUMBER
ORDER BY XA.COMPLETED DESC)
ORDER BY A.PRIMARY_NUMBER, A.COMPLETED DESC

 

[bigbore50]

Marsh,

Thank you very much

The second bit of code is what i needed
I was just thinking of it wrong

Thanks a lot

Problem with query and subquery

I want to find the top three dates of [qry_ audit-rep-all].COMPLETED

for each [qry_audits-needed-dlr-info].[5-Digit Dealership Number]

SELECT DISTINCT [qry_audits-needed-dlr-info].[5-Digit Dealership
Number], [qry_ audit-rep-all].[4-DIGIT], [qry_ audit-rep-all].[AUDIT
REP], [qry_ audit-rep-all].[Audit Rep 2], [qry_
audit-rep-all].COMPLETED
FROM [qry_ audit-rep-all] INNER JOIN [qry_audits-needed-dlr-info] ON
[qry_ audit-rep-all].PRIMARY_NUMBER =
[qry_audits-needed-dlr-info].[5-Digit Dealership Number]
ORDER BY [qry_ audit-rep-all].COMPLETED DESC;

This code shows all the previous completer dates

I know i need to form a subquery but i can't seem to get one to work...

This is what i want it to do

PDN Completed
12345 12/25/2006
12345 11/22/2006
12345 10/21/2006
23456 11/28/2006
23456 8/23/2006
23456 2/20/2006

I want it to do the above.... I can't seem to get it to sort the top 3
completed dates of each PDN

It think(?) this will do what you asked:

SELECT D.[5-Digit Dealership Number],
A.[4-DIGIT], A.[AUDIT REP],
A.[Audit Rep 2], A.COMPLETED
FROM [qry_ audit-rep-all] As A
INNER JOIN [qry_audits-needed-dlr-info] As D
ON A.PRIMARY_NUMBER = D.[5-Digit Dealership Number]
WHERE A.COMPLETED IN(
SELECT TOP 3 XA.COMPLETED
FROM [qry_ audit-rep-all] As XA
INNER JOIN [qry_audits-needed-dlr-info] As XD
ON XA.PRIMARY_NUMBER = XD.[5-Digit Dealership Number]
WHERE XD.[5-Digit Dealership Number =
D.[5-Digit Dealership Number
ORDER BY XA.COMPLETED DESC)
ORDER BY D.[5-Digit Dealership Number], A.COMPLETED DESC

But I don't understand why you are joining to
[qry_audits-needed-dlr-info] just to pick up the value of
[5-Digit Dealership Number], which is the same as the
PRIMARY_NUMBER in [qry_ audit-rep-all] Maybe you have
something I don't understand going on here, but it seems
that this should produce the same dataset:

SELECT A.PRIMARY_NUMBER,
A.[4-DIGIT], A.[AUDIT REP],
A.[Audit Rep 2], A.COMPLETED
FROM [qry_ audit-rep-all] As A
WHERE A.COMPLETED IN(
SELECT TOP 3 XA.COMPLETED
FROM [qry_ audit-rep-all] As XA
WHERE A.PRIMARY_NUMBER = XA.PRIMARY_NUMBER
ORDER BY XA.COMPLETED DESC)
ORDER BY A.PRIMARY_NUMBER, A.COMPLETED DESC