Hi guys! I am trying to select some data from a table in an Access 2010 database. The thing was: I wanted to display the latest "Configuration" release of each System (adapter). Well, using the SQL code below it seems to work perfectly, but I am having trouble filtering out the "QaEnv", too.
This is what is displayed before the query:
ID | QaEnv | System | Type | Configuration | ActiveDate
1 | Delta (UAT2) EAI-V3 | MOMS Adapter | Adapter | 1.0 | 30-06-2011
2 | Delta (UAT2) EAI-V3 | GSG Adapter | Adapter | 1.1 | 30-06-2011
3 | Yankee (UAT1) ACC-V01 | MOMS Adapter | Adapter | 2.4 | 30-06-2011
4 | Delta (UAT2) EAI-V3 | GSG Adapter | Adapter | 1.0 | 20-06-2011
5 | Delta (UAT2) EAI-V3 | KIWI2 Adapter | Adapter | 1.7 | 30-06-2011
6 | Delta (UAT2) EAI-V3 | KIWI2 Adapter | Adapter | 1.5 | 20-06-2011
This is what is displayed after the query:
ID | QaEnv | System | Type | Configuration | ActiveDate
1 | Delta (UAT2) EAI-V3 | MOMS Adapter | Adapter | 1.0 | 30-06-2011
2 | Delta (UAT2) EAI-V3 | GSG Adapter | Adapter | 1.1 | 30-06-2011
3 | Yankee (UAT1) ACC-V01 | MOMS Adapter | Adapter | 2.4 | 30-06-2011
5 | Delta (UAT2) EAI-V3 | KIWI2 Adapter | Adapter | 1.7 | 30-06-2011
But I want to display this:
ID | QaEnv | System | Type | Configuration | ActiveDate
1 | Delta (UAT2) EAI-V3 | MOMS Adapter | Adapter | 1.0 | 30-06-2011
2 | Delta (UAT2) EAI-V3 | GSG Adapter | Adapter | 1.1 | 30-06-2011
4 | Delta (UAT2) EAI-V3 | KIWI2 Adapter | Adapter | 1.7 | 30-06-2011
Code:
SELECT t.*
FROM Deployments t
INNER JOIN
(
SELECT System, Max(ActiveDate) as ActDate
FROM Deployments
WHERE QaEnv = "Delta (UAT2) EAI-V3" AND Type = "Adapter"
AND (([ActiveDate] Between #6/20/2011# And #6/30/2011#))
GROUP BY System
)
x ON t.system = x.system AND t.ActiveDate = x.ActDate;
Can any of you help me out?
Thanks in advance,
Dennis
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
