help with Access SQL query :o)

[Dan]

Can someone please assist me in fine tuning this query?

My ulitmate goal is to get the first 50 results back from the [15]
columns where the field is 0 (zero).

Here is the SQL statement as it now...

SELECT V1.[item_desc],
V1.[size],V1.[uom_desc],V1.[p_­­upc_alpha],V1.[ag_item_cd],v1­.­[status_cd],v1.[avg_mvmt],C1­.[­15],


'CEREAL' AS Sub_Category
FROM Cereal AS C1
LEFT JOIN v_item_schematics AS V1 ON C1.AG = V1.ag_item_cd

WHERE 50 >= (SELECT COUNT(AG) FROM Cereal
LEFT JOIN v_item_schematics ON Cereal.AG = v_item_schematics.ag_item_cd

WHERE v_item_schematics.avg.mvmt > v1.avg_mvmt)

UNION ALL

SELECT V1.[item_desc],
V1.[size],V1.[uom_desc],V1.[p_­­upc_alpha],V1.[ag_item_cd],v1­.­[status_cd],v1.[avg_mvmt],S1­.[­15],

'SOUP' AS Sub_Category
FROM Soup AS S1
LEFT JOIN v_item_schematics AS V1 ON S1.AG = V1.ag_item_cd

WHERE 50 >= (SELECT COUNT(AG) FROM Soup
LEFT JOIN v_item_schematics ON Soup.AG = v_item_schematics.ag_item_cd
WHERE v_item_schematics.avg.mvmt > v1.avg_mvmt)

order BY Sub_category,v1.[avg_mvmt] DESC;

Right now the results are 925 records, 243 being from the first table
and 682 from the second, instead of just a total of 100.

Thanks to everyone and I appreciate all the learning this group is
teaching me!

 

[John Vinson]

Can someone please assist me in fine tuning this query?

Dan, posting the same request five times in the span of an hour is
neither necessary nor polite. We're all volunteers here, donating our
time; it's a complex question, which I've marked to look at later when
I get some billable hours in. Please have patience. You're being
heard.

John W. Vinson[MVP]

 

[Dan]

This I know and understand. I would never post that many times
intentionally. There seems to have been something going on this
morning with the groups. Multiple people in my workplace noticed their
posts not uploading either.

Sorry if this seemed like spamming, but I only posted once on my end.

Regards,
Dan

 

[Dan]

(In reference to another posting by co-worker) Again, please see prior
posting...there were issues with the groups yesterday posting. This
was not done by me, but a co-worker, to see if his posts would go
through either.

Before you claim rudeness, perhaps you could have researched for a
minute at past posts made by myself and others to see what history
there truly was with their ID. Does this person spam? Are they rude?
Before jumping to conclusions. Hmm? Maybe their was some server issue

on the other end, that is why the multiple postings came through?!?


I have always aknowledged my graciousness for the time others put into
helping those of us with questions. I again thank everyone who assists

and hope we can move forward from here.


Regards, Dan