How to create in different columns different count

[kiler40]

Hello Everybody,
I`m a really new Access user and i`m trying to create a querie that creeate a different count in different columns. In column B to count number of imputs, and in column C to count number of unique imputs.
Here is an example

ColumnA ColumnB ColumnC
A WIRE1 OPTION1
A WIRE2 OPTION1
A WIRE3 OPTION1
A WIRE4 OPTION1
B CABLE1 OPTION2
B CABLE2 OPTION2
B CABLE3 OPTION3

And what i look as result in another table is:

ColumnA ColumnB ColumnC
A 4 1
B 3 2

Can you please help me with this ?

Thank you in advance

 

[sneuberg]

I think you would put

Count(ColumnA) as NumberOfInputs, Count(DISTINCT Column A) as UniqueInput

In your query

 

[kiler40]

In column A i can easily find the unique inputs,
In column B is also easy to count how many imputs are there per input in A. My problem comes when i need to find how many unique inputs are there per input in columnA.

Hope this explanation is not too messy...

 

[sneuberg]

Sorry about my previous post. Access doesn't even support DISTINCT in counts.

When you say unique inputs per columnA I'm assuming you mean unique ColumnC per ColumnA or in the example unique options.

The attached database solves this in a Rube Goldberg fashion by first getting the distinct ColumnA, ColumnC in the query qryDistinctOptions then the counts for this are obtained in qryCountOfDistinctOptions. In a separate query qryCountOfDistinctA the counts of the distinct ColumnA are obtain. Then the counts are combined in qryFinal.

There's probably a more concise way of doing this.

 

[kiler40]

Ah this i can do yes.
although it is quite nice
So there is no way to do it in one queries ??

Thank anyhow

 

[sneuberg]

So there is no way to do it in one queries ??

You might be able to do this in one query with subqueries, but I'm not very good at that.

 

[CJ_London]

you can using sql

 SELECT CC.ColA, CountOfColB, CountOfColC
FROM (SELECT ColA, Count(ColC) AS CountOfColC
FROM (SELECT DISTINCT ColA, ColC
FROM Table2)
GROUP BY ColA)  AS CC INNER JOIN (SELECT ColA, Count(ColB) AS CountOfColB
FROM (SELECT DISTINCT ColA, ColB
FROM Table2)
GROUP BY ColA)  AS CB ON CC.ColA = CB.ColA

 

[kiler40]

Thank you all
I did the the job