Hi,
  
I have 1 Access table called (Backtesting)and am looking for a
solution to the following:

Count the number of duplicate entries in each column NOT records.

Table format has 300 records (rows) and 35 Columns 
Format Example below:

BTID               34Ema                 CCI-15M        CCI-60M 
1                  TRUE                  ZLC            SCCI 
2                  FALSE                 ZLC            ZLC 
3                  FALSE                 ZLC            ZLC 
4                  TRUE                  ZLR            ZLC 
5                  FALSE                 SCCI           ZLC 
6                  FALSE                 ZLC            SCCI 

I want a report or SQL Query to achieve the following
For each column: 

Count total Duplicates in each of the 35 columns NOT records.
i.e. CCI-15M field from above will have :
SCCI 1 
ZLC 4 
ZLR 1 

Being trying to work it out, but it has beaten me!

SELECT BackTesting.[CCI-15M]
FROM BackTesting
WHERE (((BackTesting].[CCI-15M)="count.[CCI-15M]"));

Thankyou

---

Request for Question Clarification by lotd-ga on 28 Oct 2005 09:25 PDT

Hi bremar,

The SQL needed to achieve what you are after for all 35 columns will
need to be quite long. You will need to join each result set using the
UNION keyword after each select statement. For example for the columns
you have listed, the SQL will be:

SELECT Backtesting.[CCI-60M] AS Entry, COUNT(Backtesting.[CCI-60M]) AS [Count]
FROM BackTesting
GROUP BY Backtesting.[CCI-60M];
UNION 
SELECT Backtesting.[34ema], COUNT(Backtesting.[34ema]) 
FROM BackTesting
GROUP BY Backtesting.[34ema];
UNION SELECT Backtesting.[cci-15m], COUNT(Backtesting.[cci-15m]) 
FROM BackTesting
GROUP BY Backtesting.[cci-15m];

I have also added ?As Entry? and ?AS [Count]? in the first select
statement so that the column names appear as ?Entry? and ?Count?
respectively.

Please let me know if this solves your problem and I will post it as
an official answer.

Regards,
lotd ? Google Answers Researcher

---

Request for Question Clarification by lotd-ga on 28 Oct 2005 13:21 PDT

Hi bremar,

Please let me know if the following SQL gives you the desired result.
Obviously you would need to extend the SQL to add an entry for each of
the columns in your table.

SELECT 
([34ema] & " " & count([34ema])) AS ['34ema'], 
([cci-15m] & " " & count([cci-15m])) AS ['cci-15m'], 
([cci-60m] & " " & count([cci-60m])) AS ['cci-60m']
FROM backtesting
GROUP BY [34ema], [cci-15m], [cci-60m];

Regards,
lotd - Google Answers Researcher

---

Request for Question Clarification by lotd-ga on 29 Oct 2005 06:15 PDT

Hi bremar,

Please can you let me know whether there is a value in every column
for every row. i.e. Whether there are any nulls any where in the
table.

Regards,
lotd - Google Answers Researcher

Hi,

You need to use the COUNT and GROUP BY Clause as follows to count the 
Total duplicates in each column.

Use the following query

SELECT Backtesting.[CCI-16M], COUNT(Backtesting.[CCI-16M])
FROM BackTesting
GROUP BY Backtesting.[CCI-16M];

Thanks bharatm!
Worked great for 1 column.
When I tried to apply this to 2 columns: 
CCI-15M and CCI-60M

(as I am after the SQl query
to perform all 35 columns)

SELECT Backtesting.[CCI-15M], COUNT(Backtesting.[CCI-15M])
FROM BackTesting
GROUP BY Backtesting.[CCI-15M]
SELECT Backtesting.[CCI-60M], COUNT(Backtesting.[CCI-60M])
FROM BackTesting
GROUP BY Backtesting.[CCI-60M];

The following error occured:
Syntax Error. in query expression
'Backtesting.[CCI-15M]
SELECT Backtesting.[CCI-60M]'.

Is there a joining aggregate I need to use?

Thanks

Thanks bharatm,

On the right track.
I apologise for not making myself more clear from the beginning.

From the following table:

BTID               34Ema                 CCI-15M        CCI-60M 
1                  TRUE                  ZLC            SCCI 
2                  FALSE                 ZLC            ZLC 
3                  FALSE                 ZLC            ZLC 
4                  TRUE                  ZLR            ZLC 
5                  FALSE                 SCCI           ZLC 
6                  FALSE                 ZLC            SCCI 


Desired result is a table which appears as individual columns:

34Ema              CCI-15M       CCI-60M                    
TRUE 2             SCCI 1        SCCI 2
FALSE 4            ZLC 4         ZLC 4
                   ZLR 1 

Current SQL returns:


Entry	Count
0	27
-1	6
HFE	1
HFE	2
SCCI	6
SCCI	12
ZLC	14
ZLC	21
ZLR	5

This should be the final question, before we post it as
an official answer.

Thanks

Thanks Lotd

This is very close. 
The only thing it is not doing, is calculating the 'TOTAL'number of dupicates.


When I ran your query on my table of 33 records, this is the result.

'34ema'	       'cci-15m'	'cci-60m'
TRUE 1	        SCCI 1	         ZLR 1
TRUE 1	        ZLC 1	         SCCI 1
TRUE 1	        ZLC 1	         ZLC 1
TRUE 1	        ZLR 1	         HFE 1
TRUE 1	        ZLR 1	         ZLC 1
TRUE 1	        ZLR 1	         ZLR 1
FALSE 1	        HFE 1	         HFE 1
FALSE 1	        SCCI 1	         SCCI 1
FALSE 2	        SCCI 2	         ZLC 2
FALSE 2	        SCCI 2	         ZLR 2
FALSE 9	        ZLC 9	         SCCI 9
FALSE 10	ZLC 10	         ZLC 10
FALSE 1	        ZLR 1	         SCCI 1
FALSE 1	        ZLR 1	         ZLR 1

All these duplicates above are exactly right.
Now the desired result FORMAT/LAYOUT with the correct duplicate amounts is:


34Ema              CCI-15M           CCI-60M                    
TRUE 6             SCCI 6            SCCI 12
FALSE 27           ZLC 21            ZLC 14
                   ZLR 5             HFE 2
                   HFE 1             ZLR 5

Both queries have exactly the same number of duplicates. 
Just need to total the amounts somehow to achieve the format above.

Thanks, once this is done, I have an Access Reports question on the
same table,I will post for $50

Brett

Yes there are Nulls in the table. Not many though.
Is it easier if there is a value in each column for every row?
As I can do this.

Thanks

Bremar,

Thanks for the clarification.
I did think it might help earlier but I am not so sure now.
I am still looking into a solution and will hopefully have an answer for you soon. 

Regards,
lotd - Google Answers Researcher

bremar,

I have hit a mental brick wall at the moment. I will come back to the
question later today but will open it up to any other researchers
which would like to attempt to solve the problem. It is possible to
group and display the count for each column individually, however when
attempting to combine more than one column then the column(s) with
less than the maximum number of rows will split the duplicates and you
will have many entries for the values that should be grouped.

Thanks,
lotd - Google Answers Researcher

Thanks lotd

Thanks lotd,

I don't know if this will make it easier.

My end goal is to have 35 columns, appear on an 'ACCESS Reports' page.

Each column will show the total number(COUNT)of duplicates,
within the coulmn from a table/Query database.

You mentioned:
It is possible to group and display the count for each column
individually, however when attempting to combine more than one column
then the column(s) with
less than the maximum number of rows will split the duplicates and you
will have many entries for the values that should be grouped.

To overcome this issue, I can populate each record so no 'NULL' entries exist.
i.e. There will be NO blank entries for any record

Does this help?

Thanks again

Hi bremar,

Apologies for the delay in responding but I was not around my PC yesterday.
I do not think NULL entries will actually make any difference to the
end solution. I will work on the problem again today and will let you
know if I make any progress later this evening.

Regards,
lotd

Thanks very much lotd!

Hi bremar,

I think, without a table re-design or involvement of a macro or
external program there is no elegant solution, although I may well be
wrong.

The brute force inefficient solution would be:

1. Create a single query for each column to count and group the duplicates
2. Create a report for each query
3. Create a master report which would include all of the reports
created as subreports.

Sorry I wasn't able to be of more help.

Regards,
lotd

Thanks for trying!
It sounds more involved than I first thought and may need professional programming

Regards

Bremar

I assume you mean ms-access.

You use the dcount funtion to do stuff like this, this allows you to
specify the domain in which you are grouping.

Its fully documented in the help???

Thanks dbest-ga,

Read through DCOUNT:

I understand the DCount function is used to count the number of
records in a domain when you don't need to know their particular
values.'

I need to use it to count the number of duplicate records in a column.
Trying to display these on a report may be easier than in a QUERY.

Played around with DCOUNT, using the expr

i.e.

DCount(expr, domain, [criteria])
=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

This worked to count the total duplicate records for 1 field:

SELECT =DCount("[CCI-15M]","Backtesting","[CCI-15M] = 'zlc'")


The problem I am having is trying to total MULTIPLE duplicate records,
existing in a field and placing these results on a REPORT.

Tried UNION:

SELECT =DCount("[CCI-15M]","Backtesting","[CCI-15M] = 'zlc'")
UNION
SELECT =DCount("[CCI-60M]","Backtesting","[CCI-60M] = 'zlc'");

But no luck!!

Thanks for your suggestion.
Any advice to solve this question, would be appreciated and worth $50!