ms access - SQL reports error when secondary table has no records -

i'm writing family tree application access 2010 end , vb.net front end. (long story, not relevant here.) works, need sort marriages person. sorting children in family, can't use obvious dateofbirth or dateofmarriage fields because don't have values. here's simplified snapshot of data structure

people table                families table - id                        - id - fullname                  - husband - dob (date of birth)       - wife - dod (date of death)       - dom (date of marriage)  personmarriage table (a linking table) - person (foreign key people) - family (foreign key families) - spouse (foreign key people) - marriage order (a contrived value force order want)  familychild table (a linking table) - family (foreign key families) - child  (foreign key people) - birthorder (a contrived value force order want) 

(fwiw, dates of birth, date, , marriage contrived can handle values "march 1862" , "before 1753" still maintain sortability. empty string signifies "unknown")

i'm trying read relevant data of marriages person can use complex comparisons provide partial sort. instance, when person has 2 marriages, , don't know marriage dates, if child of spouse born after spouse b died, can infer marriage spouse b occurred first.

for each marriage, want retrieve marriage date, spouse's dob, spouse's dod, , dob of child. (any child; grab first physical record birthdate.) here's sql query works sometimes:

select personmarriage.family, personmarriage.spouse, dom, s.dob, s.dod,     (select top 1 c.dob familychild          left join people c on familychild.child=c.id      c.dob<>"" , familychild.family=personmarriage.family) childdob  families f inner join      (personmarriage inner join people s on personmarriage.spouse = s.id ) on f.id = personmarriage.family  personmarriage.person=? 

note use subquery within field list find child. i'm sure need move child-selection subquery clause , use left join, haven't figured out way it. join order, fwiw, determined access's query wizard, since join order seemed natural me didn't work.

this query appears work when @ least 1 of marriages has child birth date. when neither marriage has child birth date, error on order of "this query can return @ 1 result".

any suggestions?

regarding error

"at 1 record can returned subquery."

your problem stems fact ace/jet (access) database top 1 query may return more 1 row if there "tie" top 1. example given [people] data...

id  fullname  dob         dod --  --------  ----------  ---  1  dave      1967-07-01       2  gertrude  1968-03-21       3  alice     after 1968       4  duncan    after 1987   5  ernest    after 1987       6  richard   after 2003       7  suzy      after 2003      

...the query...

select top 1 * [people] order [dob] desc 

...will return 2 rows:

id  fullname  dob         dod --  --------  ----------  ---  7  suzy      after 2003       6  richard   after 2003 

so, instead of subquery...

(     select top 1 c.dob      familychild left join people c              on familychild.child=c.id      c.dob<>"" , familychild.family=personmarriage.family ) childdob 

...try using

(     select min(c.dob)      familychild left join people c              on familychild.child=c.id      c.dob<>"" , familychild.family=personmarriage.family ) childdob