Nested Select - Jet SQL

Hi Experts,
Trying to figure out this but unable. I will summarize as real data much more complex.

Table = Details mid
fields = over 60 among which the following: Vessel, Voyage, BL

the table key is an autonumber. for the same Vessel, Voyage and BL I could have several records.

WHAT I NEED
is 1 SQL statement  that can pull ALL the fields in the table that meet the following criteria:
Vessel + str(Voyage) = 'ABC 123' and count(BL)>1

How to achieve this ??
I was able to get the following:

SELECT BL
FROM `details mid`
WHERE vessel & str(voyage) = 'ABC 123'
GROUP BY BL
HAVING count(BL)>1
ORDER by BL

This SQL give me all the BL that have more that 1 instance of them which is excellent, now based on the results of this SQL statement I need ALL the fields that are associated with these short listed BL's how do I do this ??

If I try SELECT *, (SELECT BL ....) as A
I get you will get only 1 record !!!

Please help.
Regards
gowflow
LVL 31
gowflowAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Deepak ChauhanSQL Server DBACommented:
you can do like this

select * from [details mid] where BL in (
SELECT BL
 FROM `details mid`
WHERE vessel & str(voyage) = 'ABC 123'
GROUP BY BL
 HAVING count(BL)>1)

order by BL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowAuthor Commented:
YOU ARE SIMPLY a GENIUS deepakChauhan !!!! tks very much you saved me greatly.

Fantastic thank you
gowflow
Deepak ChauhanSQL Server DBACommented:
a small amendment  here "WHERE vessel & str(voyage) = 'ABC 123' "

                                             >>   where vessel + str(voyage) = 'ABC 123'

If space between Vessel & voyage

>>  where vessel +' '+ str(voyage) = 'ABC 123'
gowflowAuthor Commented:
for the where its fine it works well as it is. if was the double SELECT I had a problem with you opened large possibilities for me as I use SQL a lot in my applications that I develop but never had to go complex which is very good now as it will open a lot of new combinations for me. Tks again
gowflow
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.