Inner Sub Query/VBA problem! PLEASE HELP!!!

[alwayshouston]

Hi All
I have a table as below:

Dept_ID Salary Original Modified
ACCT 5000 Y N
ACCT 5000 N Y
MKTG 7500 Y N
MKTG 8000 N Y

I need to get the result from the table in which if there is a duplication (ACCT), I would need Original =Y and Modified = N. If there is no duplication (MKTG), I would need the record where Modified = Y.
My output should be as follow:

Dept_ID Salary Original Modified
ACCT 5000 Y N
MKTG 8000 N Y

How can I accomplish this logic in a query/queries or VBA? I am going nuts in solving this simple problem. PLEASE HELP!!!! Please note that I cannot alter the table design because it comes from the system this way.

Thanks in Advance!!!

 

[LonnieJohnson]

Is there ever a case where the original flag is N and there are no dups?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[LonnieJohnson]

I may have a sample for you over the weekend unless that's not soon enough for ya.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[alwayshouston]

Is there ever a case where the original flag is N and there are no dups?
My answer is Yes. When the original flag is N there is only ONE modified record.

Thanks!

 

[Golom]

Suprisingly tricky
[tt]
SELECT D.DeptID, D.Salary,
First(D.Original) AS [Original], First(D.Modified) AS [Modified]
FROM DeptSalary AS D
WHERE [D].[Original]=TRUE And [D].[Modified]=FALSE AND
D.Salary IN (Select Salary From DeptSalary X Where D.DeptID = X.DeptID Group By Salary Having Count(*) > 1)
GROUP BY D.DeptID, D.Salary

UNION ALL

SELECT D.DeptID, D.Salary, First(D.Original) , First(D.Modified)
FROM DeptSalary AS D
WHERE Modified = TRUE AND
D.Salary NOT IN (Select Salary From DeptSalary X Where D.DeptID = X.DeptID Group By Salary Having Count(*) > 1)
GROUP BY D.DeptID, D.Salary

Order By 1, 2
[/tt]
I've assumed that "Original" and "Modified" are Yes/No (i.e. Boolean) fields. If they are really text with "Y" and "N" in them then change "=TRUE" to "='Y'" and "=FALSE" to "='N'" respectively.

 

[alwayshouston]

Thanks for your quick feedback. Your solution works fine. Again THANK YOU )

 

[Golom]

Here's another solution
[tt]
SELECT D.DeptID, D.Salary,
First(D.Original) AS [Original], First(D.Modified) AS [Modified]

FROM DeptSalary AS D

GROUP BY D.DeptID, D.Salary

HAVING IIF ( (Select Count(*) From DeptSalary X
Where D.DeptID = X.DeptID
AND X.Salary = D.Salary
Group By X.Salary) = 1,

First(Original) = FALSE And First(Modified) = TRUE,

First(Original) = TRUE And First(Modified) = FALSE )
[/tt]