determining if a field should be included

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

DawnTreader

Hello All

i have this query

....
UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"Cooling Method" AS FieldName,
IIf([CoolingMethod] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"1st Stage Configuration" AS FieldName,
IIf([1stStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"2nd Stage Configuration" AS FieldName,
IIf([2ndStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"3rd Stage Configuration" AS FieldName,
IIf([3rdStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"4th Stage Configuration" AS FieldName,
IIf([4thStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"5th Stage Configuration" AS FieldName,
IIf([5thStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor;

and i need to determine first if how many stages a PID has like this:

SELECT subdatatblCompressor.CompressorID, subdatatblCompressor.ProductID, subdatatblCompressor.Stages
FROM subdatatblCompressor
WHERE (((subdatatblCompressor.Stages) Is Not Null));

and then determine if i need the query to do the stage in the previous query.

i hope that makes sense. how would i do that? am i going to need a sub query? seperate query?

any and all help appreciated.
 
DawnTreader said:
Hello All

i have this query
UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"5th Stage Configuration" AS FieldName,
IIf([5thStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor;

and i need to determine first if how many stages a PID has like this:

SELECT subdatatblCompressor.CompressorID,
subdatatblCompressor.ProductID, subdatatblCompressor.Stages FROM
subdatatblCompressor
WHERE (((subdatatblCompressor.Stages) Is Not Null));

and then determine if i need the query to do the stage in the
previous query.

i hope that makes sense.

Sorry, but it doesn't. Maybe if you show us some sample data and desired
results it will become clearer.
 
DawnTreader said:
Hello All

i have this query
UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"5th Stage Configuration" AS FieldName,
IIf([5thStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor;

and i need to determine first if how many stages a PID has like this:

SELECT subdatatblCompressor.CompressorID,
subdatatblCompressor.ProductID, subdatatblCompressor.Stages FROM
subdatatblCompressor
WHERE (((subdatatblCompressor.Stages) Is Not Null));

and then determine if i need the query to do the stage in the
previous query.

i hope that makes sense.

Sorry, but it doesn't. Maybe if you show us some sample data and desired
results it will become clearer.

Sorry. here is a different question:

how do i only get this to happen if the stages field has data and has a value of 1.

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"1st Stage Configuration" AS FieldName,
iif([Stages] is not null,IIf([Stages]>=1,(IIf([1stStageConfig] Is Null,0,1)),0),Null) AS Complete
FROM subdatatblCompressor

you can see i am trying to do it, but i dont want this to appear at all if the stages value is null. should i wrap the select statement in the Union into a sub query?
 
DawnTreader said:
Sorry. here is a different question:

how do i only get this to happen if the stages field has data and has
a value of 1.
Get what to happen? Define "this" please.

If stages has a value of 1, then it has data, does it not?
UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"1st Stage Configuration" AS FieldName,
iif([Stages] is not null,IIf([Stages]>=1,(IIf([1stStageConfig] Is
Null,0,1)),0),Null) AS Complete
FROM subdatatblCompressor

you can see i am trying to do it, but i dont want this to appear at

What do you mean by "this"?
all if the stages value is null. should i wrap the select statement
in the Union into a sub query?

I still don't understand. Sample data and desired results always helps.

As a guess, maybe this will help?

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"1st Stage Configuration" AS FieldName,
iif([Stages] is not null,IIf([Stages]>=1,(IIf([1stStageConfig] Is
Null,0,1)),0),Null) AS Complete
FROM subdatatblCompressor
WHERE [Stages] =1
 
Hello All

i have this query

...
UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"Cooling Method" AS FieldName,
IIf([CoolingMethod] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"1st Stage Configuration" AS FieldName,
IIf([1stStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"2nd Stage Configuration" AS FieldName,
IIf([2ndStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"3rd Stage Configuration" AS FieldName,
IIf([3rdStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"4th Stage Configuration" AS FieldName,
IIf([4thStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"5th Stage Configuration" AS FieldName,
IIf([5thStageConfig] Is Null,0,1) AS Complete
FROM subdatatblCompressor;

and i need to determine first if how many stages a PID has like this:

SELECT subdatatblCompressor.CompressorID, subdatatblCompressor.ProductID,subdatatblCompressor.Stages
FROM subdatatblCompressor
WHERE (((subdatatblCompressor.Stages) Is Not Null));

and then determine if i need the query to do the stage in the previous query.

i hope that makes sense. how would i do that? am i going to need a sub query? seperate query?

any and all help appreciated.

sorry bob. i meant that the iif([Stages] is null..., part of my statement. i really dodnt have sample data. but you know what, you are awesome as you helped immensely as that was what i was missing. it should only do that statement now is stages is not null. i will implement and let you know.
 
DawnTreader said:
Sorry. here is a different question:

how do i only get this to happen if the stages field has data and has
a value of 1.
Get what to happen? Define "this" please.

If stages has a value of 1, then it has data, does it not?
UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"1st Stage Configuration" AS FieldName,
iif([Stages] is not null,IIf([Stages]>=1,(IIf([1stStageConfig] Is
Null,0,1)),0),Null) AS Complete
FROM subdatatblCompressor

you can see i am trying to do it, but i dont want this to appear at

What do you mean by "this"?
all if the stages value is null. should i wrap the select statement
in the Union into a sub query?

I still don't understand. Sample data and desired results always helps.

As a guess, maybe this will help?

UNION ALL SELECT
subdatatblCompressor.ProductID AS PID,
"Compressor Data" AS Category,
"1st Stage Configuration" AS FieldName,
iif([Stages] is not null,IIf([Stages]>=1,(IIf([1stStageConfig] Is
Null,0,1)),0),Null) AS Complete
FROM subdatatblCompressor
WHERE [Stages] =1

sorry bob. i meant that you could see that i was trying to get the statement to not do anything with the "iif([Stages] is null..." part of my statement. i really don't have sample data. but you know what, you are awesome as you helped immensely as that was what i was missing. it should only do that statement now is stages is not null. i will implement and let you know.
 
DawnTreader said:
sorry bob. i meant that the iif([Stages] is null..., part of my
statement. i really dodnt have sample data. but you know what, you
are awesome as you helped immensely as that was what i was missing.
it should only do that statement now is stages is not null. i will
implement and let you know.

I'm glad it helped - still not sure what I did ... :-)
 
Back
Top