Solved Project Count

Weekleyba

Registered User.
Local time
Today, 06:53
Joined
Oct 10, 2013
Messages
593
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.

1623938381223.png


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.
1623938788358.png

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".
1623938938584.png

1623939050610.png

1623938676836.png


Any help would be appreciated!
 
you need to Aggregate it (total query).
 
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?

1623941416616.png


1623941477514.png


1623941522720.png
 
create new aggregate query and remove the 2 extra Joined table.
on your form just dlookup() the value on this query.
 
Try changing Group By to Expression for the calculated fields (Contingency,TotalPoolRequest, etc.).
 
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.
 

Attachments

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.
1624033638090.png
 

Attachments

Why not rely on the built-in navigation bar at the bottom of the screen?
 
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?
 
That is because your concatenation is wrong?, You are mixing field values and text?
Post actual sql code within tags, not pictures.
 
Both the FYID and ProjectID are numbers.
What do I need to do to correct?

Code:
ProjectCount: DCount("*","T_Project","FYID = " & [FYID] And "ProjectID<=" & [ProjectID])
 
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!
 

Attachments

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

Users who are viewing this thread

Back
Top Bottom