VbzCart/queries/qryCat Titles Item count

About

 * Purpose: Subquery to count items for each title record
 * Used by: a one-time maintenance query which might eventually become part of the catalog-building process. The objective was to be able to distinguish between (a) items which need a CatSfx added to the Title's CatKey because there are more than one of them per title. and (b) items whose catalog numbers can be generated without a CatSfx. If an item falls into the former category but its CatSfx is NULL, this represents a maintenance issue -- probably leftover data from before that field was created, but might also indicate a bug in the catalog building.
 * Note: This is very similar to, but does not bring in stock information -- therefore faster and possibly including more records.
 * History:
 * 2010-11-11 Created.

SQL
CREATE OR REPLACE VIEW qryCat_Titles_Item_count AS SELECT ID_Title, ID_Dept, DateAdded AS WhenAdded, COUNT(i.ID) AS cntItems, SUM(IF(i.isForSale,1,0)) AS cntForSale, SUM(IF(i.isInPrint,1,0)) AS cntInPrint, MIN(i.PriceSell) AS currMinSell, MAX(i.PriceSell) AS currMaxSell, t.Name FROM cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID GROUP BY i.ID_Title;

maintenance query
This is the temporary query for which this query was created: SELECT i.ID, i.ID_Title, i.CatNum, i.CatSfx, t.cntItems FROM cat_items AS i LEFT JOIN qryCat_Titles_Item_count AS t  ON t.ID_Title=i.ID_Title WHERE (i.CatSfx IS NULL) AND (NOT i.isPulled) AND (t.cntItems>1) AND (CatNum IS NOT NULL) ORDER BY CatNum;

That version won't let you edit the records; this one will, although it includes a lot of records which don't need to be edited -- use the above query to find them. SELECT ID, ID_Title, CatNum, CatSfx FROM cat_items WHERE (CatSfx IS NULL) AND (NOT isPulled) AND (CatNum IS NOT NULL) ORDER BY CatNum;