This Just In!

[trackmedic]

I need help again!

I have a query that I am making a report off of. The querie has infor for a vessel, good findings, bad findings and comments.

I want the report to see bad findings per vessel. I added a criteria space under the vessel section and when you type the boat name in, the report pulls up the bad stuff. The weird thing is that the report pulls up the blanks for the good stuff.

the good stuff and the bad stuff are in one table that is linked to the main table the query is made from.

How can I make the query just see the items and not the blanks??????

As usual, thanks in advance!

 

[ajetrumpet]

I need help again!

What a surprise

I want the report to see bad findings per vessel. I added a criteria space under the vessel section and when you type the boat name in, the report pulls up the bad stuff. The weird thing is that the report pulls up the blanks for the good stuff.

Is there a blank cell (in the "good stuff" field) for every record that has a value in the "bad stuff" field??

How can I make the query just see the items and not the blanks??????

Depends on where the blanks are...

1) In another field = *Eliminate the field from the query*
2) In the "bad" field = Write criteria that says...

WHERE ["bad" field] IS NOT NULL

 

[trackmedic]

nope

not working??

 

[Kinger43]

Do you mean that if the "bad stuff" field is blank, the report still adds a section for that vessel and you don't want to see anything for that vessel if there is no bad stuff?

 

[trackmedic]

yup

That's what I need!

 

[trackmedic]

Here is my SQL

SELECT main_input_table.ID,
main_input_table.Date,
main_input_table.Vessel,
concerns_sub.concerns,
concerns_sub.comments,
main_input_table.Location
FROM main_input_table INNER JOIN concerns_sub ON main_input_table.ID = concerns_sub.Number
ORDER BY concerns_sub.positives DESC ,
concerns_sub.concerns DESC , concerns_sub.comments DESC;

I would like the query to pick a certian vessel and I can do that in criteria. What happens is I have some comment sections that are blank and they are pulling blank concern sections. I only want to pull the concerns section if it has criteria and I do not want to pull the blank comment sections over. the only time I would need these is if they have info in them. If the comments section does not have info in it, I do not need it. Basically, not all concerns have comments.

 

[ajetrumpet]

What happens is I have some comment sections that are blank and they are pulling blank concern sections.

I don't understand this.

I only want to pull the concerns section if it has criteria

You mean the criteria that you type in?? I don't see any criteria listed in the SQL above...

I'm not sure if you're understanding what exactly a criteria can do. It queries RECORDS, not individual fields. So, if you're wanting to see only field cells in records where there is data present, you can't do that. The field is part of the query, thus, the blank cells in that field that are part of certain records come along for the ride!!

 

[Kinger43]

fixable

So you have multiple entries per vessel and you only want to see the ones with comments and/or concerns?

If this is the case you need to put Is Not Null in the criteria of the comments section and the concerns section. If you only want entries with both comments and concerns put these on the same criteria line. If you want entries with one or the other put them on different criteria lines.

I have attached two screen shots with examples of what I described above. OR will give you all records with either a comment OR a concern. AND will give you only records with a comment AND concern. If you use the OR method you will still have the blank comment/concern sections as long as they aren't both blank.