Solved Sequential Counter (1 Viewer)

[Weekleyba]

I have a query that I'd like to count the number of records. Seems easy enough but, I only want the number of records where the FYID is equal to what I have on the open form Main_F.
I'm close, but not there yet.
Can someone lend a hand?

 

[CJ_London]

In principle you would use an aggregate query but from your images you want a lot of fields so think to clarify your requirement show some example data and the result required from that example data

 

[MajP]

"ProjectID <= " & [ProjectID] & " AND FYID = " & [Forms]![MAIN-F]![cboFY]

If not your count will include other FYs

 

[The_Doc_Man]

DON'T pug that DCount in the pile. You'll get the count repeated for each record. Putting a DCount in a query is legal, possible, and usually wrong. The DCount function builds a query and therefore you would have a query within a query - for which the DCount's value wouldn't change for any iteration of that query. So if you have 5000 records and 50 of them match the desired value., you would get 5000 copies of 50.

 

[Pat Hartman]

Create a self join and that will give you the running count. Because the join uses a relational operator other than =, the QBE cannot display the join graphically. It needs to be SQL. Because of this annoyance, create the query using "=" for both fields in the join and once all the columns are selected and the aggregates created, then switch to SQL view and change the join.

 

[MajP]

So if you have 5000 records and 50 of them match the desired value., you would get 5000 copies of 50.

But that is not going to happen. Assuming the records have ProjectID 1,2,3,4,5....
Then the first record
Dcount("*","Project_T","ProjectID <= " & [ProjectID] & " AND FYID = " & [Forms]![MAIN-F]![cboFY]
=Dcount("*","ProjectT", "ProjectID <= 1 AND FYID = 2023")
second is
=Dcount("*","ProjectT", "ProjectID <= 2 AND FYID = 2023")
...
Nth
=Dcount("*","ProjectT", "ProjectID <= N AND FYID = 2023")

Not the most efficient way, but you can do a ranking query with dlookup

Microsoft Access tips:

How to rank records in Microsoft Access

allenbrowne.com

 

[Weekleyba]

Thanks MajP! It worked out great for what I am trying to accomplish.

 

[The_Doc_Man]

But that is not going to happen. Assuming the records have ProjectID 1,2,3,4,5....
Then the first record
Dcount("*","Project_T","ProjectID <= " & [ProjectID] & " AND FYID = " & [Forms]![MAIN-F]![cboFY]
=Dcount("*","ProjectT", "ProjectID <= 1 AND FYID = 2023")
second is
=Dcount("*","ProjectT", "ProjectID <= 2 AND FYID = 2023")
...
Nth
=Dcount("*","ProjectT", "ProjectID <= N AND FYID = 2023")

Not the most efficient way, but you can do a ranking query with dlookup

Microsoft Access tips:

How to rank records in Microsoft Access

allenbrowne.com

MajP, my response to this post was made having just dealt with

Query Count

I have a query that has one field (number1) and I would like to retrieve the records that are less than 5. The table has about 5000 records. So, if say 500 records meet that criteria, I want just the count or how many records. Thanks.

www.access-programmers.co.uk

which DID put a DCount in a query and got 5000 iterations of the count. This thread's first post used the less qualified DCount and would have gotten the same result. Of course when you modify the DCount, you WOULD get different results. But the original request would have done as I suggested.

 

[MajP]

This thread's first post used the less qualified DCount and would have gotten the same result. Of course when you modify the DCount, you WOULD get different results. But the original request would have done as I suggested.

Again, that is completely wrong. The OP's original was

Select ... Dcount("ProjectID","Project_T","ProjectID <= " & [ProjectID]) as Count

That clearly does not do as you suggest. It in fact provides a sequential but most likely non contiguous sequence. I only eliminated record counts outside the FY.

 

[The_Doc_Man]

Have it your way, MajP.

 

[MajP]

Not really about having it "My way." It is not an opinion, It is just a statement of fact. Your post in thread #4 was very emphatic, but incorrect, and contradicted the OP. This can be confusing to both the OP and other readers. Sometimes it is just better to admit you are wrong, instead of always trying to double down.
Although a dlookup is less efficient, it often does not matter. More importantly if the query needs to be editable than this is a very viable solution.

 

[The_Doc_Man]

We just had another thread where that repetition of results occurred that I had described as a possible result. YOU EVEN CONTRIBUTED TO THAT THREAD. DON'T tell me it won't happen. IT DID.

 

[MajP]

We just had another thread where that repetition of results occurred that I had described as a possible result. YOU EVEN CONTRIBUTED TO THAT THREAD. DON'T tell me it won't happen. IT DID.

You seem obsessed with some other thread, that is not really the same as this one. My comments have only been about this thread, and your comments concerning this thread.

 

[The_Doc_Man]

MajP, I think in the interests of avoiding "thread pollution" we should let this drop. You didn't get my point and I don't want to divert the thread.

 

[ebs17]

Sequential Counter​

There is no RowNumber query function in Access/Jet-SQL. Therefore, in many cases, a sequentiell counter is determined using a correlated subquery or a corresponding domain aggregate function (DCount).
@MajP correctly pointed out that in order to count properly, this subquery must have the same amount of data, and thus the same filter, as the main query.

The same results only come if the key for the correlation (ProjectID) is the same.

I don't particularly like domain aggregate functions in queries myself, but I wouldn't go as far as saying I never use them. Especially with sequentiell counter it turns out that DCount is faster than a corresponding subquery. Karl Donaubauer already pointed this out in his (quite old) AccessFAQ.

I had made my own measurements with combinations of different variants:
DCount - Subquery - QueryIncrement (own method)
+
Data sorted / unsorted (randomly distributed)
+
no index / individual indexes / composite index (multiple criteria were used)

Grundlagen - SQL ist leicht (10) - Nummerierung in Abfragen - MS-Office-Forum

SQL ist leicht (10) - Nummerierung in Abfragen Microsoft Access - Code Archiv

www.ms-office-forum.net

In any case, DCount was faster than the subquery and therefore preferable.