Count records with criteria

[Thompyt]

How do I count the instances of Y where X=1 and z>1 (Repeated Z value in the field)



Example outcome:

X.....Y.....Z
1.....1.....1
1.....2.....1
1.....3.....1

Use a Dlookup?

Thanks


Edit: I am using Text vice numbers because the table I am really working on uses short text. The point of table 2 is using it as a reference. I have pruned down the information store in there from the original table. The original data set X was 6 characters long. It is so I can place multiple data points. I understand in this instance I could have used in X in the query criteria where X = "1" and X = "2", but it becomes cumbersome to have 14 more X types to put in criteria.

How many times does X occur if there are multiple Z instances. so if there are 2 instances of Z, then you would get 1 for the first record, and 2 for the second record, 3 for the third record.........

[isladogs]

Don't use DCount.
Before I answer, some questions that may affect the solution

In table 1, why are you using text datatype for each field when all the data shown are numbers?
What is the point of table 2?

Also your tables should always have a primary key field.

[isladogs]

Not sure I understand your edit comments but there are at least 2 approaches:

a) Use DCount

DCount("Y","Table1", "X='1' And Z>'1'")

The single quotes are needed because you are using text fields

b) Create query to filter your records & call it e.g. Query1

SELECT Table1.X, Table1.Y, Table1.ZFROM Table1
WHERE (((Table1.X)='1') AND ((Table1.Z)>'1'));

Now create a second query based on that to count the records:

SELECT Count(Query1.Y) AS CountOfY FROM Query1;

[Thompyt]

That's a step closer. Thanks. Notice that there are multiple instances of Z of the same type.

Example:


Z..........Instances
14.........2
38.........2
39.........2

This query is just to pull the duplicates. Normally I wouldn't put 600 instances of Z

SELECT Table1.X, Table1.Y, Table1.Z
FROM Table2 INNER JOIN Table1 ON Table2.X = Table1.X
GROUP BY Table1.X, Table1.Y, Table1.Z
HAVING (((Table1.Z)="14" Or (Table1.Z)="38" Or (Table1.Z)="39"));

You get 6 records

X stays the same = 2

Y has 6 different records

Z has 3 sets of records

My desired outcome would be:

X.....Y.....Z
2.....1.....14
2.....2.....14
2.....1.....38
2.....2.....38
2.....1.....39

That's telling me how many times X occurred in Z when Z is the same value.

[Thompyt]

DLookUp("[X]","[Table1]","[Z] ='" & [Z] & "'" And "' & Count([Z])>1")

I know this comes closer to my intentions to get to where I am in my previous post.

How many times X occurred in Z, when Z in other rows, is the same value. Then count Z for each repeat.

[isladogs]

I'm sorry but I lost you a couple of posts back ....
Hopefully someone else can follow your gist & advise

[Thompyt]

Updated the file by filling out Fields:
X, Y,and Z

And adding Fields:
PID - Autonumber
W - Numeric
Notes - Text


In W I put the desired outcome, with the notes beside it the reason why I want that number to be there. I hope this will help define what I am trying to do.

Counting2a.zip

I did not add another Field to equate to "parent_equipment_item_id" as below. These are charts I made to try to develop what I am trying to do.

Z= Base (Platform)
Y.. is a child of Z where X is that Type (Mount such as XXB8ZX)
Then
....Y becomes the parent for the Box as a child on that Mount (Box such as Z0072D)
Then
.........Y becomes the parent for the Line A as a child on that Box(Box such as XXB840)
and if there
.........Y becomes the parent for the Line B as a child on that Box(Box such as XXB841)

If there was another Box on that Mount

Z= Base (Platform) where Z equals the First Time
Y.. is a child of Z where X is that Type (Mount such as XXB8ZX, but on a different row, so Different Y Value but will be greater than the first)
Then
....Y becomes the parent for the Box as a child on that Mount (Box such as Z0072D, but on a different row, so Different Y Value but will be greater than the first))
Then
.........Y becomes the parent for the Line A as a child on that Box(Box such as XXB840, but on a different row, so Different Y Value but will be greater than the first))
and if there
.........Y becomes the parent for the Line B as a child on that Box(Box such as XXB841, but on a different row, so Different Y Value but will be greater than the first))






Thanks

[isladogs]

Thanks for trying but no it doesn't help me understand.
However, maybe a fresh pair of eyes will see what this means at once.

I'm going to drop out of this thread now.
Good luck & hope you find a solution

EDIT:
I see you've added a huge amount of extra info + a detailed flow diagram since I replied.
Not sure how anyone could have guessed any of this from your first post!
Good luck!