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

You could use a DCount() or subquery in the MultipleOrder field, but on larger datasets the two-query method would likely be much more efficient.  That assumes you were going to use a join on the queries.

Paul

------ Original Message ------

From "Sean Cooper via groups.io" <smcjb=yahoo.com@groups.io>

To "MSAccessProfessionals@groups.io" <msaccessprofessionals@groups.io>

Date 1/13/2023 10:29:56 AM

Subject [MSAccessProfessionals] Identify non-unique fields in a column

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