If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
show all results even when null for each name
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; |
#2
|
|||
|
|||
show all results even when null for each 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. -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "B" wrote in message ... 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; |
#3
|
|||
|
|||
show all results even when null for each name
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; |
#4
|
|||
|
|||
show all results even when null for each name
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" wrote: 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; |
#5
|
|||
|
|||
show all results even when null for each name
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 '================================================= === B wrote: 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" wrote: 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; |
#6
|
|||
|
|||
show all results even when null for each name
Thanks this works great
"John Spencer" wrote: 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 '================================================= === B wrote: 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" wrote: 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; |
#7
|
|||
|
|||
show all results even when null for each name
On 25 mayo, 14:50, B wrote:
Thanks this works great "John Spencer" wrote: 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 '================================================= === B wrote: 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" wrote: 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;- Ocultar texto de la cita - - Mostrar texto de la cita - 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??? |
Thread Tools | |
Display Modes | |
|
|