Solved

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

Posted on 2009-04-04
5
758 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
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - Labels prompt to print 4 31
SELECT query on two levels (detail and summary) 13 50
SQL Server Count where two id types exist in column 8 27
Access Update Query 1 20
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

776 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