Solved

IIf() in Query to return Yes/No if sub-records exist or not MS-Acces

Posted on 2009-04-04
5
763 Views
Last Modified: 2013-11-27
I need to know how to get a field in a query to indicate by "yes" or "No" whether records exist in a child table of one of the tables in the Query. As yet the child table is not represented in the query.
I have tried wrapping dCount() in IIf() but no luck:
iif(dCount("ChildTable.Key","ChildTable","ChildTableKey = ParentTable.key") > 0,"Yes","No")
The query populates a listbox
0
Comment
Question by:zoomtrac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Expert Comment

by:pskeens
ID: 24070287
How about

Iif(Count([childTable].[field]) > 0, "YES", "NO")
0
 
LVL 2

Accepted Solution

by:
pskeens earned 500 total points
ID: 24070290
You can do DCOUNT this way as well

HAS_CHILD: IIf(DCount("*","ChildTable",[MasterField]=[ChildField])>0,"YES","NO")
0
 
LVL 11

Expert Comment

by:mildurait
ID: 24070527
You can do this using EXISTS keyward

Column Definition:
HasChldren: EXISTS (SELECT * FROM CHILDTABLE WHERE PARENTTABLE.ID=CHILDTABLE.ID)

Full SQL Syntax:
SELECT
   *,
   EXISTS (SELECT * FROM CHILDTABLE WHERE PARENTTABLE.ID=CHILDTABLE.ID) as HasChildren
FROM PARENTTABLE
0
 
LVL 30

Expert Comment

by:hnasr
ID: 24071419
Try this:
tparent:
id
1
2
3
4
tchild:
id      idparent
1      1
2      1
3      2

SELECT tparent.id, IIf(IsNull([idparent]),"No","Yes") AS Expr1
FROM tparent LEFT JOIN tchild ON tparent.id = tchild.idparent;

id      Expr1
1      Yes
1      Yes
2      Yes
3      No
4      No
0
 

Author Closing Comment

by:zoomtrac
ID: 31566689
Well it was pretty close just a syntax issue:
HasRecords: IIf(DCount("*","tblMLMtrans","[tblMLMtrans].[MLMID] =" & [MLMID])>0,"Yes","No")
where tblMLMtrans is the child table NOT in the Query and the MLMID refers to parent table field in query (needed the ="& syntax) is this because it refers to a value in the query itself?
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question