0

I am fairly new to access. I am trying to create a column that calculates duplicates values in a query. Taking in consideration that the duplicates value may change after other few filters taking action.

Supplier    Spec   SupplierDuplicate
   A         X            2
   B         Y            1
   A         Z            2
   C         X            1

I most appreciate your help!

forpas
  • 160,666
  • 10
  • 38
  • 76
Samuel Magharious
  • 11

1 Answers1

0

I think you can run a SELECT query with DCount() function to get expected result.

SELECT Table1.Supplier, Table1.Spec, DCount("[Supplier]","[Table1]","[Supplier]='" & Table1.Supplier & "'") AS SupplierDuplicate FROM Table1;

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Hi Harun, Thanks fr your response. However, I am getting this error "undefined function dcount in expression" I am using Access 2016. – Samuel Magharious Aug 10 '20 at 15:20
  • Can you show your full expression? `DCount()` is available to all version of access. – Harun24hr Aug 11 '20 at 01:50
  • I think the problem that I currently have microsoft office 15.0 access database engine object library, and I need 16.0 SELECT [MCL SQL list].ID, [MCL SQL list].Specification, [MCL SQL list].[Specification Name], [MCL SQL list].Supplier, [MCL SQL list].City, [MCL SQL list].Procedure, [MCL SQL list].StateCountry FROM [MCL SQL list], DCount("[Supplier]","[MCL SQL list]","[Supplier]='" & [MCL SQL list].Supplier & "'") AS SupplierDuplicate FROM [MCL SQL list]; WHERE ((([MCL SQL list].SupplierDuplicate)=[forms]![Lookup Supplier]![Text42])); – Samuel Magharious Aug 12 '20 at 15:48