Solved Project Count (1 Viewer)

[Weekleyba]

I'm trying to count the number of projects based on the fiscal year (FY).
I'm using an expression in the query called ProjectCount as shown below and it works but not with the condition of FY.

It should also share that the database opens to a Main_F where there is a combobox for FY.
The FY is required to be selected before any cmd buttons can be executed.

So related to the issue, is the cmd button, Project Score Sheet.
When a FY is selected and the button is clicked, it opens the ProjectScoreSheet_F which based on the AllScoreSheet_Q with the criteria shown below.
The red circle shows the project count. This is incorrect. There are 59 projects related to 2021 but only 1 project related to 2022. So this should show "1" and not "60".

Any help would be appreciated!

 

[arnelgp]

you need to Aggregate it (total query).

 

[Weekleyba]

I assume you mean so it can be "Group By".
I tried and it still gives be "60" in the ProjectCount.
It also gives me an error for the Contingency when I switch to a total query.
What am I doing wrong here?

 

[arnelgp]

create new aggregate query and remove the 2 extra Joined table.
on your form just dlookup() the value on this query.

 

[bastanu]

Try changing Group By to Expression for the calculated fields (Contingency,TotalPoolRequest, etc.).

 

[Weekleyba]

Ok... I just can't figure this out. Not sure what I'm missing.
Attached is a copy of the database.
I've tried the new aggregate query and the dlookup in the default value of the form but it's not working.
Can you take a look and see where I'm going wrong?
I'm puzzled because in theory it seems like it would be easy to accomplish.

 

[Weekleyba]

Try changing Group By to Expression for the calculated fields (Contingency,TotalPoolRequest, etc.).

That did resolve the issue. Not sure why...

 

[bastanu]

Because that is the correct syntax....

 

[Weekleyba]

I still need a hand with this.
I created a small database to demonstrate what I'm looking for.
Basically, I want a counter, to show not only the total number of projects for a specific FY but, also each project number, as shown below.
I attached the small database.
Can someone help me out as I am stuck on this?
thanks.

 

[bastanu]

Why not rely on the built-in navigation bar at the bottom of the screen?

 

[Weekleyba]

I usually turn the navigation bar off so the users cannot add records but maybe in this case you're right....
To accomplish what I want however, I'm thinking the query Q_ProjectCount, needs to be tweaked so that the counter starts over based on a different FYID.
Any ideas how to do this?

 

[Weekleyba]

I was thinking something like this?

 

[Weekleyba]

But it doesn't work....

 

[Gasman]

That is because your concatenation is wrong?, You are mixing field values and text?
Post actual sql code within tags, not pictures.

 

[Weekleyba]

Both the FYID and ProjectID are numbers.
What do I need to do to correct?

ProjectCount: DCount("*","T_Project","FYID = " & [FYID] And "ProjectID<=" & [ProjectID])

 

[bastanu]

Is this what you're trying to do? It is called a ranking query (within a group)?
Cheers,

 

[Weekleyba]

YES!! Thank you some much for looking at this.
Here's the final database example for sequential numbering based on a group.
Hope this helps someone else one day.
Thanks again Vlad!

 

[bastanu]

You're very welcome, good luck with your project!
Cheers,