Need some help making a bad query good (AC2007)

[jdraw]

Glad you have it sorted. I was responding to your test data. I could see potential issues if the test data was not representative, but glad you have adjusted accordingly.

Access is case insensitive so x and X are equals --- just a little sloppy on my part.

 

[AOB]

Thanks jdraw! I figured as much but just wanted to clarify.

While my solution is sufficient - it produces the desired dataset within an acceptable timeframe - the one bit that bugs me slightly are the similarities of the sub-queries either side of the UNION ("A" and "C", "B" and "D")

Seems a tad inefficient to effectively run the same subqueries twice. But figured I had to treat each side of the UNION separately. Just curious to know if there's a snappier way of writing it?

(Needless to say, not important - what I have will suffice - will mark the thread solved regardless!)

Thanks again!

 

[jdraw]

I'm not aware of anything "snappier".
I know when you use the same table multiple times you need to supply an alias.

Pure SQL is probably faster than a user defined function, but you could create a function. Might be easier to follow than the SQL. I haven't done a lot of SQL work for 8 years or so.
Others, more into SQL on a daily basis, might have a more elegant expression.

 

[AOB]

No that's fine, thanks jdraw - it may be 8 years, but your familiarity with SQL is still considerably stronger than mine!

I noticed you've removed this comment from your last post?

When I ran your latest sql against the test data you suggested in post 17, I get 2 records for Elliot???

Should I be concerned?...

 

[jdraw]

I revised my test data following your post. When I ran your code I had 2 Elliot records.
I did some other tests and found I had some extra text in one of the records. As soon as I removed the extra text ( I think it was caused by how I pasted the data into the record), the "apparent duplication" was removed and there was only 1 record for Elliot in the result.
Once I saw that my post was incorrect, I removed the comment.
Good luck with your project.

 

[AOB]

Phew!! Thanks for clarifying! I was off clicking my heels and treating myself to an extra shot in my morning coffee to celebrate before I noticed your post and started to feel blood draining!

Thank you very much for all your efforts and suggestions, I appreciate it all!