Re: [MS_AccessPros] Grouping and Sum on a report

 

Phucon-

What you need is:

=DCount("DepID","Department","Department.ComID = '" & [ComID] & "'")

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Feb 21, 2017, at 5:10 PM, saigonf7q5@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello Duan and Mr. Viescas

Here I include the sample query and the report output format. What I've been trying to archieve is to count how many department that an individual company has. For example, Company A has only 1 depatment, so the Depatment Count is 1. And both Companies B and C have 2 depatments, therefore the Department Count for each company is 2.

By adding a textbox name: txtDepCount, Control Source: = 1, Running Sum: Over All in the Department Header, and add another textbox to the Report Footer, Control Source: = txtDepCount, that will give the correct count of Total Departments (the grand total)in the Reprt Footer.

However, placing a textbox in the Department Footer with the Control Source: 
=DCount("DepID","Company","Company.ComID = 'Department.ComID'") the count becomes 0 (zero). What caused that trouble?

Note: the ComID is Text Type, not Number.

Phucon

the query

SELECT Company.ComID, Company.ComName, Department.DepID, Department.ComID, Employee.EmpID, Employee.EmpFirst, Employee.EmpLast
FROM (Company INNER JOIN Department ON Company.ComID = Department.ComID) INNER JOIN Employee ON Department.DepID = Employee.DepID
ORDER BY Company.ComName;

the report output format

Company: A
              Department:A01
                                          Employee: Fisrt               Last
                                                           xxxxxxxxxxxx xxxxxxxxxxxx
                                                           xxxxxxxxxxxx xxxxxxxxxxxx
                                                           xxxxxxxxxxxx xxxxxxxxxxxx
             Department Count = 1
----------------------------------------------------------------------------

Company: B
             Department:B01
                                          Employee: Fisrt  Last
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
             Department:B02
                                          Employee: Fisrt  Last
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
        
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
          Department Count = 2
----------------------------------------------------------------------------

Company: C
              Department:C01
                                          Employee: Fisrt  Last
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
              Department:C02
                                          Employee: Fisrt  Last
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx
                                                            xxxxxxxxxxxx xxxxxxxxxxxx

  
              Department Count = 2
----------------------------------------------------------------------------
  Total Departments = 5

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (12)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___