SQL or Crystal Report

I have a report that I am trying to develop that returns a log of inspections.  The user wants the report to return records where a second inspection has not been done.  I have the following fields:

Facility
Inspection Date
Cycle-indicates what number inspection has been done.  All the inspections that were done the first time have a "1".  All the records that have been done a second time have a "2".    

So somehow I need to write the query or build the report to return only those records where there is only one record initially.  For the future though the report or query should return those records were there are only 1s and 2s but not a 3.  That could be built into a parameter somehow where the user puts the last finished cycle number.

Any help would be greatly appreciated.
mounty95Asked:
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.

vastoCommented:
You can use Max value in this way:

SELECT InspectionID, MAX(Cycle) as MaxCycle
FROM TableName
GROUP BY InspectionID
Having MAX(Cycle)=1

This will return the list of InspectionIDs with one inspection. You can further join it with Inspections to get the Inspection date and other details.

If you create the SQL above as a view then you will be able to use database expert in Crystal to join it with Inspection table
mlmccCommented:
One way to do this in Crystal is to group on the facility and suppress any group that has more than 1 record through the GROUP SELECTION formula

   Count({InspectionDate},{FacilityField}) > 1

Are you doing any filtering on the dates?

You could also do this through SQL

Something like

SELECT Facility, InspectionDate, Cycle FROM YourDatabase
WHERE Not Facility IN (SELECT ydb.Facility FROM YourDatabase ydb WHERE ydb.cycle >= 2)

Open in new window


mlmcc

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
mounty95Author Commented:
Thank you both for pointing me in two very correct methods for solving the problem!!!!  The idea of doing a subquery inside the SQL statement gives me the ability to create a parameter that the user can then put in the last completed cycle.  The MAX gives me the simpler approach for just simple query writing.

Thank you both!!!!!!
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
Crystal Reports

From novice to tech pro — start learning today.