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:
FROM `details mid`
WHERE vessel & str(voyage) = 'ABC 123'
GROUP BY BL
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 !!!