Cross Tab Query Help

[clayt101]

I have a large table that I can run a cross tab query on. My issue is that when I run the cross tab query, there are some duplicates. I can find them by doing using the count function when running the query. Unfortunately, there are 1014 rows out of 16,000+ rows that have a duplicate some where in the row (anywhere there is not a 1 using the count function). Can anyone think of a fast, easy way to get at these to examine them?

Right now, my best idea is to run the multiple times and compare them. I would run it once as a max function, and then as an average function. I could then compare the results. This would knock out the multiples where the value is the same (for example if the cell has the max value of 0 and the average value of 0, that would mean that all of the multiple values all have the value of 0). I would then have to go in manually and compare them.

Of course, this is a very large database with about 60 columns, so if I were to compare the average crosstab query and the max crosstab query, I might run out of memory.

I guess what I want to know is are there any easy ways to do this (like a tool) that might be built into microsoft access that my novice abilities do not know about?

Thanks

 

[clayt101]

I am pretty sure it is a crosstab query that I want. Honestly, with the 31 rows of differences I am good. One possible issue (and it is not my issue) I can only explain:

You have samples taken at the same date and location. Using the "remove multiples" in excel, you get rid of all of the values that are exactly the same (this is fine for me, leaves me 31 rows out of 16000+). But what if you want to have a record of how many results that sample was taken and the same value was given. My solution does not work for this.

I am working with a database that has 1.5 million rows of individual sample components. The crosstab knocks it down to 16,000.

 

[Micron]

But what if you want to have a record of how many results that sample was taken and the same value was given. My solution does not work for this.

Then your table structure is incorrect or you are overlooking something because each sample taken should have it's own id, at least from a datbase perspective. I do understand sampling a bit, and can't picture a situation where I would generate two samples at the same date/time at the same location that had the same sample ID. This is how you would include the samples that produce the same values yet were not the same sample. Perhaps a lack of sample ID or not using it is one reason you are struggling with duplicates...