Complete If

[Schwimms]

I have a query that I want to return results of "complete" if the same job
number has an Completipon date in all the lines that is taken up by the job
number, and an "incomplete" if not. This is what it looks like and I want the
Complete Column added:

Job # Date Complete?
400 1/2/08 Icomplete
400 Incomplete
400 Incomplete
405 1/3/08 Complete
405 1/10/08 Complete
410 5/7/07 Complete
410 3/10/07 Complete
410 8/9/06 Complete
415 6/5/06 Incomplete
415 Incomplete

Can you help me?

 

[Bob Quintal]

I have a query that I want to return results of "complete" if the
same job number has an Completipon date in all the lines that is
taken up by the job number, and an "incomplete" if not. This is
what it looks like and I want the Complete Column added:

Job # Date Complete?
400 1/2/08 Icomplete
400 Incomplete
400 Incomplete
405 1/3/08 Complete
405 1/10/08 Complete
410 5/7/07 Complete
410 3/10/07 Complete
410 8/9/06 Complete
415 6/5/06 Incomplete
415 Incomplete

Can you help me?

two step process. Step one is a totals query
SELECT [JOB #],
count([date]) AS All_Rows,
sum(iif(isnull([date]),1,0) as OpenRows
FROM Table
GROUP BY [job #];

Step 2 is a left join query of the first query and the table
with a calculated field to give your status
SELECT [JOB #],
[Date],
IIF(OpenRows >0, "Inc","C") & "omplete" as Status
FROM table
LEFT JOIN query
ON table.[Job #] = query.[job #]
ORDER BY
table.[Job #],
IsNull([date]),
table.[date] ;

Be careful using date as a field name. it is a reserved word in
Access, and has been know to cause all sorts of problems when access
misinterprets your date for its date and vice versa.

 

[Beetle]

You can use an IIF statement in one of the fields of your query like;

Iif(Not IsNull([DateField]), "Complete", "Incomplete")

 

[Schwimms]

Bob,

Great got it to work, THANK YOU!

....I couldn't figure out the second part in SQL but I just created a query
and said if open rows equals 0 then complete.

I am going to use this in a lot of reports!

I have a query that I want to return results of "complete" if the
same job number has an Completipon date in all the lines that is
taken up by the job number, and an "incomplete" if not. This is
what it looks like and I want the Complete Column added:

Job # Date Complete?
400 1/2/08 Icomplete
400 Incomplete
400 Incomplete
405 1/3/08 Complete
405 1/10/08 Complete
410 5/7/07 Complete
410 3/10/07 Complete
410 8/9/06 Complete
415 6/5/06 Incomplete
415 Incomplete

Can you help me?

two step process. Step one is a totals query
SELECT [JOB #],
count([date]) AS All_Rows,
sum(iif(isnull([date]),1,0) as OpenRows
FROM Table
GROUP BY [job #];

Step 2 is a left join query of the first query and the table
with a calculated field to give your status
SELECT [JOB #],
[Date],
IIF(OpenRows >0, "Inc","C") & "omplete" as Status
FROM table
LEFT JOIN query
ON table.[Job #] = query.[job #]
ORDER BY
table.[Job #],
IsNull([date]),
table.[date] ;

Be careful using date as a field name. it is a reserved word in
Access, and has been know to cause all sorts of problems when access
misinterprets your date for its date and vice versa.