Trying to find vacant properties

[shortstuff2212]

I have a database with the tables property and elector and I am trying to find all the vacant properties. Does anyone have any ideas? It would be great if you could help me on this I have tried all ways and nothing works. I can't use the sql statement because I need to be able to count how may properties there are as this will be part of a statistical report. Thanks in advance.

 

[rosemaryl]

Dear shortstuff2212,

Could you please provide more information: Tables and fields in use, and what indicates that a property is vacant.

If I understand, you want to pull all properties, but get a count of the ones that are vacant.

Assuming that there is a field that indicates if the property is vacant, you could get a counter by doing this:

If {Table.PropertyVacantfield} = "Yes" then 1 else 0

You would then simply sum.

Without more information such as the Tables and fields in use, how they are linked, and what indicates that a property is vacant I cannot provide more assistance.

regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Create a running total to count and in the evaluate use a formula section place something like:

{Property.Vacant} = true

Or however your table defines a vacant property.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[shortstuff2212]

Thanks for replying. The properties are held in the property table and the coresponding electors are in the elector table. What I need is a formula to count the properties which do not appear in the elector table. Something like

select count(*) from property where propertyref not in (select propertyref from elector)

I can't do this in the formula section of a running total so I need to know how to convert the select statement.

Any help will be appreciated.

 

[rosemaryl]

Dear shortstuff2212,

Okay, how about:

I am assuming you are doing a left outer join from properties to electors so you get all properties.


Create a formula:

If isnull({electors.propertyref}) then 1 else 0

place in your detail and then insert a summary for that which will give you a total of all vacant properties.

Hope that helps,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[shortstuff2212]

Dear Rosemary

Thanks for the advice, unfortunately it didn't work. There aren't any empty records in the elector table. Thanks anyway.

Cheers
Kath

 

[rosemaryl]

Dear Kath,

Did you do a left outer join in the Visual Linking Expert?

From Properties to Electors (choose options on link, left outer).

Please provide more explanation. What I described does work.

Here is an example:

Clients Table
Client ID
Client Name

Order Table
Order ID
Client ID

If I do a Left Outer join from Clients to Orders, I will get a list of all clients, regardless of whether they have orders. An equal join would only return clients with orders. Sounds to me like you have an Equal join in place since no empty records for Electors exists.

Let me know if you need more help.

Ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.