determining if a field should be included

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.
 
B

Bob Barrows

DawnTreader said:
Hello All

i have this query
Click to expand...
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.
Click to expand...

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

DawnTreader

DawnTreader said:
Hello All

i have this query
Click to expand...
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.
Click to expand...

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

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?
 
B

Bob Barrows

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.
Click to expand...
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
Click to expand...

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?
Click to expand...

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
 
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.
Click to expand...

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.
 
D

DawnTreader

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.
Click to expand...
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
Click to expand...

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?
Click to expand...

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
Click to expand...

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.
 
B

Bob Barrows

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.
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concatenating the same field from multiple records 16
Question about Queries with Subqueries 1
How to use WHERE NOT EXISTS 2
Determining most recent date in a Union query 2
Reading Previous record of field and inserting it into current If Is Null?? 2
System Resource Exceeded/Query Too Complex 4
#Error in calculated field 1
Union Query Access 2007 SP2 MSO 7

Top