(Impossible?) Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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 said:
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 said:
Want to show us the SQL?
Click to expand...
Click to expand...
 
Back
Top