Community
Showing results for 
Search instead for 
Do you mean 
Reply

Query help needed

Tuned Listener
Posts: 40
Country: USA

Query help needed

I am trying to build a query where I can create a worklist based on expiration dates of various types of insurance for a property.  I have created a group query with the following criteria:

 

Field: Liability Insurance Exp on or before Today OR

Field: Liability Insurance Exp does not contain data AND

Field: Property Insurance Exp on or before Today OR

Field: Property Insurance Exp does not contain data AND

Field: Comments on Ins Certificates contains data = Nothing END

 

When testing this query, I can identify one property that has dates in the insurance fields = 6-30-10 (which is before TODAY), and has data in the Comments field.  This result is not showing up in the query result.

 

How do I need to adapt my query to get all properties that have expired or missing insurance certificates?

 

Thanks.

 

Bronze Contributor
Posts: 930
Country: USA

Re: Query help needed

Probably need parenthesis around the grouped expressions.

 

(Field: Liability Insurance Exp on or before Today OR

Field: Property Insurance Exp on or before Today  ) and

Field: Comments on Ins Certificates Contains Data

 

Use the dropdowns to add left or right parens before the first line and after the second line to group them together.

This query will find two types of records:

1. Those with a Liability Insurance Expiration date on or before today AND something in the Comments field.

2. Those with a Property Insurance Expiration date on or before today AND something in the Comments field.

Signature
Tuned Listener
Posts: 40
Country: USA

Re: Query help needed

Ok...I'm catching on to where you're leading me but this doesn't quite do it.  The result I'm looking for is to see a list that includes:

 

properties where the liability insurance exp is either on or before today or blank AND the properties where the property insurance is either on or before today or blank.  I want the comment field to show whether it has data in it or not.

 

Another piece of info...Because my database has groups that I don't want considered for the list, I've added an active box that is a yes/no box.  I've id the groups I want to include and checked the box.  So I know my query will need to include that this field = true.

 

I REALLY appreciate your help.

 

I know one way I could accomplish this is to do a simple lookup on my active box and show the columns I want, but my intent is to create a simple "query report" that ultimately encourages other users to engage in use of the database.  If I can create a tool for them, they might be encouraged to investigate more.

 

 

Bronze Contributor
Posts: 930
Country: USA

Re: Query help needed

 

(Field: Liability Insurance Exp on or before Today OR

Field: Liability Insurance Exp does not contain data) AND

(Field: Property Insurance Exp on or before Today OR

Field: Property Insurance Exp does not contain data) AND

Active = true

 

If you want the comment field to show whether it has data or not, then it's not part of the query because it doesn't matter - it's simply part of the report, and unlike working with SQL statements, you don't have to decide in the query (might look something like 'Select comments, liability insurance, etc from... ')which fields are included in the result - in ACT!, the query and the selected data are separate.

 

 

 

 

Signature