Need Help Please w/IIF Statement

[TotallyConfused]

I need help please with IIF statement for my query. I have a large list of
mbrs with ids were a family will have the same id consisting of text and
numbers and ending with a letter or number that if it is a family the ending
letter or number will increase by one number or text. eg. Main subscriber -
ZXXXXY0A, dependents would be ZXXXXY0B, ZXXXXY0C etc if more dependents. I
need to weed out all the mbrs that do not have dependents. How can I write
this? Thank you.

 

[banem2]

I need help please with IIF statement for my query. I have a large list of
mbrs with ids were a family will have the same id consisting of text and
numbers and ending with a letter  or number that if it is a family the ending
letter or number will increase by one number or text.  eg.  Main subscriber -
ZXXXXY0A, dependents would be ZXXXXY0B, ZXXXXY0C etc if more dependents.   I
need to weed out all the mbrs that do not have dependents.  How can I write
this?  Thank you.

Assuming that XXXXY are same for main subscriber and all dependants,
then create a query which counts number of these character appearance
and filter the field with = 1. Something like this:

SELECT Count(Mid([DataField],2,6)) AS MyField
FROM tblMyTable
HAVING Count(Mid([dep],2,6)))=1);

You can modifiy this SQL to make it work for your data, table and
field.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[John Spencer]

The SQL statement for the query would probably look like

SELECT *
FROM [Members Table]
WHERE Left(ID, Len(Id)-1) in
(SELECT Left(ID, Len(Id)-1) as FamID
FROM [Members Table]
GROUP BY Left(ID, Len(Id)-1)
HAVING COUNT(*) =1)

Post back if you can't figure out how to set that up and can only use the
design view. Basically, you need a calculated field and a subquery as
criteria
Field: FamID: Left([ID],Len(ID)-1)
Criteria: IN (SELECT Left(ID, Len(Id)-1) as FamID FROM [Members Table] GROUP
BY Left(ID, Len(Id)-1) HAVING COUNT(*) =1)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..