Re: [MSAccessProfessionals] Identify non-unique fields in a column

Hi Sean,

Does the expression work? Is it really slow? Is InvoiceNum indexed?

Duane

On Jan 14, 2023, at 5:25 PM, Sean Cooper via groups.io <smcjb=yahoo.com@groups.io> wrote:



Thanks Duane. I created a simple example. The problem is in reality "InvoiceNum" is a large (10 or 12 digit I believe) exchange generated deal number, and the table has thousands of records in it - and I don't know what the deal numbers.

On Friday, January 13, 2023 at 03:11:14 PM CST, Duane Hookom <duanehookom@hotmail.com> wrote:

I expect a DCount() would work. 

Select *, IIf(DCount("*","Your Table Name","InvoiceNum='" & [InvoiceNum] & "'")>1, "Yes","No") As MultipleOrder

FROM …

Duane

On Jan 13, 2023, at 12:30 PM, Sean Cooper via groups.io <smcjb=yahoo.com@groups.io> wrote:



Greetings All.

I know that you can easily use Access to identify "Unique Records" but this is slightly different.

Imagine I have a simple table with CustomerID, ProductID & InvoiceNum but I want create a query with an additional column "MultipleOrder" which identifies if an Invoice has more than one product on it. ie if the InvoiceNum is not unique then it is a multiple order.

Hence the resulting query would like this

Obviously I can do this by creating two queries, the first to count how many records there are for each InvoiceNum and then a second query referencing both the table and the query to identify InvoiceNum with a count greater than one.

Is there a way to do this all in one query though?

Thanks, Sean