yes or no field in query

[ddbrook]

I have a query that looks like:

Acct#,Name,Loc,Utility Type.
123,Diane,3008 Look up St,W(Water)
456,Juli,4407 Pleasant St, W(Water)
456,Juli,4407 Pleasant St, S(Sewer)

From the data above you will see I have one record with Water...
I need a yes or no field showing the record does not have Sewer account with it.

All records should have both Water and Sewer...but some don't have Sewer.

How can I run a query and just show the Water without Sewer accounts??
I need them to cross reference to one another...

Can you please help??

Thanks
ddbrook

 

[jsolutions]

It looks like the last field either contains a W for Water or S for Water and Sewer - is this correct?

If so, create a query and in the criteria row for the field, type W. This will give you only the ones that have water.

If this is not correct, give more details about how the fields are divided and we can move forward.

Jay

 

[MrBillSC]

It would be helpful to know what tables your query is reading.

You will probably need to do multiple passes.
In the first query, select all the accounts with water.
In the second query read the first query and do a left outer join by account number to select only the sewer records. If the type is null in the sewer record, set an expression to "n" otherwise set it to "y".

hope this helps!

 

[jsolutions]

DDBrook - I just looked over the question again and realized I missed a piece of the puzzle that luckily MrBillSC picked up.

There are several approaches here. MrBillSC has a good one.

Another one is to create two queries - one to pull Water records and another to pull Sewer. Then create a third query (a lot of queries). Add the two original queries. Create a left outer join (draw a line from the Acct num in the Water query to the Acct num in the sewer query). Add the Utility type field from the sewer query. Use the is null criterion for this field.

The result will be all account numbers which have water but not sewer.

Sorry for the earlier confusion.

Let me know if you have any questions.

 

[ddbrook]

Thank you both very much...

It worked perfect. I greatly appreciate your promptness.

ddbrook