Using =DCount to show the totals within a Report (1 Viewer)

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
What I have is report that lists all the project names, below there is a total of the projects and under that is a grand total number of all the records under every project.

What I am trying to achieve is having a total number of records for each project.

Table
tblProject – contains the projects
tblRecord – contains all the records

Relationship
tblProject[ID] with a one-to-many relationship to tblRecords[ProjectID]

The report is bound to tblProject

This field lists all the project names (WORKS)
Name: Project
Control Source: Project

This field gives the total number of records for each project (DON'T WORK)
Name: TotalProjectRecords
Control Source: =DCount("[Project]","tblRecord","[Project]='*'")

This field gives the total number of projects (WORKS)
Name: TotalProjects
Control Source: =Count([Project])

This field gives the total number of records in all projects (WORKS)
Name: TotalRecords
Control Source: =DCount("Project","tblRecord")
 

Minty

AWF VIP
Local time
Today, 12:32
Joined
Jul 26, 2013
Messages
10,354
You need to add the project type as criteria to the dcount

=DCount("*","tblRecord","[Project]='"& [Project] & "'")

Assuming the project field is text and included in the report in a control called Project.
 

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
You need to add the project type as criteria to the dcount

=DCount("*","tblRecord","[Project]='"& [Project] & "'")

Assuming the project field is text and included in the report in a control called Project.

Thanks for the speedy reply.

This has given me the number of total records in the database, rather than the total number of records of each project.
 

plog

Banishment Pending
Local time
Today, 07:32
Joined
May 11, 2011
Messages
11,611
This should be done via a query, which will be the source of the report. Actually, 2 queries, the first will be a sub query:

Code:
SELECT ProjectID, COUNT(ProjectID) AS TotRecs
FROM tblRecord
GROUP BY ProjectID

That gets the total records for each project. Name it "sub1", then use it and tblProject to build a new query. JOIN them via ProjectID and change the join to the option tht shows all records from tblProject and just those that match from sub1. Bring donw all the fields you need from tblProject and then use the below to determine the number of records:

TotalRecords: NZ(TotRecs, 0)

That will give you all the data you need for the report, no DCount needed.
 

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
Thanks @Plog, I have created a query and tried to follow what you have said, I'm now lost.

Can this be done with a solution like @Minty has given?
 

Minty

AWF VIP
Local time
Today, 12:32
Joined
Jul 26, 2013
Messages
10,354
Not easily - I misread your requirement.

As plog explained, you need to do a sub query to get the first stage of your results.
Assuming you have the first query producing the results you want, you simply need to drag that into your reports query / record source in the builder and join it by the project field.

If you are really stuck then strip down your database to provide enough data t play with and your report and, and we'll have a look for you.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:32
Joined
May 7, 2009
Messages
19,169
Create a Total query thatis grouped by project, add the project field again this time dont use group but Count.

Use this query in your textbox:

Totalrecords=dlookup("CountOfProject","queryname","Project='" me.project & "')
 

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
Create a Total query thatis grouped by project, add the project field again this time dont use group but Count.

Use this query in your textbox:

Totalrecords=dlookup("CountOfProject","queryname","Project='" me.project & "')

I have created a query that groups the project and counts the records, this bit works.

I can only get as far as calling up the value of the first record, and that number is showing up for all the projects in the report.

=DLookUp("CountOfProjectID","qryProjectTotals","[Project]")

It seems that I am so close to resolving it, yet so far.
 

Minty

AWF VIP
Local time
Today, 12:32
Joined
Jul 26, 2013
Messages
10,354
You aren't adding the criteria correctly.
See the example posted in Arne's post and look at the links in my signature.
 

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
You aren't adding the criteria correctly.
See the example posted in Arne's post and look at the links in my signature.


I have changed the code from
=DLookUp("CountOfProjectID","qryProjectTotals","[Project]")

to the following code
=DLookUp("CountOfProjectID","qryProjectTotals","Project=Project")

Both of these lines of code give me the same result of displaying the value of the first record in the Query.

I don't know what I should be putting in as the criteria, it needs to look at the Project name in the Query and display the value next to it if there is one in the text box within the report.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:32
Joined
May 7, 2009
Messages
19,169
=DLookUp("CountOfProjectID","qryProjectTotals","Project='" & Me.Project & "'")
 

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
=DLookUp("CountOfProjectID","qryProjectTotals","Project='" & Me.Project & "'")

I now get a pop up 'Enter Parameter Value' asking for a value of 'Me'.

When I look back at the code, it has changed itself to:
=DLookUp("CountOfProjectID","qryProjectTotals","Pr oject='" & [Me].[Project] & "'")
 

Minty

AWF VIP
Local time
Today, 12:32
Joined
Jul 26, 2013
Messages
10,354
Nearly - you need to make it match the current report Project name To do that you need to concatenate the reports ID into the dlookup ;

Code:
=DLookup("CountofProjectD","qryProjectTotals","[Project] = '" & Me.Project & "'")

To explain the criteria more fully
"[Project] =
This is the field in the query we are going to compare. Its text so we need to make it compare a string value from your report so we enclose the reports Project control in single quotes ;
'" & Me.Project & "'" )
Me.Project means return the value of the current object (your report) and the control called Project.

Does that make sense?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:32
Joined
May 7, 2009
Messages
19,169
If you got error use [Project] instead if Me.Project
 

Minty

AWF VIP
Local time
Today, 12:32
Joined
Jul 26, 2013
Messages
10,354
Our posts crossed - where are you putting this - it looks like you are putting it in the query?
It should be in the control on the report, or as originally suggested the totals query should be linked back to your record source query, then you can simply add it as a field from your query.
 

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
If you got error use [Project] instead if Me.Project

The following code is now displaying #Error
=DLookUp("CountofProjectD","qryProjectTotals","[Project] = '" & [Project] & "'")
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:32
Joined
May 7, 2009
Messages
19,169
Rename the textbox Project to txtProject


=DLookUp("CountofProjectD","qryProjectTotals","[Project] = '" & [txtProject] & "'")
 

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
Our posts crossed - where are you putting this - it looks like you are putting it in the query?
It should be in the control on the report, or as originally suggested the totals query should be linked back to your record source query, then you can simply add it as a field from your query.

I am entering the code into the Control Source of a text box called 'TotalProjectRecords' within the report called 'rptProjectTotals'.

The other text box that gives the Project name is called 'Project'.

The Query qryProjectTotals works, giving me the project name and the number of records within those projects.
 

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
I have renamed the text box Project to txtProject and changed the code to the following:

=DLookUp("CountofProjectD","qryProjectTotals","[Project] = '" & [txtProject] & "'")

The report shows #Error for the value (TotalProjectRecords)
 
Last edited:

GrahamUK33

Registered User.
Local time
Today, 12:32
Joined
May 19, 2011
Messages
58
Our posts crossed - where are you putting this - it looks like you are putting it in the query?
It should be in the control on the report, or as originally suggested the totals query should be linked back to your record source query, then you can simply add it as a field from your query.

I have used the qryProjectTotals as the record source of rptProjectTotals
Text box 'txtProject' control source is 'Project'
Text box 'TotalProjectRecords' control source is 'CountOfProjectID'

Total amount of Projects using =Count([Project])
Total records using =DCount("Project","tblTimmsImpact")

This has now worked, I have the report that I am after. :)

Thanks for all your help.
 

Users who are viewing this thread

Top Bottom