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
|
|||
|
|||
(Impossible?) Query
I have a query that sometimes is blank (as in, no records, depending on my
criteria) Is there anyway to have all the values be 0 instead of there being no values at all? I need them to be 0 for them to show up on my report (The query is part of another query's expression, it gets the numbers from the 1st query then those #'s show up on the report) I dont think it can be done, what do you guys think? |
#2
|
|||
|
|||
Want to show us the SQL?
-- Chaim "Joel" wrote: I have a query that sometimes is blank (as in, no records, depending on my criteria) Is there anyway to have all the values be 0 instead of there being no values at all? I need them to be 0 for them to show up on my report (The query is part of another query's expression, it gets the numbers from the 1st query then those #'s show up on the report) I dont think it can be done, what do you guys think? |
#3
|
|||
|
|||
On the report, I have 3 columns, Totals to be inspected, Inspected and Issued.
Then I have a total count of each column by area. AREA 1 TTBI Inspected Issued Unit 1 3 2 0 Unit 2 5 3 1 Total 8 5 1 Area 2 exc..... This all works fine untill there is a Structure(s) in a Unit that needs to be inspected but the last inspection dosent fall between the date on the forms. Then the report will look like this, AREA 1 TTBI Inspected Issued Unit 1 3 Unit 2 5 3 1 Total 8 3 1 I need the blank spots to show up as 0's for my report. Here is the SQL's for all 7 queries that make up this report (BTW subIDQ and subIDR are "at runtime" queries so they change a little depending on the user) IDQ This selects all of the structures in a Unit's inspection history [SELECT InspectionHistory.Date, InspectionHistory.NextInspectionDate, InspectionHistory.Inspected, InspectionHistory.Issued, MainUnitData.UnitID, MainAreaData.AreaID, MainUnitData.StructureID FROM (MainAreaData INNER JOIN MainUnitData ON MainAreaData.UnitID = MainUnitData.UnitID) INNER JOIN InspectionHistory ON MainUnitData.StructureID = InspectionHistory.StructureID;] subIDQ This selects the most recent Inspection for each structure in each Unit between the dates entered in the form from IDQ. [SELECT IDRInspectedCounts.UnitID, IDRInspectedCounts.AreaID, IDRInspectedCounts.Inspected, IDRInspectedCounts.Issued, IDRInspectedCounts.StructureID, Max(IDRInspectedCounts.Date) AS MaxOfDate FROM IDRInspectedCounts GROUP BY IDRInspectedCounts.UnitID, IDRInspectedCounts.AreaID, IDRInspectedCounts.Inspected, IDRInspectedCounts.Issued, IDRInspectedCounts.StructureID HAVING (((Max(IDRInspectedCounts.Date)) Between CDate([Forms]![IDRForm].[SDate]) And CDate([Forms]![IDRForm].[EDate])));] SubIDQ2 This counts the inspections by the most recent inspection and by the Unit from subIDQ. [SELECT Sum(IDRsubInspectionCounts.Issued) AS Issued, Abs(Sum(IDRsubInspectionCounts.Inspected)) AS SumOfInspected, IDRsubInspectionCounts.AreaID, IDRsubInspectionCounts.UnitID FROM IDRsubInspectionCounts GROUP BY IDRsubInspectionCounts.AreaID, IDRsubInspectionCounts.UnitID;] SubIDQ3 This counts the inspections by the most recent inspection and by the Area from subIDQ2 [SELECT Sum(IDRsubInspectedCounts2.Issued) AS SumOfIssued, Sum(IDRsubInspectedCounts2.SumOfInspected) AS SumOfSumOfInspected, IDRsubInspectedCounts2.AreaID FROM IDRsubInspectedCounts2 GROUP BY IDRsubInspectedCounts2.AreaID;] IDR This Selects all the structures in each Unit in every Area between the dates entered in the form. [SELECT Max(MainUnitData.InspectionDate) AS MRID, DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[MainUnitData.InspectionDate]) AS NID, MainUnitData.UnitID, MainUnitData.StructureID, MainAreaData.AreaID, MainAreaData.AreaName, MainUnitData.UnitName FROM MainAreaData INNER JOIN (InspectionFrequency INNER JOIN MainUnitData ON InspectionFrequency.InspectionFrequencyID = MainUnitData.InspectionFrequencyID) ON MainAreaData.UnitID = MainUnitData.UnitID GROUP BY DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[MainUnitData.InspectionDate]), MainUnitData.UnitID, MainUnitData.StructureID, MainAreaData.AreaID, MainAreaData.AreaName, MainUnitData.UnitName HAVING (((DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[MainUnitData.InspectionDate])) Between CDate([Forms]![IDRForm].[SDate]) And CDate([Forms]![IDRForm].[EDate])) AND ((MainAreaData.AreaName) In ('Area A')));] subIDR This counts all of the Units selected in IDR [SELECT Count(IDRTotalCounts.NID) AS CountOfNID, IDRTotalCounts.UnitID FROM IDRTotalCounts GROUP BY IDRTotalCounts.UnitID;] subIDR2 This counts all the Area totals selected in IDR [SELECT Count(IDRTotalCounts.StructureID) AS CountOfStructureID, IDRTotalCounts.AreaID FROM IDRTotalCounts GROUP BY IDRTotalCounts.AreaID;] Thanks in advance for any help. "Chaim" wrote: Want to show us the SQL? -- Chaim "Joel" wrote: I have a query that sometimes is blank (as in, no records, depending on my criteria) Is there anyway to have all the values be 0 instead of there being no values at all? I need them to be 0 for them to show up on my report (The query is part of another query's expression, it gets the numbers from the 1st query then those #'s show up on the report) I dont think it can be done, what do you guys think? |
#4
|
|||
|
|||
Can I assume that you've already tried things like enclosing those counts
inside of an iif()? It's inconvenient, admittedly, since these are long expressions, but possibly something like: [SELECT Iif (Sum(IDRInspCts2.Issued) = 0, 0, Sum(IDRInspCts2.Issued)) AS SumOfIssued, Iif (Sum(IDRInspCts2.SumOfInspected) = 0, 0, Sum(IDRInspCts2.SumOfInspected) AS SumOfInspected, IDRInspCts2.AreaID FROM IDRsubInspectedCounts2 AS IDRInspCts2 GROUP BY IDRInspCts2.AreaID;] I find it hard to read in the ng reader, so I inserted the alias, but hopefully that gives you the idea of where I'm going. Good Luck! -- Chaim "Joel" wrote: On the report, I have 3 columns, Totals to be inspected, Inspected and Issued. Then I have a total count of each column by area. AREA 1 TTBI Inspected Issued Unit 1 3 2 0 Unit 2 5 3 1 Total 8 5 1 Area 2 exc..... This all works fine untill there is a Structure(s) in a Unit that needs to be inspected but the last inspection dosent fall between the date on the forms. Then the report will look like this, AREA 1 TTBI Inspected Issued Unit 1 3 Unit 2 5 3 1 Total 8 3 1 I need the blank spots to show up as 0's for my report. Here is the SQL's for all 7 queries that make up this report (BTW subIDQ and subIDR are "at runtime" queries so they change a little depending on the user) IDQ This selects all of the structures in a Unit's inspection history [SELECT InspectionHistory.Date, InspectionHistory.NextInspectionDate, InspectionHistory.Inspected, InspectionHistory.Issued, MainUnitData.UnitID, MainAreaData.AreaID, MainUnitData.StructureID FROM (MainAreaData INNER JOIN MainUnitData ON MainAreaData.UnitID = MainUnitData.UnitID) INNER JOIN InspectionHistory ON MainUnitData.StructureID = InspectionHistory.StructureID;] subIDQ This selects the most recent Inspection for each structure in each Unit between the dates entered in the form from IDQ. [SELECT IDRInspectedCounts.UnitID, IDRInspectedCounts.AreaID, IDRInspectedCounts.Inspected, IDRInspectedCounts.Issued, IDRInspectedCounts.StructureID, Max(IDRInspectedCounts.Date) AS MaxOfDate FROM IDRInspectedCounts GROUP BY IDRInspectedCounts.UnitID, IDRInspectedCounts.AreaID, IDRInspectedCounts.Inspected, IDRInspectedCounts.Issued, IDRInspectedCounts.StructureID HAVING (((Max(IDRInspectedCounts.Date)) Between CDate([Forms]![IDRForm].[SDate]) And CDate([Forms]![IDRForm].[EDate])));] SubIDQ2 This counts the inspections by the most recent inspection and by the Unit from subIDQ. [SELECT Sum(IDRsubInspectionCounts.Issued) AS Issued, Abs(Sum(IDRsubInspectionCounts.Inspected)) AS SumOfInspected, IDRsubInspectionCounts.AreaID, IDRsubInspectionCounts.UnitID FROM IDRsubInspectionCounts GROUP BY IDRsubInspectionCounts.AreaID, IDRsubInspectionCounts.UnitID;] SubIDQ3 This counts the inspections by the most recent inspection and by the Area from subIDQ2 [SELECT Sum(IDRsubInspectedCounts2.Issued) AS SumOfIssued, Sum(IDRsubInspectedCounts2.SumOfInspected) AS SumOfSumOfInspected, IDRsubInspectedCounts2.AreaID FROM IDRsubInspectedCounts2 GROUP BY IDRsubInspectedCounts2.AreaID;] IDR This Selects all the structures in each Unit in every Area between the dates entered in the form. [SELECT Max(MainUnitData.InspectionDate) AS MRID, DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[MainUnitData.InspectionDate]) AS NID, MainUnitData.UnitID, MainUnitData.StructureID, MainAreaData.AreaID, MainAreaData.AreaName, MainUnitData.UnitName FROM MainAreaData INNER JOIN (InspectionFrequency INNER JOIN MainUnitData ON InspectionFrequency.InspectionFrequencyID = MainUnitData.InspectionFrequencyID) ON MainAreaData.UnitID = MainUnitData.UnitID GROUP BY DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[MainUnitData.InspectionDate]), MainUnitData.UnitID, MainUnitData.StructureID, MainAreaData.AreaID, MainAreaData.AreaName, MainUnitData.UnitName HAVING (((DateAdd([InspectionFrequency.PeriodType],[InspectionFrequency.Frequency],[MainUnitData.InspectionDate])) Between CDate([Forms]![IDRForm].[SDate]) And CDate([Forms]![IDRForm].[EDate])) AND ((MainAreaData.AreaName) In ('Area A')));] subIDR This counts all of the Units selected in IDR [SELECT Count(IDRTotalCounts.NID) AS CountOfNID, IDRTotalCounts.UnitID FROM IDRTotalCounts GROUP BY IDRTotalCounts.UnitID;] subIDR2 This counts all the Area totals selected in IDR [SELECT Count(IDRTotalCounts.StructureID) AS CountOfStructureID, IDRTotalCounts.AreaID FROM IDRTotalCounts GROUP BY IDRTotalCounts.AreaID;] Thanks in advance for any help. "Chaim" wrote: Want to show us the SQL? -- Chaim "Joel" wrote: I have a query that sometimes is blank (as in, no records, depending on my criteria) Is there anyway to have all the values be 0 instead of there being no values at all? I need them to be 0 for them to show up on my report (The query is part of another query's expression, it gets the numbers from the 1st query then those #'s show up on the report) I dont think it can be done, what do you guys think? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
Parameter query - nested queries | laura | Running & Setting Up Queries | 3 | February 10th, 2005 04:09 PM |
Moving from xBase/Clipper | [email protected] | New Users | 1 | February 3rd, 2005 07:25 PM |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |