Extracting unqiue entries from a query

A

annysjunkmail

Hi,

I have been struggling with this query for a while and need some help
from the experts!
I am trying to extract unique records from the following sample list

RefID QtrDate TargetNameTargetID Value
1 30-Jun-05 Target 1 010574 13
1 31-Mar-07 Target 1 010574 16.5
1 31-Dec-07 Target 1 010574 17
1 30-Jun-06 Target 1 010574 20
1 30-Sep-06 Target 1 010574 21
1 31-Mar-06 Target 1 010574 25
2 30-Jun-07 Target 1 010689 4
2 31-Mar-07 Target 1 010689 6
3 30-Sep-05 Target 1 011010 1
3 31-Dec-07 Target 1 011010 1.5
3 31-Dec-07 Target 2 011019 8
3 31-Dec-07 Target 3 011031 1.5
4 31-Dec-07 Target 4 012011 6.5
5 31-Mar-05 Target 1 013069 6.5
5 30-Sep-07 Target 1 013069 2
5 31-Dec-06 Target 1 013069 4.5
6 31-Dec-05 Target 1 013069 6.5
6 31-Dec-06 Target 1 013069 10
6 31-Mar-07 Target 1 013069 11.5

which should read like this...

RefID QtrDate TargetNameTargetID Value
1 31-Dec-07 Target 1 010574 17
2 30-Jun-07 Target 1 010689 4
3 31-Dec-07 Target 1 011010 1.5
3 31-Dec-07 Target 2 011019 8
3 31-Dec-07 Target 3 011031 1.5
4 31-Dec-07 Target 4 012011 6.5
5 30-Sep-07 Target 1 013069 2
6 31-Mar-07 Target 1 013069 11.5

I am trying to extract unqiue records based on the following logic.

For each RefID show the value by most recent QtrDate where TargetID is
unqiue


Hoping for some expert help

Chris
 
J

John Spencer

Perhaps the following might work - As long as your table name consists of
ONLY letters, numbers (not in first position), and underscores.

SELECT Y.*
FROM YourTable as Y
INNER JOIN
(SELECT RefID, TargetID, Max(QtrDate) as MaxQ
FROM YourTable) as T
ON Y.RefID = T.RefID
AND Y.TargetID = TargetID
AND Y.QtrDate = T.MaxQ

If your table name does not follow the rules, you will need two queries.
Query one will get the information in the subquery. And then you can use
that joined to your table to get the results you want.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
A

annysjunkmail

Perhaps the following might work - As long as your table name consists of
ONLY letters, numbers (not in first position), and underscores.

SELECT Y.*
FROM YourTable as Y
INNER JOIN
   (SELECT RefID, TargetID, Max(QtrDate) as MaxQ
    FROM YourTable) as T
ON Y.RefID = T.RefID
AND Y.TargetID = TargetID
AND Y.QtrDate = T.MaxQ

If your table name does not follow the rules, you will need two queries.
Query one will get the information in the subquery.  And then you can use
that joined to your table to get the results you want.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.














- Show quoted text -

Hi John,
Thanks for your reply.
My table name follows your rules (it's called
tblProjectActualIndicator) but the query is returning an error saying
"join expression not supported" with the following being highlighted
"Y.TargetID = TargetID"

I am not sure what to do next?

Thanks
Chris
 
J

John Spencer

Typo. I missed referencing the table in the join statement. Try this
version

SELECT Y.*
FROM tblProjectActualIndicator as Y
INNER JOIN
(SELECT RefID, TargetID, Max(QtrDate) as MaxQ
FROM tblProjectActualIndicator) as T
ON Y.RefID = T.RefID
AND Y.TargetID = T.TargetID
AND Y.QtrDate = T.MaxQ

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,
Thanks for your reply.
My table name follows your rules (it's called
tblProjectActualIndicator) but the query is returning an error saying
"join expression not supported" with the following being highlighted
"Y.TargetID = TargetID"

I am not sure what to do next?

Thanks
Chris
 
A

annysjunkmail

Typo.  I missed referencing the table in the join statement.  Try this
version

SELECT Y.*
FROM tblProjectActualIndicator as Y
INNER JOIN
   (SELECT RefID, TargetID, Max(QtrDate) as MaxQ
    FROM tblProjectActualIndicator) as T
ON Y.RefID = T.RefID
AND Y.TargetID = T.TargetID
AND Y.QtrDate = T.MaxQ

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi John,
Thanks for your reply.
My table name follows your rules (it's called
tblProjectActualIndicator) but the query is returning an error saying
"join expression not supported" with the following being highlighted
"Y.TargetID = TargetID"

I am not sure what to do next?

Thanks
Chris

Hi John,
I have updated the sql as corrected.
However the query won't run properly...it says "you tried to execute a
query that does not include the specified expression 'RefID' as part
of an aggregate function.
Again, I am stuck and don't know what to do to fix it.. Also when I go
into the design window to look at the query Access says it can't
represent the join expressions and removes them all so the alias table
T does not have any fields for selection

Regards
Chris
 
J

John Spencer

Jumpin' Jehosaphat!!! I am havng a bad day!

Hopefully this one is right and will give you the desired results.

SELECT Y.*
FROM tblProjectActualIndicator as Y
INNER JOIN
(SELECT RefID, TargetID, Max(QtrDate) as MaxQ
FROM tblProjectActualIndicator
GROUP BY RefID, TargetID) as T
ON Y.RefID = T.RefID
AND Y.TargetID = T.TargetID
AND Y.QtrDate = T.MaxQ

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
A

annysjunkmail

Jumpin' Jehosaphat!!! I am havng a bad day!

Hopefully this one is right and will give you the desired results.

SELECT Y.*
FROM tblProjectActualIndicator as Y
INNER JOIN
   (SELECT RefID, TargetID, Max(QtrDate) as MaxQ
    FROM tblProjectActualIndicator
    GROUP BY  RefID, TargetID) as T
ON Y.RefID = T.RefID
AND Y.TargetID = T.TargetID
AND Y.QtrDate = T.MaxQ

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.











- Show quoted text -

LOl!!

Perfect. Working brilliantly now.
Thanks for your time and expertise

Chris
Have a good day ;-)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top