January 17, 2014 at 11:38 am
A query question...the data below has duplicate RES numbers where one of the records for a dup RES# is flagged in the CALLED field as "YES" where other instances are flagged "NO". What I need to do is query this data so that any RES which has it's called value set to YES results in no instance of that RES being returned in the final data set. Any thoughts on this would be greatly appreciated
RES SEQUENCECalled
R00102046033075No
R00102086823534No
R00102086922731No
R00102086922730Yes
R00102087023174No
R00102087023175Yes
R00102087322962No
R00102087322961Yes
R00102101822309No
R00102101822310Yes
R00102102022551No
R00102102022552No
R00102102322218No
R00102102322220No
R00102102322219No
R00102102422867No
R00102102422866No
R00102102422865Yes
R00102102523471No
R00102102722938No
R00102102722940No
R00102102722939No
R00102102922829No
R00102102922828Yes
January 17, 2014 at 11:57 am
No idea what your table name is called or what you are really trying to do here but maybe something like this? (Assuming your table name is #Something)
Select *
from #Something
where RES in
(
select RES
from #Something
group by RES
having MAX(Called) = 'No'
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 17, 2014 at 12:09 pm
If I want to use another query in MSAccess as the target of the code you sent instead of pointing it at a table in the database... (query name "remaining".. for example... can I do that?
January 17, 2014 at 12:10 pm
Another method if you are spooked by using a subquery is to create a query which returns all records which have a Yes. Then join that query to the table where your records are and create a new query which gives you the set of records where there is no record returned in the first query by using an outer join. Many Access users find that to be an easier concept as they often work in the designer rather than actually writing SQL statements.
And in answer to your latest question, yes you can do that. In fact that is how the duplicates query in older versions of Access is created. That's the subquery I mentioned above.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
January 17, 2014 at 12:30 pm
Sean Lange (1/17/2014)
No idea what your table name is called or what you are really trying to do here but maybe something like this? (Assuming your table name is #Something)
Select *
from #Something
where RES in
(
select RES
from #Something
group by RES
having MAX(Called) = 'No'
)
Or, in T-SQL, you could do this:
Select s1.* -- really should list the columns returned individually
from #Something s1
where not exists(select 1 from #Something s2 where s1.RES = s2.res and s2.Called = 'Yes')
January 17, 2014 at 12:36 pm
Your ingenious method returns the dataset I need.. but doesn't allow me to update any data in the resulting dataset. What info can I provide to help with sorting that issue out?
January 17, 2014 at 1:01 pm
SQL33 (1/17/2014)
Your ingenious method returns the dataset I need.. but doesn't allow me to update any data in the resulting dataset. What info can I provide to help with sorting that issue out?
Well, hard to say since you didn't say anything regarding this in your original post.
Would help if you provided the entire scenario upfront rather than providing things piece meal. You should read the first article I reference below in my signature block. Follow the instructions in that article regarding what you should post and how you should post it to get the best possible answer(s) to your question(s).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply