Count equal values in one column, different rows.

[maxiwoll]

Hi! I have been trying to count duplicate BOMHeader for a while by using DCount and variations of Count, but with no luck.
The result I want is laid out in the table below.. But I cannot manage to count duplicate values and show the count in a new coloumn.

The reason for asking is that I have a database with spare parts, and the top level is called BOMHeader, with a list of items connected to the BOMheader in a separate table. I manage to count all items connected to the BOMheader, but I cannot count the number of times a BOM header is connected to a functional location ID. As you see the BOMHeader 329002 is connected to 2 functional location IDs, I have a list of 1500 functional locations...

DCount(expression;domain;[criteria]), is this at all possible?

Functional location ID	BOMHeader	CountOfBOMHeader
540-289-EC-0001	329002	2
540-289-EC-0002	329002	2
540-289-EC-0003	329013	3
540-289-EC-0004	329013	3
540-289-EC-0005	329013	3

<tbody>
</tbody>

Thank you for all help.

 

[maxiwoll]

Additional information regarding the tables.

The relationship of the tables are: Functional location table(Functional location id, equipentnumber) is connected to an equipmentt table (Equipment number, bom material), connected to BOM detail table (BomHeader, Bomdetail), which again is connected to a table with all item details (item number, description etc)

 

[apr pillai]

Create a Total Query by combining both 'Functional Location ID' and 'BOMHeader' fields like the sample SQL given below:

SELECT [Functional Location ID], [BomHeader], Count([BomHeader]) As myCount
FROM TableName
GROUP BY [Functional Location ID], [BomHeader];

If first two fields are in different tables then combine them together in a SELECT Query first by linking both the information through a common field in both tables. Use the Select Query as source for the Total Query.

 

[maxiwoll]

Tried your code, but the result was a column myCount with "1" in all rows.
The fields are in two different tables, but I do not understand how to combine them in a Total Query. Can you please explain?

 

[apr pillai]

Copy Paste the following SQL into a new Query's SQL editing window, make changes whereever Table Name or Field Name is in error and save the query with the name Query1:

SELECT [Functional location].[Functional Location ID], [BOM detail].[BOMHeader]
FROM [BOM detail]
INNER JOIN [Functional Location] ON [Equipment].[Equipmentnumkber]
INNER JOIN [BOM detail] ON [Equipment].[bom material]=[BOM detail].[BOMHeader];

Use Query1 as source Data Set for the Count taking Total Query as shown below:

SELECT Query1.[BOMHeader], Count(Query1.[Functional Location ID]) as myCount
FROM Query1
GROUP BY Query1.[BOMHeader];