How to find out how many fields from a specific list of fields are empty

  • Thread starter Thread starter DawnTreader
  • Start date Start date
D

DawnTreader

Hello

i have a few tables that i need to know the completeness of records
based on certain fields. i am wondering if there is a way to query
each of those tables easily based on a list of field names in a table
to see if the fields are null or default data.

i am thinking i will need some code, but i am wondering if any one has
a simple query solution to this.

thanks in advance.
 
It happens that DawnTreader formulated :
Hello

i have a few tables that i need to know the completeness of records
based on certain fields. i am wondering if there is a way to query
each of those tables easily based on a list of field names in a table
to see if the fields are null or default data.

i am thinking i will need some code, but i am wondering if any one has
a simple query solution to this.

thanks in advance.
Click to expand...

This is Easy - Peasy. Hint! Nulls Propagate! So...

SELECT PrimaryID, [Column1]+[Column2]+[Column3] AS MissingFields
FROM YourTable
Where ([Column1]+[Column2]+[Column3]) is Null

Show all of the Rows that are missing data in one or more columns.

Note I am using the + not the &.

Rdub
 
It happens that DawnTreader formulated :
Hello
Click to expand...
i have a few tables that i need to know the completeness of records
based on certain fields. i am wondering if there is a way to query
each of those tables easily based on a list of field names in a table
to see if the fields are null or default data.
Click to expand...
i am thinking i will need some code, but i am wondering if any one has
a simple query solution to this.
Click to expand...
thanks in advance.
Click to expand...

This is Easy - Peasy.  Hint!  Nulls Propagate!  So...

SELECT PrimaryID, [Column1]+[Column2]+[Column3] AS MissingFields
FROM YourTable
Where ([Column1]+[Column2]+[Column3]) is Null

Show all of the Rows that are missing data in one or more columns.

Note I am using the + not the &.

Rdub
Click to expand...

hmmm.

if, as you say, nulls propagate then if only one field is null your
missingfields will turn null. i need to know how many are null.
something akin to count if null. or count if not null. i want to know
how much out of a 100% a record is complete or missing. your
suggestion gives me an idea, but i need something more.

thanks for the reply. will post what i come up with.
 
if, as you say, nulls propagate then if only one field is null your
missingfields will turn null. i need to know how many are null.
something akin to count if null. or count if not null. i want to know
how much out of a 100% a record is complete or missing. your
suggestion gives me an idea, but i need something more.
Click to expand...

Since TRUE is represented by -1 and FALSE by 0, you could use

NumberNull: -(IsNull([FieldA]) + IsNull([FieldB]) + IsNull([FieldC] + ...)

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
DawnTreader formulated on Monday :
It happens that DawnTreader formulated :
Hello
Click to expand...
i have a few tables that i need to know the completeness of records
based on certain fields. i am wondering if there is a way to query
each of those tables easily based on a list of field names in a table
to see if the fields are null or default data.
i am thinking i will need some code, but i am wondering if any one has
a simple query solution to this.
Click to expand...
thanks in advance.
Click to expand...

This is Easy - Peasy.  Hint!  Nulls Propagate!  So...

SELECT PrimaryID, [Column1]+[Column2]+[Column3] AS MissingFields
FROM YourTable
Where ([Column1]+[Column2]+[Column3]) is Null

Show all of the Rows that are missing data in one or more columns.

Note I am using the + not the &.

Rdub
Click to expand...

hmmm.

if, as you say, nulls propagate then if only one field is null your
missingfields will turn null. i need to know how many are null.
something akin to count if null. or count if not null. i want to know
how much out of a 100% a record is complete or missing. your
suggestion gives me an idea, but i need something more.

thanks for the reply. will post what i come up with.
Click to expand...

How about:

Select * From YourTable Where
PrimaryID in( SELECT PrimaryID FROM YourTable Where
([Column1]+[Column2]+[Column3]) is Null)

Returns all of the Rows where at least one of the Columns was null

Rdub
 
Ron said:
DawnTreader formulated on Monday :
It happens that DawnTreader formulated :

Hello

i have a few tables that i need to know the completeness of records
based on certain fields. i am wondering if there is a way to query
each of those tables easily based on a list of field names in a
table to see if the fields are null or default data.
i am thinking i will need some code, but i am wondering if any one
has a simple query solution to this.

thanks in advance.

This is Easy - Peasy. Hint! Nulls Propagate! So...

SELECT PrimaryID, [Column1]+[Column2]+[Column3] AS MissingFields
FROM YourTable
Where ([Column1]+[Column2]+[Column3]) is Null
Click to expand...
Click to expand...
Click to expand...

Good but, as the OP says, it doesn't really address the question.
Returns all of the Rows where at least one of the Columns was null
Click to expand...

:chuckle: What is the difference between these two statements? Your secod
solution answers the same incorrect question that your first solution did.

See the subject. Read it. Carefully. What does the OP want to count? :-)
 
DawnTreader formulated on Monday :




It happens that DawnTreader formulated :
Hello
i have a few tables that i need to know the completeness of records
based on certain fields. i am wondering if there is a way to query
each of those tables easily based on a list of field names in a table
to see if the fields are null or default data.
i am thinking i will need some code, but i am wondering if any one has
a simple query solution to this.
thanks in advance.
This is Easy - Peasy.  Hint!  Nulls Propagate!  So...
SELECT PrimaryID, [Column1]+[Column2]+[Column3] AS MissingFields
FROM YourTable
Where ([Column1]+[Column2]+[Column3]) is Null
Show all of the Rows that are missing data in one or more columns.
Note I am using the + not the &.
Rdub
Click to expand...
hmmm.
Click to expand...

if, as you say, nulls propagate then if only one field is null your
missingfields will turn null. i need to know how many are null.
something akin to count if null. or count if not null. i want to know
how much out of a 100% a record is complete or missing. your
suggestion gives me an idea, but i need something more.
Click to expand...
thanks for the reply. will post what i come up with.
Click to expand...

How about:

Select * From YourTable Where
PrimaryID in( SELECT PrimaryID FROM YourTable Where
([Column1]+[Column2]+[Column3]) is Null)

Returns all of the Rows where at least one of the Columns was null

Rdub- Hide quoted text -

- Show quoted text -
Click to expand...

Here is what i am using on one table:

SELECT tblProductList.ProductID, tblProductList.ProductTypeID,
tblProductList.SerialNumber, IIf([CustomerOrder] Is Null,0,1) AS CO,
IIf([DateShipped] Is Null,0,1) AS DS, IIf([DateCommissioned] Is Null,
0,1) AS DC, IIf([WarrantyMonths] Is Null,0,1) AS WM,
IIf([WarrantyShippedMonths] Is Null,0,1) AS WSM,
IIf([ProductTypeID]="Compressor",IIf([WarrantyHours] Is Null,0,1),1)
AS WH, IIf([WorkOrder] Is Null,0,1) AS WO, IIf([SerialNumber] Is Null,
0,1)+IIf([CustomerOrder] Is Null,0,1)+IIf([DateShipped] Is Null,
0,1)+IIf([DateCommissioned] Is Null,0,1)+IIf([WarrantyMonths] Is Null,
0,1)+IIf([WarrantyShippedMonths] Is Null,
0,1)+IIf([ProductTypeID]="Compressor",IIf([WarrantyHours] Is Null,0,1),
1)+IIf([WorkOrder] Is Null,0,1)+IIf([ModelNumber] Is Null,0,1) AS
ProductCompleteness
FROM tblProductList
WHERE (((tblProductList.DateDeleted) Is Null))
GROUP BY tblProductList.ProductID, tblProductList.ProductTypeID,
tblProductList.SerialNumber, IIf([CustomerOrder] Is Null,0,1),
IIf([DateShipped] Is Null,0,1), IIf([DateCommissioned] Is Null,0,1),
IIf([WarrantyMonths] Is Null,0,1), IIf([WarrantyShippedMonths] Is Null,
0,1), IIf([ProductTypeID]="Compressor",IIf([WarrantyHours] Is Null,
0,1),1), IIf([WorkOrder] Is Null,0,1), IIf([SerialNumber] Is Null,
0,1)+IIf([CustomerOrder] Is Null,0,1)+IIf([DateShipped] Is Null,
0,1)+IIf([DateCommissioned] Is Null,0,1)+IIf([WarrantyMonths] Is Null,
0,1)+IIf([WarrantyShippedMonths] Is Null,
0,1)+IIf([ProductTypeID]="Compressor",IIf([WarrantyHours] Is Null,0,1),
1)+IIf([WorkOrder] Is Null,0,1)+IIf([ModelNumber] Is Null,0,1)
ORDER BY tblProductList.SerialNumber;

but here is what i have going on another table:

SELECT subdatatblCompressor.CompressorID,
subdatatblCompressor.ProductID, subdatatblCompressor.ProductTypeID,
IIf([CBASubAssemblyPartNumber] Is Null,0,1) AS CBASAPN,
IIf([CompressorPartNumber] Is Null,0,1) AS CPN, IIf([CompressorTypeID]
Is Null,0,IIf([CompressorTypeID]=18,0,1)) AS CTID, IIf([BlockTypeID]
Is Null,0,IIf([BlockTypeID]=4,0,1)) AS BTID, IIf([CrankshaftTypeID] Is
Null,0,IIf([CrankshaftTypeID]=3,0,1)) AS CSTID,
IIf([InletGasTemp]>0,1,0) AS IGT, IIf([InletPressure]>0,1,0) AS IP,
IIf([FlowRate]>0,1,0) AS FR, IIf([DischargePressure]>0,1,0) AS DP,
IIf([DischargeTemp]>0,1,0) AS DT, IIf([LowSuctionAlarm]>0,1,0) AS LSA,
IIf([HighSuctionAlarm]>0,1,0) AS HSA, IIf([StartPressure]>0,1,0) AS
SP, IIf([StopPressure]>0,1,0) AS STPP, IIf([CoolingMethod] Is Null,
0,IIf([CoolingMethod]="Default",0,1)) AS CM,
IIf([Stages]>=1,IIf([1stStageConfig] Is Null,0,1),0) AS FSC,
IIf([Stages]>=2,IIf([2ndStageConfig] Is Null,0,1),0) AS SSC,
IIf([Stages]>=3,IIf([3rdStageConfig] Is Null,0,1),0) AS TSC,
IIf([Stages]>=4,IIf([4thStageConfig] Is Null,0,1),0) AS FthSC,
IIf([Stages]>=5,IIf([5thStageConfig] Is Null,0,1),0) AS FFthSC,
IIf([PriorityPanelTypeID] Is Null,0,IIf([PriorityPanelTypeID]=1,0,1))
AS PPTID, IIf([PLCTypeID] Is Null,0,IIf([PLCTypeID]=5,0,1)) AS PLCTID,
IIf([ScreenTypeID] Is Null,0,IIf([ScreenTypeID]=5,0,1)) AS STID,
IIf([BlockQty] Is Null,0,IIf([BlockQty]=0,0,1)) AS BQTY,
IIf([PLCPanelSerial] Is Null,0,1) AS PLCPS,
IIf([MotorControlPanelSerial] Is Null,0,1) AS MCCS,
IIf([PriorityControlPanelSerial] Is Null,0,1) AS PCPS,
IIf([CoolerAssemblyPartNumber] Is Null,0,1) AS CAPN,
IIf([CoolerAssemblySerial] Is Null,0,1) AS CAS,
IIf([Stages]>=1,IIf([1stStagePSI] Is Null,0,IIf([1stStagePSI]=0,0,1)),
0) AS FSPSI, IIf([Stages]>=2,IIf([2ndStagePSI] Is Null,
0,IIf([2ndStagePSI]=0,0,1)),0) AS SSPSI,
IIf([Stages]>=3,IIf([3rdStagePSI] Is Null,0,IIf([3rdStagePSI]=0,0,1)),
0) AS TSPSI, IIf([Stages]>=4,IIf([4thStagePSI] Is Null,
0,IIf([4thStagePSI]=0,0,1)),0) AS FthSPSI,
IIf([Stages]>=5,IIf([5thStagePSI] Is Null,0,IIf([5thStagePSI]=0,0,1)),
0) AS FFthSPSI, IIf([Stages]>=1,IIf([1stStageTemp] Is Null,
0,IIf([1stStageTemp]=0,0,1)),0) AS FST,
IIf([Stages]>=2,IIf([2ndStageTemp] Is Null,
0,IIf([2ndStageTemp]=0,0,1)),0) AS SST,
IIf([Stages]>=3,IIf([3rdStageTemp] Is Null,
0,IIf([3rdStageTemp]=0,0,1)),0) AS TST,
IIf([Stages]>=4,IIf([4thStageTemp] Is Null,
0,IIf([4thStageTemp]=0,0,1)),0) AS FthST,
IIf([Stages]>=5,IIf([5thStageTemp] Is Null,
0,IIf([5thStageTemp]=0,0,1)),0) AS FFthST, IIf([BlockRPM] Is Null,
0,IIf([BlockRPM]=0,0,1)) AS BRPM, IIf([DriveTypeID] Is Null,
0,IIf([DriveTypeID]=1,0,1)) AS DTID, IIf([GasEngineRPM] Is Null,
0,IIf([GasEngineRPM]=0,0,1)) AS GERPM, IIf([GasEngineSerialNumber] Is
Null,0,1) AS GESN, IIf([EngineArrangementNumber] Is Null,0,1) AS EAN,
IIf([GasEngineModelNumber] Is Null,0,1) AS GEMN,
IIf([EngineStarterLubeLevel] Is Null,0,1) AS ESLL, IIf([EngineOilPSI]
Is Null,0,IIf([EngineOilPSI]=0,0,1)) AS EOPSI,
IIf([EngineWaterJacketTempurature] Is Null,
0,IIf([EngineWaterJacketTempurature]=0,0,1)) AS EWJT,
IIf([EngineWaterJacketCoolant] Is Null,
0,IIf([EngineWaterJacketCoolant]=0,0,1)) AS EWJC,
IIf([GasEngineHorsePower] Is Null,0,IIf([GasEngineHorsePower]=0,0,1))
AS GEHP, IIf([ElectricRPM] Is Null,0,IIf([ElectricRPM]=0,0,1)) AS
ERPM, IIf([ElectricMotorSerialNumber] Is Null,0,1) AS EMSN,
IIf([ElectricMotorHorsePower] Is Null,0,1) AS EMHP,
subdatatblCompressor.ThermalProtectionID,
subdatatblCompressor.Voltage, subdatatblCompressor.Hz,
subdatatblCompressor.CrankcaseStyleID, subdatatblCompressor.SealType,
subdatatblCompressor.OilHeater, subdatatblCompressor.TKNumber,
subdatatblCompressor.Stages, subdatatblCompressor.GasCoolingSystem,
subdatatblCompressor.MinFlowCapacity,
subdatatblCompressor.MaxFlowCapacity,
subdatatblCompressor.InletPulsationBottles,
subdatatblCompressor.DischargePulsationBottles,
subdatatblCompressor.FlowRateGGE,
subdatatblCompressor.RemoteMonitoring,
subdatatblCompressor.FlowRateKgMin
FROM subdatatblCompressor;

and i havent finished yet. in the end i need one column that will
calculate the total as i did with the first example.

is there a way to use code to do this?
 
DawnTreader formulated on Monday :
It happens that DawnTreader formulated :
Hello
i have a few tables that i need to know the completeness of records
based on certain fields. i am wondering if there is a way to query
each of those tables easily based on a list of field names in a table
to see if the fields are null or default data.
i am thinking i will need some code, but i am wondering if any one has
a simple query solution to this.
thanks in advance.
This is Easy - Peasy.  Hint!  Nulls Propagate!  So...
SELECT PrimaryID, [Column1]+[Column2]+[Column3] AS MissingFields
FROM YourTable
Where ([Column1]+[Column2]+[Column3]) is Null
Show all of the Rows that are missing data in one or more columns.
Note I am using the + not the &.
Rdub
hmmm.
if, as you say, nulls propagate then if only one field is null your
missingfields will turn null. i need to know how many are null.
something akin to count if null. or count if not null. i want to know
how much out of a 100% a record is complete or missing. your
suggestion gives me an idea, but i need something more.
thanks for the reply. will post what i come up with.
Click to expand...
Click to expand...
How about:
Click to expand...
Select * From YourTable Where
PrimaryID in( SELECT PrimaryID FROM YourTable Where
([Column1]+[Column2]+[Column3]) is Null)
Click to expand...
Returns all of the Rows where at least one of the Columns was null
Click to expand...
Rdub- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Here is what i am using on one table:

SELECT tblProductList.ProductID, tblProductList.ProductTypeID,
tblProductList.SerialNumber, IIf([CustomerOrder] Is Null,0,1) AS CO,
IIf([DateShipped] Is Null,0,1) AS DS, IIf([DateCommissioned] Is Null,
0,1) AS DC, IIf([WarrantyMonths] Is Null,0,1) AS WM,
IIf([WarrantyShippedMonths] Is Null,0,1) AS WSM,
IIf([ProductTypeID]="Compressor",IIf([WarrantyHours] Is Null,0,1),1)
AS WH, IIf([WorkOrder] Is Null,0,1) AS WO, IIf([SerialNumber] Is Null,
0,1)+IIf([CustomerOrder] Is Null,0,1)+IIf([DateShipped] Is Null,
0,1)+IIf([DateCommissioned] Is Null,0,1)+IIf([WarrantyMonths] Is Null,
0,1)+IIf([WarrantyShippedMonths] Is Null,
0,1)+IIf([ProductTypeID]="Compressor",IIf([WarrantyHours] Is Null,0,1),
1)+IIf([WorkOrder] Is Null,0,1)+IIf([ModelNumber] Is Null,0,1) AS
ProductCompleteness
FROM tblProductList
WHERE (((tblProductList.DateDeleted) Is Null))
GROUP BY tblProductList.ProductID, tblProductList.ProductTypeID,
tblProductList.SerialNumber, IIf([CustomerOrder] Is Null,0,1),
IIf([DateShipped] Is Null,0,1), IIf([DateCommissioned] Is Null,0,1),
IIf([WarrantyMonths] Is Null,0,1), IIf([WarrantyShippedMonths] Is Null,
0,1), IIf([ProductTypeID]="Compressor",IIf([WarrantyHours] Is Null,
0,1),1), IIf([WorkOrder] Is Null,0,1), IIf([SerialNumber] Is Null,
0,1)+IIf([CustomerOrder] Is Null,0,1)+IIf([DateShipped] Is Null,
0,1)+IIf([DateCommissioned] Is Null,0,1)+IIf([WarrantyMonths] Is Null,
0,1)+IIf([WarrantyShippedMonths] Is Null,
0,1)+IIf([ProductTypeID]="Compressor",IIf([WarrantyHours] Is Null,0,1),
1)+IIf([WorkOrder] Is Null,0,1)+IIf([ModelNumber] Is Null,0,1)
ORDER BY tblProductList.SerialNumber;

but here is what i have going on another table:

SELECT subdatatblCompressor.CompressorID,
subdatatblCompressor.ProductID, subdatatblCompressor.ProductTypeID,
IIf([CBASubAssemblyPartNumber] Is Null,0,1) AS CBASAPN,
IIf([CompressorPartNumber] Is Null,0,1) AS CPN, IIf([CompressorTypeID]
Is Null,0,IIf([CompressorTypeID]=18,0,1)) AS CTID, IIf([BlockTypeID]
Is Null,0,IIf([BlockTypeID]=4,0,1)) AS BTID, IIf([CrankshaftTypeID] Is
Null,0,IIf([CrankshaftTypeID]=3,0,1)) AS CSTID,
IIf([InletGasTemp]>0,1,0) AS IGT, IIf([InletPressure]>0,1,0) AS IP,
IIf([FlowRate]>0,1,0) AS FR, IIf([DischargePressure]>0,1,0) AS DP,
IIf([DischargeTemp]>0,1,0) AS DT, IIf([LowSuctionAlarm]>0,1,0) AS LSA,
IIf([HighSuctionAlarm]>0,1,0) AS HSA, IIf([StartPressure]>0,1,0) AS
SP, IIf([StopPressure]>0,1,0) AS STPP, IIf([CoolingMethod] Is Null,
0,IIf([CoolingMethod]="Default",0,1)) AS CM,
IIf([Stages]>=1,IIf([1stStageConfig] Is Null,0,1),0) AS FSC,
IIf([Stages]>=2,IIf([2ndStageConfig] Is Null,0,1),0) AS SSC,
IIf([Stages]>=3,IIf([3rdStageConfig] Is Null,0,1),0) AS TSC,
IIf([Stages]>=4,IIf([4thStageConfig] Is Null,0,1),0) AS FthSC,
IIf([Stages]>=5,IIf([5thStageConfig] Is Null,0,1),0) AS FFthSC,
IIf([PriorityPanelTypeID] Is Null,0,IIf([PriorityPanelTypeID]=1,0,1))
AS PPTID, IIf([PLCTypeID] Is Null,0,IIf([PLCTypeID]=5,0,1)) AS PLCTID,
IIf([ScreenTypeID] Is Null,0,IIf([ScreenTypeID]=5,0,1)) AS STID,
IIf([BlockQty] Is Null,0,IIf([BlockQty]=0,0,1)) AS BQTY,
IIf([PLCPanelSerial] Is Null,0,1) AS PLCPS,
IIf([MotorControlPanelSerial] Is Null,0,1) AS MCCS,
IIf([PriorityControlPanelSerial] Is Null,0,1) AS PCPS,
IIf([CoolerAssemblyPartNumber] Is Null,0,1) AS CAPN,
IIf([CoolerAssemblySerial] Is Null,0,1) AS CAS,
IIf([Stages]>=1,IIf([1stStagePSI] Is Null,0,IIf([1stStagePSI]=0,0,1)),
0) AS FSPSI, IIf([Stages]>=2,IIf([2ndStagePSI] Is Null,
0,IIf([2ndStagePSI]=0,0,1)),0) AS SSPSI,
IIf([Stages]>=3,IIf([3rdStagePSI] Is Null,0,IIf([3rdStagePSI]=0,0,1)),
0) AS TSPSI, IIf([Stages]>=4,IIf([4thStagePSI] Is Null,
0,IIf([4thStagePSI]=0,0,1)),0) AS FthSPSI,
IIf([Stages]>=5,IIf([5thStagePSI] Is Null,0,IIf([5thStagePSI]=0,0,1)),
0) AS FFthSPSI, IIf([Stages]>=1,IIf([1stStageTemp] Is Null,
0,IIf([1stStageTemp]=0,0,1)),0) AS FST,
IIf([Stages]>=2,IIf([2ndStageTemp] Is Null,
0,IIf([2ndStageTemp]=0,0,1)),0) AS SST,
IIf([Stages]>=3,IIf([3rdStageTemp] Is Null,
0,IIf([3rdStageTemp]=0,0,1)),0) AS TST,
IIf([Stages]>=4,IIf([4thStageTemp] Is Null,
0,IIf([4thStageTemp]=0,0,1)),0) AS FthST,
IIf([Stages]>=5,IIf([5thStageTemp] Is Null,
0,IIf([5thStageTemp]=0,0,1)),0) AS FFthST, IIf([BlockRPM] Is Null,
0,IIf([BlockRPM]=0,0,1)) AS BRPM, IIf([DriveTypeID] Is Null,
0,IIf([DriveTypeID]=1,0,1)) AS DTID, IIf([GasEngineRPM] Is Null,
0,IIf([GasEngineRPM]=0,0,1)) AS GERPM, IIf([GasEngineSerialNumber] Is
Null,0,1) AS GESN, IIf([EngineArrangementNumber] Is Null,0,1) AS EAN,
IIf([GasEngineModelNumber] Is Null,0,1) AS GEMN,
IIf([EngineStarterLubeLevel] Is Null,0,1) AS ESLL, IIf([EngineOilPSI]
Is Null,0,IIf([EngineOilPSI]=0,0,1)) AS EOPSI,
IIf([EngineWaterJacketTempurature] Is Null,
0,IIf([EngineWaterJacketTempurature]=0,0,1)) AS EWJT,
IIf([EngineWaterJacketCoolant] Is Null,
0,IIf([EngineWaterJacketCoolant]=0,0,1)) AS EWJC,
IIf([GasEngineHorsePower] Is Null,0,IIf([GasEngineHorsePower]=0,0,1))
AS GEHP, IIf([ElectricRPM] Is Null,0,IIf([ElectricRPM]=0,0,1)) AS
ERPM, IIf([ElectricMotorSerialNumber] Is Null,0,1) AS EMSN,
IIf([ElectricMotorHorsePower] Is Null,0,1) AS EMHP,
subdatatblCompressor.ThermalProtectionID,
subdatatblCompressor.Voltage, subdatatblCompressor.Hz,
subdatatblCompressor.CrankcaseStyleID, subdatatblCompressor.SealType,
subdatatblCompressor.OilHeater, subdatatblCompressor.TKNumber,
subdatatblCompressor.Stages, subdatatblCompressor.GasCoolingSystem,
subdatatblCompressor.MinFlowCapacity,
subdatatblCompressor.MaxFlowCapacity,
subdatatblCompressor.InletPulsationBottles,
subdatatblCompressor.DischargePulsationBottles,
subdatatblCompressor.FlowRateGGE,
subdatatblCompressor.RemoteMonitoring,
subdatatblCompressor.FlowRateKgMin
FROM subdatatblCompressor;

and i havent finished yet. in the end i need one column that will
calculate the total as i did with the first example.

is there a way to use code to do this?- Hide quoted text -

- Show quoted text -
Click to expand...

is there no better way to do this? the amount of fields to count in
the one table subdatatblcompressor is horrendus and i am most
definitely going to have to put the above into another query to do the
final total otherwise i will end up in a situation where the field
will have more than 255 characters, or what ever the character limit
is.

is there a way to do this in VBA code?
 
DawnTreader said:
is there no better way to do this? the amount of fields to count in
the one table subdatatblcompressor is horrendus and i am most
definitely going to have to put the above into another query to do the
final total otherwise i will end up in a situation where the field
will have more than 255 characters, or what ever the character limit
is.

is there a way to do this in VBA code?
Click to expand...

Sure. But before I get to possible VBA solutions, here's a sql solution that
has yet to be suggested:

Create a saved union query called, say, NullCounter, that puts all the
fields to be checked into a single column:

select keyfield1, ..., keyfieldN,
iif(FieldToBeChecked1 is Null,1,0) as NullTest FROM tablename
union all
select keyfield1, ..., keyfieldN, iif(FieldToBeChecked2 is Null,1,0) FROM
tablename
....
union all
select keyfield1, ..., keyfieldN, iif(FieldToBeCheckedN is Null,1,0) FROM
tablename

Then a simple grouping query provides the answer:

select keyfield1, ..., keyfieldN, sum(NullTest) as NullCount from
NullCounter
group by keyfield1, ..., keyfieldN


Now for VBA solutions. You could use a Subroutine:
Open a recordset on the table and loop through the records and fields,
incrementing a counter variable each time a field containing Null is
encountered. If you added a field to the table ahead of time, just populate
it with the value of the counter variable before moving to the next record.
Or you could add a field to the tabledef before opening the recordset.

dim sql as string
sql="select <list of fields>, NullCount from tablename"
dim rs as dao.recordset,fld as dao.field, icnt as integer
set rs=currentdb.openrecordset(sql)
do until rs.eof
icnt=0
for each fld in rs.fields
if fld.name <> "NullCount" and fld.value is null then
icnt = icnt + 1
end if
next fld
rs("NullCount") = icnt
rs.movenext
loop


Alternatively, you could create a function to which you pass the values of
the fields to be checked as a parameter array. In the function, loop through
the array, incrementing the counter varialble:
function GetNullCount(ParamArray pFields() as Variant) as integer
dim i as integer, icnt as integer
icnt = 0
for i = lbound(pFields) to ubound(pFields)
if isnull(pFields(1)) then icnt=icnt+1
next
GetNullCount = icnt
end function

In the query, you would use the expression:
NullCount: GetNullCount([FieldToBeChecked1], ..., [FieldToBeCheckedN])
 
DawnTreader said:
is there no better way to do this? the amount of fields to count in
the one table subdatatblcompressor is horrendus and i am most
definitely going to have to put the above into another query to do the
final total otherwise i will end up in a situation where the field
will have more than 255 characters, or what ever the character limit
is.
Click to expand...
is there a way to do this in VBA code?
Click to expand...

Sure. But before I get to possible VBA solutions, here's a sql solution that
has yet to be suggested:

Create a saved union query called, say, NullCounter,  that puts all the
fields to be checked into a single column:

select keyfield1, ..., keyfieldN,
iif(FieldToBeChecked1 is Null,1,0) as NullTest FROM tablename
union all
select keyfield1, ..., keyfieldN, iif(FieldToBeChecked2 is Null,1,0) FROM
tablename
...
union all
select keyfield1, ..., keyfieldN, iif(FieldToBeCheckedN is Null,1,0) FROM
tablename

Then a simple grouping query provides the answer:

select keyfield1, ..., keyfieldN, sum(NullTest) as NullCount from
NullCounter
group by keyfield1, ..., keyfieldN

Now for VBA solutions. You could use a Subroutine:
Open a recordset on the table and loop through the records and fields,
incrementing a counter variable each time a field containing Null is
encountered. If you added a field to the table ahead of time, just populate
it with the value of the counter variable before moving to the next record.
Or you could add a field to the tabledef before opening the recordset.

dim sql as string
sql="select <list of fields>, NullCount from tablename"
dim rs as dao.recordset,fld as dao.field, icnt as integer
set  rs=currentdb.openrecordset(sql)
do until rs.eof
    icnt=0
    for each fld in rs.fields
        if fld.name <> "NullCount"  and fld.value is null then
            icnt = icnt + 1
        end if
    next fld
    rs("NullCount") = icnt
    rs.movenext
loop

Alternatively, you could create a function to which you pass the values of
the fields to be checked as a parameter array. In the function, loop through
the array, incrementing the counter varialble:
function GetNullCount(ParamArray pFields() as Variant) as integer
dim i as integer, icnt as integer
icnt = 0
for i = lbound(pFields) to ubound(pFields)
    if isnull(pFields(1)) then icnt=icnt+1
next
GetNullCount = icnt
end function

In the query, you would use the expression:
NullCount: GetNullCount([FieldToBeChecked1], ..., [FieldToBeCheckedN])
Click to expand...

i hate to say it, usually i am pretty good at this stuff, but your
posted SQL didnt help.

SELECT
tblProductList.ProductID AS PID,
IIf([SerialNumber] Is Null,0,1) AS Complete
FROM tblProductList

UNION SELECT
tblProductList.ProductID AS PID,
IIf([CustomerOrder] Is Null,0,1) AS Complete
FROM tblProductList

UNION SELECT
tblProductList.ProductID AS PID,
IIf([DateShipped] Is Null,0,1) AS Complete
FROM tblProductList

UNION SELECT
tblProductList.ProductID AS PID,
IIf([DateCommissioned] Is Null,0,1) AS Complete
FROM tblProductList

UNION SELECT
tblProductList.ProductID AS PID,
IIf([WarrantyMonths] Is Null,0,1) AS Complete
FROM tblProductList

UNION SELECT
tblProductList.ProductID AS PID,
IIf([WarrantyShippedMonths] Is Null,0,1) AS Complete
FROM tblProductList

UNION SELECT
tblProductList.ProductID AS PID,
IIf([ProductTypeID]="Compressor",IIf([WarrantyHours] Is Null,0,1),1)
AS Complete
FROM tblProductList

UNION SELECT
tblProductList.ProductID AS PID,
IIf([WorkOrder] Is Null,0,1) AS Complete
FROM tblProductList

UNION SELECT
tblProductList.ProductID AS PID,
IIf([ModelNumber] Is Null,0,1) AS Complete
FROM tblProductList;

all i get is 1 record for each PID.
 
UNION SELECT
tblProductList.ProductID AS PID,
IIf([ModelNumber] Is Null,0,1) AS Complete
FROM tblProductList;

all i get is 1 record for each PID.
Click to expand...

You left out the "ALL" keyword:

UNION ALL SELECT

Without the "ALL", the union query is eliminating duplicates. I see you've
changed the logic so it counts non-nulls instead of counting nulls.

If you wish to identify the fields in addition to counting them, you will
need to add a column to do allow that (which also eliminates the need for
the "ALL" keyword):

SELECT
tblProductList.ProductID AS PID,
"SerialNumber" As Fieldname,
IIf([SerialNumber] Is Null,0,1) AS Complete
FROM tblProductList

UNION SELECT
tblProductList.ProductID AS PID,
"CustomerOrder" As Fieldname,
IIf([CustomerOrder] Is Null,0,1) AS Complete
FROM tblProductList
etc.
 
Back
Top