Removal of Duplicates

[ooch1]

I have created a crosstab query, where the count field is the MPAN ID. However, there are duplicate MPANs in there that i need to be removed.

The SQL is as follows:

TRANSFORM Count(area54.MPAN) AS CountOfMPAN
SELECT "00. Total" AS Field, Count(area54.MPAN) AS [total mpans]
FROM area54
GROUP BY "00. Total"
PIVOT area54.CD In

I assume that select distinct comes into the equation somewhere, but I have tried putting it after the select statement to no availe.

Any help will be much apprecited!!!

Ooch

 

[dhookom]

It isn't clear what value you want to display rather than the Count. You can create true/false -1/0 as the display with:
TRANSFORM Nz(Count(area54.MPAN),0)>0 AS CountOfMPAN
SELECT "00. Total" AS Field, Count(area54.MPAN) AS [total mpans]
FROM area54
GROUP BY "00. Total"
PIVOT area54.CD In


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Kayo]

Ooch1, to easly get rid of "dups" simply create a new table.
1) Make a copy of current table's structure only
2) Do a make table query
3) In the new table design view, make the MPA ID field
the "Primary field." This will prevent any duplicate MPA data from copying to the new table.
4) Run the make table query.
Hope this helps.

V/r,

Kayo

 

[dhookom]

Kayo,
There is rarely a reason to "make" a table when a simple query will work. Making tables causes bloat and is considered in-efficient application development when a totals query can offer the same functionality.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ooch1]

Duane,

The value I am trying to count is the No. of MPANs. The MPAN is a form of ID, but there are duplicates in there. If i was doing a normal select query i would just do a SELECT DISTINCT and everything would be ok. However, I don't know how I can appl this to a crosstab query???

Ooch

 

[dhookom]

Your query is showing the number of MPANs per CD (column heading). You may need to type a few records so we know what you mean by duplicates. Also provide us with your expected results from the crosstab.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.