By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,819 Members | 1,459 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,819 IT Pros & Developers. It's quick & easy.

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

P: 5
Hello im trying to figure out a sql that if in the same table (tbl_ComponentParts) 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:
Expand|Select|Wrap|Line Numbers
  1. ComponentID             Part
  2.       1                test 2
  3.       36               test 13
  4.       1                test 2
  5.       1                test 1
The count would be 2 because two fields are the same.
3 Weeks Ago #1

✓ answered by NeoPa

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

Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,470
GROUP BY the two Fields in question then Count(*).
3 Weeks Ago #2

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

Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_ComponentParts.ComponentID, tbl_ComponentParts.Part, Count(*) AS UsedAlready
  2. FROM tbl_ComponentParts
  3. GROUP BY tbl_ComponentParts.ComponentID, tbl_ComponentParts.Part
  4. HAVING (((Count(*))>1));
  5.  
3 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,470
I don't believe I understand the follow-on question.

Your SQL seems fine. No idea what VBA SQL could possibly refer to.
3 Weeks Ago #4

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

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as integer
  2.  
  3. strSql = "SELECT tbl_ComponentParts.ComponentID, Count(*) AS UsedAlready " & vbCrLf & _
  4. "FROM tbl_ComponentParts " & vbCrLf & _
  5. "WHERE (((tbl_ComponentParts.ProductID)=[Forms]![frm_UpdateProductComponentsParts]![cboProduct])) " & vbCrLf & _
  6. "GROUP BY tbl_ComponentParts.ComponentID " & vbCrLf & _
  7. "HAVING (((Count(*))>1));"
  8.  
  9. If IsNull(strSQL) then 
  10. 'do this
  11. Else
  12. 'Do that
  13. end if
  14.  
Or convert the SQL into a DCount for a text box, how would that go?

Expand|Select|Wrap|Line Numbers
  1. =DCOUNT("*","tbl_ComponentParts","ProductID=" & [Forms]![frm_UpdateProductComponentsParts]![cboProduct] and something else?
3 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,278
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.
3 Weeks Ago #6

NeoPa
Expert Mod 15k+
P: 31,470
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.
3 Weeks Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.