• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

I can't seem to figure out how to gather a found set of the records I need displayed...???

I have a table called Auctions that has both an ItemNumber and a ScanID in it.  ScanID's are unique to ItemNumber's, so if I have 10 auction records with the ScanID 12345 they should all have the same ItemNumber.

Somehow I've gotten lots of auction records that are getting incorrect ScanID's.  I need to figure out how, but right now I simply need a way to find all of the problem records.  

I can put a ! in the ScanID in Auctions to get a found set of records with duplicate ScanID's, however, I don't know what to do from there in order to filter so that I only see records with duplicate ScanID's but different ItemNumber's.  

Any information would be greatly appreciated.  Thanks!
Andrew Angell
Andrew Angell
  • 2
1 Solution
maybe you could create an extra temp field showing if scanID=Itemnumber. this way you would see the guilty ones once you've got the right found set, and restrict the found set to these.
Andrew AngellCo-Owner / DeveloperAuthor Commented:
I wound up creating a calc. field in Auctions that allows me to more easily find the problems.  The calc. I created is:

If (
ScanID ` "" ;
If ( Item_Number = AuctionsToScansByScanID::ItemNumber ; 1 ; 0 ) ;

I just store it as text.  Not all items have ScanID's because they didn't get introduced until just a few months ago.

So then I search on this to show me only items with invalid scan id's.  That helps a lot.  The problem now is yet another stupid thing with our system.  This ScanID field, before we started using it for our own ScanID, was being used to store an item ID from another company we were working with.  Many auction records with ScanID's are actually this other company's ID, not ours.  They're easy to spot because theirs have a few more digits than ours.

Is there a way I can do a find displays only records where this field is less than 8 characters, for instance?
Andrew AngellCo-Owner / DeveloperAuthor Commented:
Also, on this same situation, how can I add validation to the ScanID field?  Now that I have the calc. that shows whether it's valid or not it seems like it should be easy to add validation to popup a message alerting with the ScanID is not valid and won't accept it.  I'm confused over the validation calc. dialog for some reason.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now