Counting specific numbers in textbox of two or more rows , (no duplicates) (3 Viewers)

[Aminam64]

I have a table with many rows , in order of counting the numbers in rows need a query to do that
A. B. C. D. E. F.
X. Y. 1. 3. 1. 3
X. Z. 4. 5. 4. 5
X. W. 6. 4. 6. 4
X. U. 5. 6. 5. 6
X. N. 2. 3. 2. 3
X. Have a 5 sub and 6 specific numbers
X. 5. 6
I wish u guys get the idea

 

[theDBguy]

Hi. Welcome to AWF!

Perhaps you can attach an Excel file with sample data and the results you want?

 

[Aminam64]

Here a sample in excel template , tnk u

 

[Gasman]

Here a sample in excel template , tnk u

Nothing attached?

 

[Mark_]

Are you trying to;
1) Return the COUNT of rows where there is a number in a specific column?
2) Return the COUNT of rows where there is a number in any column?
3) Return the number of columns per row that have numbers then total this number?

 

[Aminam64]

Sample attached

 

[Aminam64]

Are you trying to;
1) Return the COUNT of rows where there is a number in a specific column?
2) Return the COUNT of rows where there is a number in any column?
3) Return the number of columns per row that have numbers then total this number?

Numbers are duplicate in separate rows , I want to count one time for each number , as you see there is few number 3 or number 5 in each columns , I want to count just one time for each number

 

[June7]

This data is in table in Access? You provide 5 rows of sample data but say "4 rows" in statement.

You want to know how many distinct numbers are in all 5 records combined - which in this case is 6 numbers - all from the WelderX columns?

Your options are a VBA procedure or a sequence of queries, which can be nested.

Consider:

SELECT Q.*, (SELECT Count(*) FROM Data WHERE [lone no]=Q.[lone no]) AS CntRecs FROM
(SELECT [lone no], Count(*) AS CntNums FROM (
SELECT [lone no], welder1 AS welder FROM Joints
UNION SELECT [lone no], welder2 FROM Joints
UNION SELECT [lone no], welder3 FROM Joints
UNION SELECT [lone no], welder4 FROM Joints) AS Q
GROUP BY [lone no]) AS Q;

The output is

lone no	CntNums	CntRecs
swf	6	5

​

 

[arnelgp]

on my case, i uses VBA and call it from the query:
the vba:

Public Function fncCountDistinct(ByVal LoneNo As String, FieldName As String) As Integer
    Static db As DAO.Database
    Dim SQL As String
    If db Is Nothing Then
        Set db = CurrentDb
    End If
    SQL = "SELECT DISTINCT [" & FieldName & "] FROM weld WHERE [lone no]='" & LoneNo & "';"
    With db.OpenRecordset(SQL, dbOpenSnapshot, dbReadOnly)
        If Not (.BOF And .EOF) Then
            .MoveLast
            fncCountDistinct=.RecordCount
        End If
        .Close
    End With
End Function

Query1:

SELECT weld.[lone no],
fncCountDistinct([lone no],"joint") AS JointsCount,
fncCountDistinct([lone no],"Welder1") AS Welder1JointsCount,
fncCountDistinct([lone no],"Welder2") AS Welder2JointsCount,
fncCountDistinct([lone no],"Welder3") AS Welder3JointsCount,
fncCountDistinct([lone no],"Welder4") AS Welder4JointsCount
FROM weld
GROUP BY weld.[lone no], fncCountDistinct([lone no],"joint"), fncCountDistinct([lone no],"Welder1"), fncCountDistinct([lone no],"Welder2"), fncCountDistinct([lone no],"Welder3"), fncCountDistinct([lone no],"Welder4");

lone no	JointsCount	Welder1JointsCount	Welder2JointsCount	Welder3JointsCount	Welder4JointsCount
swf	5	5	4	5	4

​

you also need to normalize your data when you use Ms Access:

LoneNo-------------Entity--------------------JointNo
swf------------------joint---------------------1
swf------------------joint---------------------2
swf------------------joint---------------------3
swf------------------joint---------------------4
swf------------------joint---------------------5
swf------------------welder1-----------------1
swf------------------welder1-----------------2
swf------------------welder1-----------------4
swf------------------welder1-----------------6
swf------------------welder1-----------------3

etc...

​

 

[June7]

I am thinking normalized is like:

ID	lone no	joint	welder	data
1	swf	1	1	1
2	swf	1	2	2
3	swf	1	3	1
4	swf	1	4	1
5	swf	2	1	2
6	swf	2	2	3
7	swf	2	3	2
8	swf	2	4	2
9	swf	3	1	4
10	swf	3	2	5
11	swf	3	3	4
12	swf	3	4	4
13	swf	4	1	6
14	swf	4	2	4
15	swf	4	3	6
16	swf	4	4	6
17	swf	5	1	3
18	swf	5	2	2
19	swf	5	3	3
20	swf	5	4	3

​

 

[Isaac]

I wish u guys get the idea

I wish we did, too! You'll need to put a lot more effort into explaining your problem if you expect help from others.
You get out what you put in, which in this case was next to nothing.

 

[Gasman]

Numbers are duplicate in separate rows , I want to count one time for each number , as you see there is few number 3 or number 5 in each columns , I want to count just one time for each number

One number per record, or all records?

 

[CJ_London]

Do the numbers always range from 1 to 6?