Countif in Access

[Guest]

I have created a joined column showing Customer Name & order number & Date in
order to create a unique identifier for each order. There are instances of
multiple lines/orders for each customer and I want to be able to tell is this
identifier is found multiple time in my query. If I was using excel I would
do a countif(a:a,a1) and segregate out those with count >1. But have been
through the web site for 30 minutes and cannot find a similar problem.

 

[Guest]

and segregate out those with count >1
This sounds like you want to EXCLUDE any with the IDENTIFIER is present. If
this is correct then just use a criteria of <>"Identifier"

If you really want to count then in design view of your query add a new
field in the grid like this ---
Identifier: IIF([YourField] Like "*" & [Enter identifier] & "*", 1, 0)

If it finds what you enter at the prompt it will return a one.

 

[Guest]

GROUP BY the Identifier column and include a HAVING clause which tests for a
count of more than 1 row per grouping:

SELECT Identifier
FROM YourQuery
GROUP BY Identifier
HAVING COUNT(*) > 1;

Ken Sheridan
Stafford, England

 

[Guest]

The logic to the second one (IIF statement) is what I am looking for, but
need it to be automated in action. Is there a way to look at each row
against the entire field with out having to key it in via the [Enter
identifier] method?

This sounds like you want to EXCLUDE any with the IDENTIFIER is present. If
this is correct then just use a criteria of <>"Identifier"

If you really want to count then in design view of your query add a new
field in the grid like this ---
Identifier: IIF([YourField] Like "*" & [Enter identifier] & "*", 1, 0)

If it finds what you enter at the prompt it will return a one.

I have created a joined column showing Customer Name & order number & Date in
order to create a unique identifier for each order. There are instances of
multiple lines/orders for each customer and I want to be able to tell is this
identifier is found multiple time in my query. If I was using excel I would
do a countif(a:a,a1) and segregate out those with count >1. But have been
through the web site for 30 minutes and cannot find a similar problem.

 

[Guest]

I still do not follow what you are saying. If what Ken Sheridan posted did
not answer your question then post 5-6 records of your data and what you
expect to get as a result.

The logic to the second one (IIF statement) is what I am looking for, but
need it to be automated in action. Is there a way to look at each row
against the entire field with out having to key it in via the [Enter
identifier] method?

and segregate out those with count >1

This sounds like you want to EXCLUDE any with the IDENTIFIER is present. If
this is correct then just use a criteria of <>"Identifier"

If you really want to count then in design view of your query add a new
field in the grid like this ---
Identifier: IIF([YourField] Like "*" & [Enter identifier] & "*", 1, 0)

If it finds what you enter at the prompt it will return a one.

I have created a joined column showing Customer Name & order number & Date in
order to create a unique identifier for each order. There are instances of
multiple lines/orders for each customer and I want to be able to tell is this
identifier is found multiple time in my query. If I was using excel I would
do a countif(a:a,a1) and segregate out those with count >1. But have been
through the web site for 30 minutes and cannot find a similar problem.