help with counting in queries

[nachumy]

I would like to count how many times each private contact id appears in order in the private records (via relationship) .The result in the "count" column in this case should be (from top to bottom): 1,2,1,2,1,1


thanks in advance

 

[arnelgp]

is this a Query?
create another query based on this Query:

select [private records.id], [private contacts.id],
DCount("1", "firstQueryName","[private contacts.id] = " & [private contacts.id] & " and [private records.id] <= " & [private records.id]) As Count from firstQueryName;

 

[CJ_London]

your example looks like it is too simple - what if contactid 1 appears against recordid 7?

and why two tables? Would expect only one table

 

[theDBguy]

Hi. Welcome to AWF!

I am not sure I follow your description. In your example data, each private contact only appears once in private records id. So, I don't know how you got 1,2,1,2,1,1 from that. Also, I don't understand what you mean by "in order."

 

[The_Doc_Man]

If that is a query, unless there is an "AS count" for that third column, you have a problem in that you are using a reserved word as a field name in a context where SQL will interpret it as an SQL Aggregate.

 

[Lucky33]

nachumy, what I see is that you are trying to get the count of Private Contacts.ID when the same ID appears in consecutive rows. If that is so, you need to clarify the following:
1- how are the records in this query sorted? by date? by primary key? is the sorting affects the results you are looking for? apparently from your example, they are not sorted by Records.ID
2- do you need to see the first column Private records.ID? are both fields in the same table and if not how are the two tables related?

Answering the above may help those who are trying to help you figure out the right "Select Query" which I believe will be a little more complicated than it looks.
As The Doc Man noted, I suggest that you change the name of the third column to anything other than Count or DCount and avoid any reserved word

CJ_London, if what I said makes sense, then, if the contactID 1 appears in the 7th row, then the count will be 1 again.

 

[CJ_London]

@Lucky33 It probably does, but that makes for a much more complicated solution.

The ops thread title implies that complication, but the example data doesn’t. From the example data a simple running count query would work. If contactid was 1 for record 7 then you need a way to ‘reset’ the count. Which can be done by comparing the current record with the previous one.

But from the example data there appears to be two tables in play (records and contacts) whereas I would expect only one table - so the situation becomes even more complex.

you make a good point about order - I had assumed the order was specified on recordid, but perhaps not since these values are potentially not reliable enough to define an order

will need to wait for the op to clarify what the data really looks like

 

[Pat Hartman]

Select [Private Records].ID, [Private Contacts].ID, Count(*) As CountOfConacts
From [Private Records] Inner Join [Private Contacts] On [Private Records].ID = {Private Contacts].someunknownname
Group by [Private Records].ID, [Private Contacts].ID,

 

[Mike Krailo]

Pat, you have a curly brace in your On clause {Private Contacts].someunknownname

 

[Pat Hartman]

Thanks. Someone with good eyes Of course if the names weren't bad, we wouldn't need the square brackets at all