Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access query - Get the last of each item by date

Posted on 2014-01-25
13
Medium Priority
?
481 Views
Last Modified: 2014-01-26
Hello all

In a query, I need to pull all of the last records of every itemID that is ActiveYN = Yes.

So if my table [t_Dtail] is

DetailID,  tdate, ItemID, ActiveYN

how do I do that?

If I do it as

SELECT * From t_dtail WHERE ActiveYN = True
I know I get everything that's true.

So if my table has 50 ItemID # 20 How do I get just the Last True of all the ItemID (not just number 20's??????

Tried
SELECT * From t_dtail WHERE ActiveYN = True HAVING ItemID= DMax(ItemID, t_Dtail,ItemID)

But that doesn't get it either
????????
0
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39809330
try this

select a.*
from t_dtail as a
inner join
(select max(b.tdate) as maxtdate, b.detailId
 from t_dtail as b
 group by b.detailId
having b.ActiveYN= true
) as c
on a.detailId = c.detailId  and a.tdate=c.maxtdate
0
 

Author Comment

by:wlwebb
ID: 39809497
I keep getting
Your query does not include the specified expression 'itemid' as part of an aggregate function
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39809546
try this


select a.*
from t_dtail as a
inner join
(select max(b.tdate) as maxtdate, b.itemid
 from t_dtail as b
 group by b.itemid
having b.ActiveYN= true
) as c
on a.itemid = c.itemid  and a.tdate=c.maxtdate
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:wlwebb
ID: 39809551
....your query expression does not ...... 'b.ActIveYN=-1' as part of an aggregate function
0
 

Author Comment

by:wlwebb
ID: 39809553
Tried True before -1
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39809559
sorry, try this


select a.*
from t_dtail as a
inner join
(select max(b.tdate) as maxtdate, b.itemid
 from t_dtail as b
 group by b.itemid,b.ActiveYN
having b.ActiveYN= true
) as c
on a.itemid = c.itemid  and a.tdate=c.maxtdate
0
 
LVL 10

Accepted Solution

by:
Gozreh earned 2000 total points
ID: 39809648
Why not simple as

SELECT t_Dtail.ItemID, Max(t_Dtail.tdate) AS MaxDate
FROM t_Dtail
WHERE (((t_Dtail.ActiveYN)=True))
GROUP BY t_Dtail.ItemID;
0
 

Author Closing Comment

by:wlwebb
ID: 39809891
Thanks for the input!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39810062
@ wlwebb

did you even try my post at http:#a39809559 ?


.
0
 

Author Comment

by:wlwebb
ID: 39810071
Cap 1....

I ALWAYS ALWAYS try anything you post first!  History has taught me that your posts always work 99.999999% of the time

Either I was typing something wrong (couldn't copy and paste last night -no connection for laptop- where I was.... I was reading on my phone and trying to type it into my laptop.  I will give your's a whirl again today.


PS.... The 0.0000001% of the time it doesn't work is because my table isn't set up right to allow what I want to do to work............
0
 

Author Comment

by:wlwebb
ID: 39810252
Cap 1 ....  I tried the last suggestion.......

select a.*
from t_dtail as a
inner join
(select max(b.tdate) as maxtdate, b.itemid
 from t_dtail as b
 group by b.itemid,b.ActiveYN
having b.ActiveYN= true
) as c
on a.itemid = c.itemid  and a.tdate=c.maxtdate


It produces the error

You tried to execute a query that does not include the specified expresion 'b.Active YN=-1' as part of an aggregate function.

The code above is a cut/paste from my sql view..........
????????????

*******************
NEVER MIND... I SEE I USED THE WRONG ONE NOW>>>>
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39810348
so did you get it working?
0
 

Author Comment

by:wlwebb
ID: 39810397
Cap1
Yes. .... Once I used the right one....So again... That bad was on me!

Like I said above ... I've seen enough here to know you are one of the best on here ....  You, MBiz and Irog and LSM always shoot straight with great pointers.  Others on here are great too ... Not discrediting them in any way ... Just know when one of the above post usually it always works (unless I screw up the typing or misinterpret something)

THANK YOU!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question