Count number of times data is same in two different fields, same table

[soggycashew]

Hello im trying to figure out a sql that if in the same table (tbl_ComponentP arts) and same record a number in the field (ComponentID) and text in the field (Part) are the same in another record in that table then count those records. Example:

ComponentID             Part
      1                test 2
      36               test 13
      1                test 2
      1                test 1

The count would be 2 because two fields are the same.

[NeoPa]

GROUP BY the two Fields in question then Count(*).

[soggycashew]

Thanks NeoPA that works.... Now to use it in a VBA SQL how would my query SQL go?

SELECT tbl_ComponentParts.ComponentID, tbl_ComponentParts.Part, Count(*) AS UsedAlready
FROM tbl_ComponentParts
GROUP BY tbl_ComponentParts.ComponentID, tbl_ComponentParts.Part
HAVING (((Count(*))>1));
 

[NeoPa]

I don't believe I understand the follow-on question.

Your SQL seems fine. No idea what VBA SQL could possibly refer to.

[soggycashew]

Im trying to figure out something what can be removed in the strSQL and how to do the same thing with a DCOUNT.

Dim strSQL as integer
 
strSql = "SELECT tbl_ComponentParts.ComponentID, Count(*) AS UsedAlready " & vbCrLf & _
"FROM tbl_ComponentParts " & vbCrLf & _
"WHERE (((tbl_ComponentParts.ProductID)=[Forms]![frm_UpdateProductComponentsParts]![cboProduct])) " & vbCrLf & _
"GROUP BY tbl_ComponentParts.ComponentID " & vbCrLf & _
"HAVING (((Count(*))>1));"
 
If IsNull(strSQL) then 
'do this
Else
'Do that
end if
 

Or convert the SQL into a DCount for a text box, how would that go?

=DCOUNT("*","tbl_ComponentParts","ProductID=" & [Forms]![frm_UpdateProductComponentsParts]![cboProduct] and something else?

[twinnyfo]

SoggyCashew,

I’m not sure I understand your question. I think you have your answer to the DCount() issue with your last bit of code. Just use all the criteria to need to count the specific records you are interested in. Since you are only using one table, that should be pretty straightforward .

[NeoPa]

SoggyCashew:
Im trying to figure out something what can be removed in the strSQL and how to do the same thing with a DCOUNT.

I'm afraid that makes absolutely no sense to me. I can't even make it into a recognisable sentence.

I think you're possibly assuming I must understand based on an understanding of what you're thinking about that isn't actually correct. Unfortunately though, I can't help until you express the actual situation clearly.

Something that may prove helpful to know is that the Domain functions require a Domain parameter (Second) that is not SQL. QueryDefs or simple tables are fine and it will take the name of either as a valid string value.

Otherwise I can't help until I understand what you're asking for.