Need help with tricky SELECT

I need to return the count of remaining items among rows of the same type.  Here's a starting SELECT statement:

SELECT InspectionType.inspection_type_name, 
              [last_sticker_no] - [next_sticker_no] + 1 AS remaining_stickers, 
              InspectionStickers.status
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 InspectionStickers.status='C'
    AND  InspectionType.pk_inspection_type = 1
    AND  CampaignYear.pk_campaign_year = 4

Open in new window


The statement returns the number of unused sticker numbers in each row having a status of 'C' for inspection type 1 by campaign year (4 in this example).

I need to add to that count the number of unused sticker numbers in other rows having a status of 'N' with the same inspection type and campaign year.  I'm sure I need a sub SELECT, but I don't know how to structure it.  Any help would be greatly appreciated!  Pete
petekipeOwnerAsked:
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.

Pawan KumarDatabase ExpertCommented:
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

Open in new window

Pawan KumarDatabase ExpertCommented:
If you face any issues then pls provide some sample data and expected output.
petekipeOwnerAuthor Commented:
Thanks for the quick response, but I don't think I made my request clear.  Attached are the three tables with sample data:InspectionType, CampaignYear and InspectionStickers tables - MS Access database
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulEE Topic AdvisorCommented:
and here is the query:
transcribe this if you really want to
 Images of data are painful, just like images of solutions would be.
PortletPaulEE Topic AdvisorCommented:
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.
petekipeOwnerAuthor Commented:
Sorry - attached is a stripped-down Access 2000 database containing the three tables and the beginnings of a query.

PeteSample.mdb
Pawan KumarDatabase ExpertCommented:
Hey,

Can you explain me the how you are getting

247 for Annual Inside  ??
petekipeOwnerAuthor Commented:
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
Pawan KumarDatabase ExpertCommented:
Thank you. Will provide you a query in some time. :)
petekipeOwnerAuthor Commented:
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
Pawan KumarDatabase ExpertCommented:
Okies. Got it.
Pawan KumarDatabase ExpertCommented:
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

Open 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)

Open in new window

petekipeOwnerAuthor Commented:
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).
petekipeOwnerAuthor Commented:
I don't think it likes the AND in the first join
Pawan KumarDatabase ExpertCommented:
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
petekipeOwnerAuthor Commented:
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'));

Open in new window


It runs, but I'm not getting the result I want.  'C' and 'N' rows are returned separately.
Pawan KumarDatabase ExpertCommented:
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
Pawan KumarDatabase ExpertCommented:
Do not group on InspectionStickers.status

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
WHERE (((InspectionStickers.status)<>'U'))
GROUP BY InspectionType.inspection_type_name

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
Pawan KumarDatabase ExpertCommented:
Edited my last comment.
petekipeOwnerAuthor Commented:
That did it!  Many thanks for your help in getting this working for me - I really appreciate it.
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.