Need Help Please w/IIF Statement

  • Thread starter Thread starter TotallyConfused
  • Start date Start date
T

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.
 
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.
Click to expand...


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
 
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
..
 
Back
Top