How to create in different columns different count

kiler40

New member
Local time
Today, 10:50
Joined
Apr 7, 2015
Messages
7
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 :)
 
I think you would put

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

In your query
 
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...
 
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.
 

Attachments

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

Thank anyhow :)
 
you can using sql

Code:
 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
 

Users who are viewing this thread

Back
Top Bottom