show all results even when null for each name

  • Thread starter Thread starter B
  • Start date Start date
B

B

How can i display all names in the table even when there are no records that
meet the parameter? When there are no records i want to show 0 not null ?

Thanks

SELECT [In tbl].name, [2007 tbl].ID2007, [2007 tbl].[HOUSE NUMBER], [tbl
March].STREET, [tbl March].UNIT, [tbl March].[ZIP CODE], [tbl March].[Cc
filed date]
FROM [2007 tbl ]
WHERE ((([2000 tbl].[Cc filed date])>=[Forms]![Report Access
Form]![StartDate] And ([2007 tbl].[Cc filed date])<=[Forms]![Report Access
Form]![EndDate]))
ORDER BY [In tbl].name;
 
How are you using this query? This query will not return a NULL nor a 0 for
any set of records, regardless of the parameter values.

Are you using a DLookup function to return some information from this query?
More details, please.
 
Well, first post the actual query. What you have posted would never
work. Hint on the menubar View: SQL to see the real SQL.

You refer to [In Tbl], [2007 tbl], [2007 tbl ], [2000 tbl], and [tbl
March] in the posted SQL but have only [2007 tbl ] in the FROM clause.

Next, if there are no records returned by a query then you can't display
zero.

If you mean you want the records in a specific table from the tables you
used in the query (i.e., [2007 tbl]) returned then you would need to use
an outer join (either a LEFT JOIN or a RIGHT JOIN) to various other
tables you want to use in your query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I'm sorry for the confusion, below is the actual query. I will try to explain
better


SELECT [2007 March].InspectorID, Count([2007 March].ID2007) AS CountOfID2007
FROM [2007 March]
WHERE ((([2007 March].[DATE INSP DONE])>=[Forms]![Report Access
Form]![StartDate] And ([2007 March].[DATE INSP DONE])<=[Forms]![Report Access
Form]![EndDate]))
GROUP BY [2007 March].InspectorID;

The results are below;

InspectorID Count
Inspector 1 1
Inspector 2 12
Inspector 3 3
Inspector 4 4

I would like show all inspectors even when null value exist for the date
parameter.

John Spencer said:
Well, first post the actual query. What you have posted would never
work. Hint on the menubar View: SQL to see the real SQL.

You refer to [In Tbl], [2007 tbl], [2007 tbl ], [2000 tbl], and [tbl
March] in the posted SQL but have only [2007 tbl ] in the FROM clause.

Next, if there are no records returned by a query then you can't display
zero.

If you mean you want the records in a specific table from the tables you
used in the query (i.e., [2007 tbl]) returned then you would need to use
an outer join (either a LEFT JOIN or a RIGHT JOIN) to various other
tables you want to use in your query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

How can i display all names in the table even when there are no records that
meet the parameter? When there are no records i want to show 0 not null ?

Thanks

SELECT [In tbl].name, [2007 tbl].ID2007, [2007 tbl].[HOUSE NUMBER], [tbl
March].STREET, [tbl March].UNIT, [tbl March].[ZIP CODE], [tbl March].[Cc
filed date]
FROM [2007 tbl ]
WHERE ((([2000 tbl].[Cc filed date])>=[Forms]![Report Access
Form]![StartDate] And ([2007 tbl].[Cc filed date])<=[Forms]![Report Access
Form]![EndDate]))
ORDER BY [In tbl].name;
Click to expand...
Click to expand...
 
Do you have a table that contains all the inspectors? You should have
in all probability.

Assumption: Table Named Inspectors with one record for each inspector.

You will need two queries.
The first query is the one you have now.

SELECT Inspectors.InspectorID, NZ(CountOfID2007,0) as TheCount
FROM Inspectors LEFT JOIN [Your Current Query]
ON Inspectors.InspectorID = [Your Current Query].InspectorID

Basically in design view
-- Add the Inspectors table and the existing query
-- Drag for InspectorID to InspectorID to set up the join
-- Double-click on the join line and select the option that shows ALL
records in the Inspector table and only matching in the query
-- Add the InspectorID field from the Inspectors table
-- Add the count from your current query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I'm sorry for the confusion, below is the actual query. I will try to explain
better


SELECT [2007 March].InspectorID, Count([2007 March].ID2007) AS CountOfID2007
FROM [2007 March]
WHERE ((([2007 March].[DATE INSP DONE])>=[Forms]![Report Access
Form]![StartDate] And ([2007 March].[DATE INSP DONE])<=[Forms]![Report Access
Form]![EndDate]))
GROUP BY [2007 March].InspectorID;

The results are below;

InspectorID Count
Inspector 1 1
Inspector 2 12
Inspector 3 3
Inspector 4 4

I would like show all inspectors even when null value exist for the date
parameter.

John Spencer said:
Well, first post the actual query. What you have posted would never
work. Hint on the menubar View: SQL to see the real SQL.

You refer to [In Tbl], [2007 tbl], [2007 tbl ], [2000 tbl], and [tbl
March] in the posted SQL but have only [2007 tbl ] in the FROM clause.

Next, if there are no records returned by a query then you can't display
zero.

If you mean you want the records in a specific table from the tables you
used in the query (i.e., [2007 tbl]) returned then you would need to use
an outer join (either a LEFT JOIN or a RIGHT JOIN) to various other
tables you want to use in your query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

How can i display all names in the table even when there are no records that
meet the parameter? When there are no records i want to show 0 not null ?

Thanks

SELECT [In tbl].name, [2007 tbl].ID2007, [2007 tbl].[HOUSE NUMBER], [tbl
March].STREET, [tbl March].UNIT, [tbl March].[ZIP CODE], [tbl March].[Cc
filed date]
FROM [2007 tbl ]
WHERE ((([2000 tbl].[Cc filed date])>=[Forms]![Report Access
Form]![StartDate] And ([2007 tbl].[Cc filed date])<=[Forms]![Report Access
Form]![EndDate]))
ORDER BY [In tbl].name;
Click to expand...
Click to expand...
Click to expand...
 
Thanks this works great

John Spencer said:
Do you have a table that contains all the inspectors? You should have
in all probability.

Assumption: Table Named Inspectors with one record for each inspector.

You will need two queries.
The first query is the one you have now.

SELECT Inspectors.InspectorID, NZ(CountOfID2007,0) as TheCount
FROM Inspectors LEFT JOIN [Your Current Query]
ON Inspectors.InspectorID = [Your Current Query].InspectorID

Basically in design view
-- Add the Inspectors table and the existing query
-- Drag for InspectorID to InspectorID to set up the join
-- Double-click on the join line and select the option that shows ALL
records in the Inspector table and only matching in the query
-- Add the InspectorID field from the Inspectors table
-- Add the count from your current query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I'm sorry for the confusion, below is the actual query. I will try to explain
better


SELECT [2007 March].InspectorID, Count([2007 March].ID2007) AS CountOfID2007
FROM [2007 March]
WHERE ((([2007 March].[DATE INSP DONE])>=[Forms]![Report Access
Form]![StartDate] And ([2007 March].[DATE INSP DONE])<=[Forms]![Report Access
Form]![EndDate]))
GROUP BY [2007 March].InspectorID;

The results are below;

InspectorID Count
Inspector 1 1
Inspector 2 12
Inspector 3 3
Inspector 4 4

I would like show all inspectors even when null value exist for the date
parameter.

John Spencer said:
Well, first post the actual query. What you have posted would never
work. Hint on the menubar View: SQL to see the real SQL.

You refer to [In Tbl], [2007 tbl], [2007 tbl ], [2000 tbl], and [tbl
March] in the posted SQL but have only [2007 tbl ] in the FROM clause.

Next, if there are no records returned by a query then you can't display
zero.

If you mean you want the records in a specific table from the tables you
used in the query (i.e., [2007 tbl]) returned then you would need to use
an outer join (either a LEFT JOIN or a RIGHT JOIN) to various other
tables you want to use in your query.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


B wrote:
How can i display all names in the table even when there are no records that
meet the parameter? When there are no records i want to show 0 not null ?

Thanks

SELECT [In tbl].name, [2007 tbl].ID2007, [2007 tbl].[HOUSE NUMBER], [tbl
March].STREET, [tbl March].UNIT, [tbl March].[ZIP CODE], [tbl March].[Cc
filed date]
FROM [2007 tbl ]
WHERE ((([2000 tbl].[Cc filed date])>=[Forms]![Report Access
Form]![StartDate] And ([2007 tbl].[Cc filed date])<=[Forms]![Report Access
Form]![EndDate]))
ORDER BY [In tbl].name;
Click to expand...
Click to expand...
Click to expand...
 
Thanks this works great



John Spencer said:
Do you have a table that contains all the inspectors?  You should have
in all probability.
Click to expand...
Assumption: Table Named Inspectors with one record for each inspector.
Click to expand...
You will need two queries.
The first query is the one you have now.
Click to expand...
SELECT Inspectors.InspectorID, NZ(CountOfID2007,0) as TheCount
FROM Inspectors LEFT JOIN [Your Current Query]
ON Inspectors.InspectorID = [Your Current Query].InspectorID
Click to expand...
Basically in design view
-- Add the Inspectors table and the existing query
-- Drag for InspectorID to InspectorID to set up the join
-- Double-click on the join line and select the option that shows ALL
records in the Inspector table and only matching in the query
-- Add the InspectorID field from the Inspectors table
-- Add the count from your current query.
Click to expand...
'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================
Click to expand...
B said:
I'm sorry for the confusion, below is the actual query. I will try toexplain
better
SELECT [2007 March].InspectorID, Count([2007 March].ID2007) AS CountOfID2007
FROM [2007 March]
WHERE ((([2007 March].[DATE INSP DONE])>=[Forms]![Report Access
Form]![StartDate] And ([2007 March].[DATE INSP DONE])<=[Forms]![Report Access
Form]![EndDate]))
GROUP BY [2007 March].InspectorID;
The results are below;
InspectorID   Count
Inspector 1   1
Inspector 2   12
Inspector 3   3
Inspector 4   4
I would like show all inspectors even when null value exist for the date
parameter.
:
Well, first post the actual query.  What you have posted would never
work. Hint on the menubar View: SQL to see the real SQL.
You refer to [In Tbl], [2007 tbl], [2007 tbl ], [2000 tbl], and [tbl
March] in the posted SQL but have only [2007 tbl ] in the FROM clause.
Next, if there are no records returned by a query then you can't display
zero.
If you mean you want the records in a specific table from the tablesyou
used in the query (i.e., [2007 tbl]) returned then you would need touse
an outer join (either a LEFT JOIN or a RIGHT JOIN) to various other
tables you want to use in your query.
'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================
B wrote:
How can i display all names in the table even when there are no records that
meet the parameter? When there are no records i want to show 0 not null ?
Thanks
SELECT [In tbl].name, [2007 tbl].ID2007, [2007 tbl].[HOUSE NUMBER],[tbl
March].STREET, [tbl March].UNIT, [tbl March].[ZIP CODE], [tbl March].[Cc
filed date]
FROM [2007 tbl ]
WHERE ((([2000 tbl].[Cc filed date])>=[Forms]![Report Access
Form]![StartDate] And ([2007 tbl].[Cc filed date])<=[Forms]![Report Access
Form]![EndDate]))
ORDER BY [In tbl].name;- Ocultar texto de la cita -
Click to expand...
Click to expand...

- Mostrar texto de la cita -
Click to expand...

Hi,

I'm having a similar dilema only that I have 1 more variable....for
example I'm trying to get the number of records created by a user on a
specific date but I want that all dates to be displayed for all users
even when there are no recods (since all users wont show activity in
all dates).

I have a query that shows Originator Login, date, and number of
records created
another table containing all the employee IDs
and another table containing dates.

So Far I've been able to get this results

SELECT [TABLE_Cases Created].[Originator Login Name], DATES.DATES,
Count([TABLE_Cases Created].[Case Id]) AS [CountOfCase Id]
FROM DATES LEFT JOIN [TABLE_Cases Created] ON DATES.DATES =
[TABLE_Cases Created].[Date Original]
GROUP BY [TABLE_Cases Created].[Originator Login Name], DATES.DATES
ORDER BY DATES.DATES DESC;

Employee Date #records created

Employee1 1/1/09 1
Employee1 1/2/09 3
1/3/09
Employee1 1/4/09 5


And what I really want to get is the following

Employee Date #records created

Employee1 1/1/09 1
Employee1 1/2/09 3
Employee1 1/3/09 0
Employee1 1/4/09 5

I need the blank record to show th employee ID with a results of
records created of 0 or blank value

Any sugestions???
 
Back
Top