Count criteria (1 Viewer)

Anderson · Jul 27, 2017

Hi,
Just after a bit of help please:

I'm trying to create a query that counts the number of duplicate records in a table, but that only looks at values outside a set of parentheses, so for example, the table field looks like this:
12345 (ABCDE)
And I want to count the number of times 12345 appears in the table, without considering what's between the brackets containing ABCDE.

I can get most of the way there, but can't work out how to specify the criteria.

Any advice would be gratefully received, thanks

isladogs · Jul 27, 2017

Do this in 2 steps.
1. Create a query which extracts the first part of that field

Code:

 left([fieldname],instr([fieldname],"(")-1)

The -1 should get rid of the space
Alternatively use Trim to do that

2. Create a duplicates query using the wizard based on the first query and group to count the dupes

Both could be combined using a subquery if you know how to use them

Galaxiom · Jul 27, 2017

Another way to get a number at the start of a field is with Val(). It stops collecting characters when a non numeric is reached.

Val([fieldname])

But watch out if the first non-numeric can be E or N then followed by more digits. It will be treated as Exponential notation.

Also Val() returns Double while Left() returns a String so it is not as reliable as ridders solution if there are enough digits to run into the precision limitations of floating point numbers.

Anderson · Jul 27, 2017

Genius! thank you, my mistake was trying to solve it with a single query

Anderson · Jul 27, 2017

Ah, damn, this is more complicated than I thought - some of the values have the format 12345 (ABCDE), some just have 12345, no parentheses - is there a way of returning all the values, whichever format they're in?

Galaxiom · Jul 27, 2017

Left([fieldname] & "(",instr([fieldname] & "(","(")-1)

It just adds the character in case it isn't present.
It doesn't really matter what character you concatenate to the first instance of the field.

isladogs · Jul 27, 2017

Anderson said:
Ah, damn, this is more complicated than I thought - some of the values have the format 12345 (ABCDE), some just have 12345, no parentheses - is there a way of returning all the values, whichever format they're in?

Answering this on my phone so can't format properly
Use the Len function in combination with the above
Basically if Len(your expression)>0 To check for whether a ( exists in field

Galaxiom · Jul 27, 2017

Or do it with Val()

Anderson · Jul 27, 2017

Looks like it can just be done with trimming actually, thanks though

Anderson · Jul 27, 2017

Is it possible to stop the values from grouping when the count query is run? Trying to end up with:
12345 2
12345 2
67890 1
So keeping all the original values listed but just adding a count in the next field according to how many times the value in the first field appears - seems easy to do from a table but it can't pick up the values from the query

Count criteria (1 Viewer)

Anderson

Registered User.

isladogs

MVP / VIP

Galaxiom

Super Moderator

Anderson

Registered User.

Anderson

Registered User.

Galaxiom

Super Moderator

isladogs

MVP / VIP

Galaxiom

Super Moderator

Anderson

Registered User.

Anderson

Registered User.

Similar threads

Users who are viewing this thread