Count or DCount to obtain unique records in a subform field? (1 Viewer)

theferd

Registered User
Joined
Dec 23, 2019
Messages
21
[SOLVED]Count or DCount to obtain unique records in a subform field?

Hi All,

I have created this form that lets me select a material of construction from a combo box - formMaterialsearch - which filters a subform - Materialsubsearch. This works perfectly fine. What i want to do is have a text box display the number of unique records in the first field, assemblies, of the subform.

Each assembly has multiple parts, each composed of different materials so one assembly appears multiple times. I have experimented with a txt box within the subform, but as expected it returns 800 items for a certain material when I only have 200 unique assemblies all together. I have also looked at other posts around the web regarding using DCount or Count (Select Distinct) but no success.

What would be the best method at accomplishing this keeping in mind that more assemblies/parts/materials will be added to the database in the future? And how would it be done?
 
Last edited:

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,749
The basic problem with your question is that we have no idea what makes a record unique. It could be based solely on on field or the entire record. Can you post a pic of the loaded form(s)? I get the feeling that your subform might not be constructed correctly as it seems the assembly is what should be on the main form, and the sub information for one assembly on the subform. If that is true, a simple subform record count could suffice but we need more info about the design.
 

theferd

Registered User
Joined
Dec 23, 2019
Messages
21
Here is the form as it appears. The datasheet is the subform. The first field is catalog numbers for assemblies. As you can see searching for polycarbonate parts yields multiple parts from the same assembly. I would like B115877-I in this case to be counted only once.
 

Attachments

jdraw

Super Moderator
Staff member
Joined
Jan 23, 2006
Messages
12,553
theferd,

I recommend you review this thread that deals/dealt with parts and assemblies. Getting the underlying tables and relationships designed and tested to meet you requirement is critical.
Good luck with your project.
 
Last edited:

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,749
What i want to do is have a text box display the number of unique records in the first field, assemblies, of the subform.

I would like B115877-I in this case to be counted only once.
Notwithstanding that the records are unique (because at least one field does not contain the same data for a given component) these 2 statements are not asking for the same thing. Does your second post mean that you don't want a count - you want component values to be unique? If so, without seeing the rest of your db (i.e. its tables) you could try basing your subform on a crosstab query so that you can assign 'commodity' to being the row values. This could also provide a count right in the query if you need it. What I also don't know is if your subform is based on a table or query. If table, you will never achieve what you're looking for.
 

theferd

Registered User
Joined
Dec 23, 2019
Messages
21
This subform is referencing the query in the image attached. What I mean is I want to know how many assemblies make use of this material and not how many parts which is what a basic count tells me.

If there are 5 parts in a single assembly that use polycarbonate, I only want that to be counted as once in the tally.
 

Attachments

Micron

AWF VIP
Joined
Oct 20, 2018
Messages
1,749
I'd argue that there's really no such thing as a 'basic' count. You can count anything in a recordset and filter that count on any value in any field. I took some liberties with your field names to build a table as represented by this:


A simple Totals query on the material field
Code:
SELECT tblMatl.Material, Count(tblMatl.MatlID) AS CountOfMatlID
FROM tblMatl
GROUP BY tblMatl.Material;
produces this



which is what you seem to be asking for
I want to know how many assemblies make use of this material
Hope that helps.
 

Attachments

MajP

Access MVP
Joined
May 21, 2018
Messages
2,341
There is probably several ways to do this. I assume you are filtering on some Material of construction field. Obviously this is untested
Code:
Public Function GetDistinct(Material as variant) as long
  dim rs as dao.recordset
  dim strSql as string
  if isnull(material) then exit function
  strSql = "Select DISTINCT Component_Catalog_Number from SomeTable WHERE Material_Of_Construction = '" & Material & "'"
  set rs = currentDb.openrecordset(strSql)
  if not (rs.eof and rs.bof) then 
    rs.movelast
    rs.movefirst
    GetDistinct = rs.recordcount
  end if
end function
if that combobox returns a Material string you may be able to have a calculated control like
=GetDistinct([yourComboName])
 

theferd

Registered User
Joined
Dec 23, 2019
Messages
21
Thanks to Micron and Majp.
I went with something modified from Majp's code

Code:
 Dim getdistinct As String
 
  Dim rs As dao.Recordset
  Dim strSql As String
  strSql = "Select DISTINCT Component_Catalog_Number" _
  & " FROM Components RIGHT JOIN ((tblSubctype RIGHT JOIN tblManufacturernum ON tblSubctype.SubctypeID = tblManufacturernum.SubctypeID) LEFT JOIN (tblDistributornum LEFT JOIN tblSubcassociation ON tblDistributornum.ID = tblSubcassociation.SubsupID) ON tblManufacturernum.ID = tblDistributornum.ManufacturernumID) ON Components.ComponentID = tblSubcassociation.CatalogID" _
  & " WHERE [Material] = '" & Me.cmboMaterialsearch & "'"
  Set rs = CurrentDb.OpenRecordset(strSql)
  If Not (rs.EOF And rs.BOF) Then
    rs.MoveLast
    rs.MoveFirst
    getdistinct = rs.RecordCount
    txtassemblycount = getdistinct
  End If
I pulled the From statement using the SQL line used to construct the query then included the code as part of the command button sub.

I want to apologize for the confusion I may have caused Micron due to unclear terms. I am relatively new to Access and VBA.

In the most basic sense I wanted to know after filtering for materials how many unique catalog numbers were present in that column. After searching for polycarbonate parts, it now returns 82 assemblies. I will probably count this manually to be sure but this number atleast make sense.
 

isladogs

CID Moderator
Staff member
Joined
Jan 14, 2017
Messages
12,329
Post #9 was moderated. This post is just to trigger email notifications
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom