Count question

[sparkbyte]

I need a way to validate entries.

There are 3 columns (track1, track2, track3) respectively in an activity table. Only one of these can have a true value for the record to be valid.

What I was wanting to do is write a select script that will identify records that do not meet this rule that are already in the DB, clean the DB and use the Select script to create a rule for the table.

This is out of Access but I have been migrating the DB to a SQL 2005 server.

SELECT tbl_Activity.ID, tbl_Activity.LoginID, tbl_Activity.ContactName, tbl_Activity.ContactPhone, tbl_Activity.ContactFax, tbl_Activity.OfficeCode, tbl_Activity.Type_of_Contact, tbl_Activity.DigitizedFile, tbl_Activity.RPC_Code, tbl_Activity.ProgramName, tbl_Activity.AfileNumber, tbl_Activity.Reason_for_Call, tbl_Activity.ReceivedFile, tbl_Activity.Status_of_Activity, tbl_Activity.StartTimeDate, tbl_Activity.LoginIDClosed, tbl_Activity.ClosedTimeDate, tbl_Activity.Haitian_TPS_Case, tbl_Activity.Complex, tbl_Activity.Noncomplex, tbl_Activity.Track1, tbl_Activity.Track2, tbl_Activity.Track3, tbl_Activity.FileReviewButton, tbl_Activity.AlienStatusButton, tbl_Activity.ApplStatusButton, tbl_Activity.RemovalDocsButton, tbl_Activity.PhotoOnlyButton, tbl_Activity.PrintOnlyButton, tbl_Activity.PhotosButton, tbl_Activity.PrintsButton, tbl_Activity.PhotosAndPrintsButton, tbl_Activity.NatzCertButton, tbl_Activity.AffSupportButton, tbl_Activity.BondButton, tbl_Activity.FAXButton, tbl_Activity.ccMailButton, tbl_Activity.OtherButton, tbl_Activity.phonebutton, tbl_Activity.Photos3Button, tbl_Activity.Prints3Button, tbl_Activity.Updated_CIS, tbl_Activity.AFileCreate, tbl_Activity.BioDataButton, tbl_Activity.ResearchButton, tbl_Activity.FRCFileButton, tbl_Activity.FTFButton, tbl_Activity.ResponseMemo, tbl_Activity.LastDate, tbl_Activity.Project, tbl_Activity.Memo, tbl_Activity.Login1, tbl_Activity.Start1, tbl_Activity.End1, tbl_Activity.Login2, tbl_Activity.Start2, tbl_Activity.End2, tbl_Activity.Login3, tbl_Activity.Start3, tbl_Activity.End3, tbl_Activity.StaffingSheetToggle, tbl_Activity.SEIPrint, tbl_Activity.StartTimeOnly, tbl_Activity.StartDateOnly, tbl_Activity.ClosedTimeOnly, tbl_Activity.DateOnly, *
FROM tbl_Activity;

Thanks

John Fuhrman

 

[gmmastros]

This query should help you identify the rows where track 1,2, or 3 have multiple values.

Select tbl_Activity.ID
From   tbl_Activity
Where  Case When tbl_Activity.Track1 > '' Then 1 Else 0 End
       + Case When tbl_Activity.Track2 > '' Then 1 Else 0 End
       + Case When tbl_Activity.Track3 > '' Then 1 Else 0 End > 1

This code assumes an empty string, NULL or spaces are stored in the tracks to represent a "no value" condition. If there are other characters, like tabs, or carriage returns, this code will not find the problematic rows.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom