Crosstab Query: Only Want Results with 1 Value

[clayt101]

I have a crosstab query where all the values in the columns are text. In order to verify that there were no duplicates, I ran the crosstab query using a count of the entries. All of the individual values with have a count of 1; however, there were a bunch that had a count of 2 or more...meaning that they were duplicates (may or may not have the same value in text form).

So here is my question, can I make a crosstab query that only keeps the individual values, and disregards the multiple values? Maybe it could insert a value like "XXX" so that it is easy to find.

Here is my query below. To explain, the duplication comes into play when the columns Regis_ID and Act_ID are the same, AND there was a duplication in testing for one of the samples (about 200 more columns).

 

[Fazza]

hello. It isn't totally clear to me what you want.

It sounds like, but I'm not sure, you want to exclude from the dataset combinations of REGIS_ID, Act_ID with more than 1 Something.

Please provide a small sample of data to demonstate/clarify the requirement - so input data & corresponding results. With enough sample data to show which records to include and which to exclude.

cheers

 

[clayt101]

hello. It isn't totally clear to me what you want.

It sounds like, but I'm not sure, you want to exclude from the dataset combinations of REGIS_ID, Act_ID with more than 1 Something.

Please provide a small sample of data to demonstate/clarify the requirement - so input data & corresponding results. With enough sample data to show which records to include and which to exclude.

cheers

Thank you for responding. That is what I was looking for exactly. I ended up just deleting the events where there was more than 1 value. Basically, it seems like the lab ran tests that occasionally sampled for the same component using a different test. This should not be an issue, except when they returned different values. To solve this, I went through and deleted all occurrences when the following occurred:
* Sample Date Matched
* Sample Component Matched
* Sample Location Matched
* Multiple Values were Returned

I left intact the occasions where all four from the list above were a perfect match.
It might not have been the best solution or the most time efficient, but it worked.

Thanks again though.
Clay

 

[Fazza]

cheers