Need report to show all data

W

WMorsberger

I have a report that I am running - Although when run the report I want it to
show me the information whether there is a count of zero or not. How can I
set this up on my report or my query?

Here is the query that I have my report linked to - Even if the department
doesn't have a count to show, I still would like it to say zero.

SELECT tblAllWorkLoggedRecords.DEPARTMENT, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblAllWorkLoggedRecords
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT, tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date) Between [Enter Start Date:
mm/dd/yyyy] And [Enter End Date: mm/dd/yyyy]));
 
J

John Spencer

Do you have a table of Departments? If so include it in your query with an
outer join. Probably something LIKE the following.

SELECT tblDepartments.Department
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblDepartments LEFT JOIN tblAllWorkLoggedRecords
ON tblDepartments.Department = tblAllWorkLoggedRecords.Department
WHERE tblAllWorkLoggedRecords.Date Between [Enter Start Date: mm/dd/yyyy]
And [Enter End Date: mm/dd/yyyy]
OR tblAllWorkLoggedRecords.Date is Null
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT, tblAllWorkLoggedRecords.Date
 
W

WMorsberger

Here is the query that I have - I have tried all the joins and it doesn't
seem to be working, but then again maybe I'm missing something. It's still
not showing me all the departments

SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT
GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date)=[enter date]));


John Spencer said:
Do you have a table of Departments? If so include it in your query with an
outer join. Probably something LIKE the following.

SELECT tblDepartments.Department
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblDepartments LEFT JOIN tblAllWorkLoggedRecords
ON tblDepartments.Department = tblAllWorkLoggedRecords.Department
WHERE tblAllWorkLoggedRecords.Date Between [Enter Start Date: mm/dd/yyyy]
And [Enter End Date: mm/dd/yyyy]
OR tblAllWorkLoggedRecords.Date is Null
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT, tblAllWorkLoggedRecords.Date

WMorsberger said:
I have a report that I am running - Although when run the report I want it
to
show me the information whether there is a count of zero or not. How can
I
set this up on my report or my query?

Here is the query that I have my report linked to - Even if the department
doesn't have a count to show, I still would like it to say zero.

SELECT tblAllWorkLoggedRecords.DEPARTMENT, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblAllWorkLoggedRecords
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT, tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date) Between [Enter Start Date:
mm/dd/yyyy] And [Enter End Date: mm/dd/yyyy]));
Click to expand...
Click to expand...
 
J

John Spencer

You need to move the HAVING clause to a WHERE clause and also search for
nulls since you are looking at the Many table. Try this modification to
your SQL.
SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT

WHERE tblAllWorkLoggedRecords.Date=[enter date]
OR tblAllWorkLoggedRecords.Date Is Null

GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date

IF you switch back to the design view (grid view), you will see
tblAllWorkLoggedRecords.Date in the list of field twice. Once with the
Total line set as GROUP BY and once with the Total line set as WHERE.

WHERE filters the records BEFORE the Totals are calculated. HAVING filters
the results after the totals are calculated.
Usually you are better off using WHERE when you are applying criteria
against a field you are grouping.


WMorsberger said:
Here is the query that I have - I have tried all the joins and it doesn't
seem to be working, but then again maybe I'm missing something. It's
still
not showing me all the departments

SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT
GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date)=[enter date]));


John Spencer said:
Do you have a table of Departments? If so include it in your query with
an
outer join. Probably something LIKE the following.

SELECT tblDepartments.Department
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblDepartments LEFT JOIN tblAllWorkLoggedRecords
ON tblDepartments.Department = tblAllWorkLoggedRecords.Department
WHERE tblAllWorkLoggedRecords.Date Between [Enter Start Date: mm/dd/yyyy]
And [Enter End Date: mm/dd/yyyy]
OR tblAllWorkLoggedRecords.Date is Null
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT, tblAllWorkLoggedRecords.Date

WMorsberger said:
I have a report that I am running - Although when run the report I want
it
to
show me the information whether there is a count of zero or not. How
can
I
set this up on my report or my query?

Here is the query that I have my report linked to - Even if the
department
doesn't have a count to show, I still would like it to say zero.

SELECT tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblAllWorkLoggedRecords
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date) Between [Enter Start Date:
mm/dd/yyyy] And [Enter End Date: mm/dd/yyyy]));
Click to expand...
Click to expand...
Click to expand...
 
W

WMorsberger

I used the SQL and no matter what date I put in it is giving me the same
information. Also it is not showing me all the departments. Is there
something that I need to double check is right, or something else that I need
to do?

John Spencer said:
You need to move the HAVING clause to a WHERE clause and also search for
nulls since you are looking at the Many table. Try this modification to
your SQL.
SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT

WHERE tblAllWorkLoggedRecords.Date=[enter date]
OR tblAllWorkLoggedRecords.Date Is Null

GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date

IF you switch back to the design view (grid view), you will see
tblAllWorkLoggedRecords.Date in the list of field twice. Once with the
Total line set as GROUP BY and once with the Total line set as WHERE.

WHERE filters the records BEFORE the Totals are calculated. HAVING filters
the results after the totals are calculated.
Usually you are better off using WHERE when you are applying criteria
against a field you are grouping.


WMorsberger said:
Here is the query that I have - I have tried all the joins and it doesn't
seem to be working, but then again maybe I'm missing something. It's
still
not showing me all the departments

SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT
GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date)=[enter date]));


John Spencer said:
Do you have a table of Departments? If so include it in your query with
an
outer join. Probably something LIKE the following.

SELECT tblDepartments.Department
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblDepartments LEFT JOIN tblAllWorkLoggedRecords
ON tblDepartments.Department = tblAllWorkLoggedRecords.Department
WHERE tblAllWorkLoggedRecords.Date Between [Enter Start Date: mm/dd/yyyy]
And [Enter End Date: mm/dd/yyyy]
OR tblAllWorkLoggedRecords.Date is Null
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT, tblAllWorkLoggedRecords.Date

I have a report that I am running - Although when run the report I want
it
to
show me the information whether there is a count of zero or not. How
can
I
set this up on my report or my query?

Here is the query that I have my report linked to - Even if the
department
doesn't have a count to show, I still would like it to say zero.

SELECT tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblAllWorkLoggedRecords
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date) Between [Enter Start Date:
mm/dd/yyyy] And [Enter End Date: mm/dd/yyyy]));
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Hmmm. Let's try something more complex.

This will require two queries. Query one will get the counts
Parameters [Enter Date] DateTime;
SELECT DEPARTMENT
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblAllWorkLoggedRecords
WHERE tblAllWorkLoggedRecords.Date=CDate([enter date])
GROUP BY DEPARTMENT, tblAllWorkLogged.Date

Save that as qGetWorkCount. See if it works to give you the Department
counts (except for those with no records in tblA). If so, then use the
following as the SQL for your query.

SELECT tblDepartment.Department
, qGetWorkCount.Date
, Nz(qGetWork.CountOfID,0) as CountRecords
FROM tblDepartment LEFT JOIN qGetWorkCount
ON tblDepartment = qGetWorkCount.DEPARTMENT


John Spencer said:
You need to move the HAVING clause to a WHERE clause and also search for
nulls since you are looking at the Many table. Try this modification to
your SQL.
SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT

WHERE tblAllWorkLoggedRecords.Date=[enter date]
OR tblAllWorkLoggedRecords.Date Is Null

GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date

IF you switch back to the design view (grid view), you will see
tblAllWorkLoggedRecords.Date in the list of field twice. Once with the
Total line set as GROUP BY and once with the Total line set as WHERE.

WHERE filters the records BEFORE the Totals are calculated. HAVING
filters the results after the totals are calculated.
Usually you are better off using WHERE when you are applying criteria
against a field you are grouping.


WMorsberger said:
Here is the query that I have - I have tried all the joins and it doesn't
seem to be working, but then again maybe I'm missing something. It's
still
not showing me all the departments

SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT
GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date)=[enter date]));


John Spencer said:
Do you have a table of Departments? If so include it in your query with
an
outer join. Probably something LIKE the following.

SELECT tblDepartments.Department
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblDepartments LEFT JOIN tblAllWorkLoggedRecords
ON tblDepartments.Department = tblAllWorkLoggedRecords.Department
WHERE tblAllWorkLoggedRecords.Date Between [Enter Start Date:
mm/dd/yyyy]
And [Enter End Date: mm/dd/yyyy]
OR tblAllWorkLoggedRecords.Date is Null
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date

I have a report that I am running - Although when run the report I want
it
to
show me the information whether there is a count of zero or not. How
can
I
set this up on my report or my query?

Here is the query that I have my report linked to - Even if the
department
doesn't have a count to show, I still would like it to say zero.

SELECT tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblAllWorkLoggedRecords
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date) Between [Enter Start Date:
mm/dd/yyyy] And [Enter End Date: mm/dd/yyyy]));
Click to expand...
Click to expand...
Click to expand...
 
W

WMorsberger

I tried the first query and I am getting the following error message:

You tried to execute a query that does not include the specified expression
'Date' as part of an aggregate function.

John Spencer said:
Hmmm. Let's try something more complex.

This will require two queries. Query one will get the counts
Parameters [Enter Date] DateTime;
SELECT DEPARTMENT
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblAllWorkLoggedRecords
WHERE tblAllWorkLoggedRecords.Date=CDate([enter date])
GROUP BY DEPARTMENT, tblAllWorkLogged.Date

Save that as qGetWorkCount. See if it works to give you the Department
counts (except for those with no records in tblA). If so, then use the
following as the SQL for your query.

SELECT tblDepartment.Department
, qGetWorkCount.Date
, Nz(qGetWork.CountOfID,0) as CountRecords
FROM tblDepartment LEFT JOIN qGetWorkCount
ON tblDepartment = qGetWorkCount.DEPARTMENT


John Spencer said:
You need to move the HAVING clause to a WHERE clause and also search for
nulls since you are looking at the Many table. Try this modification to
your SQL.
SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT

WHERE tblAllWorkLoggedRecords.Date=[enter date]
OR tblAllWorkLoggedRecords.Date Is Null

GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date

IF you switch back to the design view (grid view), you will see
tblAllWorkLoggedRecords.Date in the list of field twice. Once with the
Total line set as GROUP BY and once with the Total line set as WHERE.

WHERE filters the records BEFORE the Totals are calculated. HAVING
filters the results after the totals are calculated.
Usually you are better off using WHERE when you are applying criteria
against a field you are grouping.


WMorsberger said:
Here is the query that I have - I have tried all the joins and it doesn't
seem to be working, but then again maybe I'm missing something. It's
still
not showing me all the departments

SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT
GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date)=[enter date]));


:

Do you have a table of Departments? If so include it in your query with
an
outer join. Probably something LIKE the following.

SELECT tblDepartments.Department
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblDepartments LEFT JOIN tblAllWorkLoggedRecords
ON tblDepartments.Department = tblAllWorkLoggedRecords.Department
WHERE tblAllWorkLoggedRecords.Date Between [Enter Start Date:
mm/dd/yyyy]
And [Enter End Date: mm/dd/yyyy]
OR tblAllWorkLoggedRecords.Date is Null
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date

I have a report that I am running - Although when run the report I want
it
to
show me the information whether there is a count of zero or not. How
can
I
set this up on my report or my query?

Here is the query that I have my report linked to - Even if the
department
doesn't have a count to show, I still would like it to say zero.

SELECT tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT NAME]
FROM tblAllWorkLoggedRecords
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date) Between [Enter Start Date:
mm/dd/yyyy] And [Enter End Date: mm/dd/yyyy]));
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

That is due to an error in the Group by clause. I mistyped the table name
as tblAllWorkLogged

Parameters [Enter Date] DateTime;
SELECT DEPARTMENT
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblAllWorkLoggedRecords
WHERE tblAllWorkLoggedRecords.Date=CDate([enter date])
GROUP BY DEPARTMENT, tblAllWorkLoggedRecords.Date

WMorsberger said:
I tried the first query and I am getting the following error message:

You tried to execute a query that does not include the specified
expression
'Date' as part of an aggregate function.

John Spencer said:
Hmmm. Let's try something more complex.

This will require two queries. Query one will get the counts
Parameters [Enter Date] DateTime;
SELECT DEPARTMENT
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblAllWorkLoggedRecords
WHERE tblAllWorkLoggedRecords.Date=CDate([enter date])
GROUP BY DEPARTMENT, tblAllWorkLogged.Date

Save that as qGetWorkCount. See if it works to give you the Department
counts (except for those with no records in tblA). If so, then use the
following as the SQL for your query.

SELECT tblDepartment.Department
, qGetWorkCount.Date
, Nz(qGetWork.CountOfID,0) as CountRecords
FROM tblDepartment LEFT JOIN qGetWorkCount
ON tblDepartment = qGetWorkCount.DEPARTMENT


John Spencer said:
You need to move the HAVING clause to a WHERE clause and also search
for
nulls since you are looking at the Many table. Try this modification
to
your SQL.
SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT

WHERE tblAllWorkLoggedRecords.Date=[enter date]
OR tblAllWorkLoggedRecords.Date Is Null

GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date

IF you switch back to the design view (grid view), you will see
tblAllWorkLoggedRecords.Date in the list of field twice. Once with the
Total line set as GROUP BY and once with the Total line set as WHERE.

WHERE filters the records BEFORE the Totals are calculated. HAVING
filters the results after the totals are calculated.
Usually you are better off using WHERE when you are applying criteria
against a field you are grouping.


Here is the query that I have - I have tried all the joins and it
doesn't
seem to be working, but then again maybe I'm missing something. It's
still
not showing me all the departments

SELECT tblDepartment.Department, tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.ID) AS CountOfID
FROM tblDepartment LEFT JOIN tblAllWorkLoggedRecords ON
tblDepartment.Department = tblAllWorkLoggedRecords.DEPARTMENT
GROUP BY tblDepartment.Department, tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date)=[enter date]));


:

Do you have a table of Departments? If so include it in your query
with
an
outer join. Probably something LIKE the following.

SELECT tblDepartments.Department
, tblAllWorkLoggedRecords.Date
, Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT
NAME]
FROM tblDepartments LEFT JOIN tblAllWorkLoggedRecords
ON tblDepartments.Department = tblAllWorkLoggedRecords.Department
WHERE tblAllWorkLoggedRecords.Date Between [Enter Start Date:
mm/dd/yyyy]
And [Enter End Date: mm/dd/yyyy]
OR tblAllWorkLoggedRecords.Date is Null
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date

message
I have a report that I am running - Although when run the report I
want
it
to
show me the information whether there is a count of zero or not.
How
can
I
set this up on my report or my query?

Here is the query that I have my report linked to - Even if the
department
doesn't have a count to show, I still would like it to say zero.

SELECT tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date,
Count(tblAllWorkLoggedRecords.[REPORT NAME]) AS [CountOfREPORT
NAME]
FROM tblAllWorkLoggedRecords
GROUP BY tblAllWorkLoggedRecords.DEPARTMENT,
tblAllWorkLoggedRecords.Date
HAVING (((tblAllWorkLoggedRecords.Date) Between [Enter Start Date:
mm/dd/yyyy] And [Enter End Date: mm/dd/yyyy]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need All In Table To Show 1
How to use/create code in MS Word to calculate age? 0
VBA - exporting serial letters to single documents 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Report Group Footer DLookUp 0
How to add day to PWA 2016 date format? 0
Export Report with Graph to Excel error 2495 1
Weekly Report 2

Top