Related records

[Petros]

Table A and Table B are related in "one to many" relation.

I want to show all records in Table A and IF there are related records in Table B, i want to display a Yes, IF not a No, and the number of related records to a specific records in Table A.

How to do this?

Thanks!

 

[vbaInet]

Petros, use a DCount() function plus an IIF()

IIF(DCount("*", "TableName", "ID=" & [ID]) > 0, "Yes", "No")

 

[Brianwarnock]

Why not just join the tables on the related fields with a left/right join and count something like the SQL below and if a 0 is not enough to show no records then , well see below

SELECT Tablea.akey, Count(Tableb.bkey) AS CountOfbkey, IIf([countofbkey]=0,"NO","YES") AS [any]
FROM Tablea LEFT JOIN Tableb ON Tablea.akkey = Tableb.btx
GROUP BY Tablea.akey;

Joins are generally more efficient than Domain functions.

Brian

 

[Petros]

I will try out both suggestions; i much appreciate your effort!

I see i have a tendency to complicate "things", beginners ignorance i suppose..

Thanks!