Need help with tricky SELECT

Please try this -

SELECT InspectionType.inspection_type_name, 
              --[last_sticker_no] - [next_sticker_no] + 1 AS remaining_stickers, 
              --InspectionStickers.status
			   SUM ( CASE WHEN InspectionStickers.status='C' THEN 1 ELSE 0 END )  UnusedStickersC
			  ,SUM ( CASE WHEN InspectionStickers.status='N' THEN 1 ELSE 0 END )  UnusedStickersN
FROM  (InspectionStickers 
INNER JOIN CampaignYear ON InspectionStickers.fk_campaign_year = CampaignYear.pk_campaign_year) 
INNER JOIN InspectionType ON CampaignYear.fk_inspection_type = InspectionType.pk_inspection_type
WHERE 
    AND  InspectionType.pk_inspection_type = 1
    AND  CampaignYear.pk_campaign_year = 4
GROUP BY InspectionType.inspection_type_name

Select allOpen in new window

If you face any issues then pls provide some sample data and expected output.

Thanks for the quick response, but I don't think I made my request clear.  Attached are the three tables with sample data:
I'm looking for a result set that would look like this:

Annual Inside                 247
Semi-Annual Inside       10
Annual Outside              4
Emissions                        150

For this query, rows in status 'U' are ignored.  All but the Annual Inside type involve only a single InspectionStickers status 'C' row, but there are two Annual Inside rows - one in status 'C' (pk 5) and the other status 'N' (pk 7).  I need to be able to sum the total of all unused stickers (to_sticker_no - next_sticker_no + 1) in both rows.  And I want the result set to look like the four rows shown above.  There will only be one row in status 'C' for any inspection type and campaign year, but there can be more than one row is status 'N' for a given inspection type and campaign year.

Pete

and here is the query:

 Images of data are painful, just like images of solutions would be.

sorry (silly mood); but is there any chance you can just copy/paste some data into code boxes? or, for access, you can upload a db file of just the stuff you care to share.

Sorry - attached is a stripped-down Access 2000 database containing the three tables and the beginnings of a query.

PeteSample.mdb

Hey,

Can you explain me the how you are getting

247 for Annual Inside  ??

InspectionStickers primary key 5 shows next_sticker_no of 1253804 and to_sticker_no of 1253950, so 1253950 - 1253804  + 1 = 147.
InspectionStickers primary key 7 shows next_sticker_no of 2590481 and to_sticker_no of 2590580, so 2590580 - 2590481  + 1 = 100.
The total of the two = 247.

If 2 stickers of 10 have been used, the next sticker would be 3 - and to calculate the number remaining (which should be 8), we subtract 3 from 10 and add 1.

Pete

Thank you. Will provide you a query in some time. :)

Just to clarify, InspectionStickers primary keys 5 and 7 have fk_inspection_type and fk_campaign_year in common (1 and 4), and neither is in status 'U', so the remaining counts need to be combined.

Pete

Okies. Got it.

Please try this solution -

SELECT Inspection_type_name , SUM( to_sticker_no - next_sticker_no + 1 ) as SummationStickers FROM 
inspectionStickers as iss
INNER JOIN CampaignYear as cy ON iss.fk_CampaignYear = cy.pk_CampaignYear AND iss.fk_Inspection_Type = cy.fk_Inspection_Type
INNER JOIN InspectionType it ON it.pk_Inspection_Type = iss.fk_Inspection_Type
WHERE [Status] <> 'U'
GROUP BY Inspection_type_name

Select allOpen in new window

OUTPUT

/*------------------------
OUTPUT
------------------------*/
Inspection_type_name                               SummationStickers
-------------------------------------------------- --------------------
Annual Inside                                      247
Annual Outside                                     4
Emissions                                          150
Semi-Annual Inside                                 10

(4 row(s) affected)

Select allOpen in new window

Sweet approach - I never thought of it.  But...

I copied your solution into the sample database I attached earlier and ran it - got the error 'Syntax error (missing operator) in query expression ''.  I'm running Office 365 installed locally, version 1709 (build 8528.2147).

I don't think it likes the AND in the first join

Updated..Pls try(as was missing)

SELECT Inspection_type_name , SUM ( to_sticker_no - next_sticker_no + 1 ) as SummationStickers FROM
inspectionStickers as iss
INNER JOIN CampaignYear as cy ON ( iss.fk_CampaignYear = cy.pk_CampaignYear ) AND ( iss.fk_Inspection_Type = cy.fk_Inspection_Type )
INNER JOIN InspectionType as it ON ( it.pk_Inspection_Type = iss.fk_Inspection_Type )
WHERE Status <> 'U'
GROUP BY Inspection_type_name

You had a couple of field names incorrectly spelled, so I fixed them.  But, I'm still getting 'Syntax error (missing operator) in query expression '( iss.fk_Campaign...etc.  

I tried building the query using Access' Query Design, but Access wants me to group on status, and that doesn't allow 'C' and 'N' rows to be combined.  Here's Access' code:

SELECT InspectionType.inspection_type_name, 
       Sum([to_sticker_no]-[next_sticker_no]+1) AS SummationStickers
FROM (InspectionStickers 
INNER JOIN CampaignYear ON InspectionStickers.fk_campaign_year = CampaignYear.pk_campaign_year) 
INNER JOIN InspectionType ON CampaignYear.fk_inspection_type = InspectionType.pk_inspection_type
GROUP BY InspectionType.inspection_type_name, InspectionStickers.status
HAVING (((InspectionStickers.status)<>'U'));

Select allOpen in new window

It runs, but I'm not getting the result I want.  'C' and 'N' rows are returned separately.

Do not build the query...

You can directly go to the query window where you can run the sql query.

This will help you to go to SQL query window - > http://www.jaffainc.com/SQLStatementsInAccess.htm

Edited my last comment.

That did it!  Many thanks for your help in getting this working for me - I really appreciate it.