[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?
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: