Struggling with a query

[Phil]

Members Table
MemberID ' Key
MemFirstName
MemHeadOfHouseID eID Long. All the family who live together have the same
MemHeadOfHouseID. This points to the MemberID who is head of the house.
MemHead True / False True for the head of the house, false for everyone else
MemEmail

Wha I am trying to get is the first name of the head of hous and how many
email addresses there are in that house. (Really only interested whether
there an email address or not)

My SQL is
SELECT Member.MemHeadOfHouseID, Sum(Nz([MemEmail])>"") AS FamilyEmailExists,
Max(IIf([MemHead]=True,[MemFirstName])) AS FirstName FROM Member
GROUP BY Member.MemHeadOfHouseID;

I am getting a few blank FirstNames.
As I am getting close to the "too many databases" limit, I would like to use
a single table, but cant see how to get the correct FirstName

Any help appreciated

Phil

[Patrick Finucane]

Would it help if you had 2 copies of the Members table in the
QueryBuilder and link the two? Would Dlookup() assist?

[Phil]

Hi Patrick.
Yes it can be done that way, but I want to keep the number of table pointers
to a minimum because of this "Too many databases error". This query is a
small part of a much bigger query in a form with lots of combo boxes,
subforms etc. and is "pushing the limit" I know because there is a button on
the form to preview a report. If I try to print from the preview I exceed the
limit. So I have to have 2 buttons, one which opens the report in preview and
the other that opens the report acViewNormal

Phil

[Patrick Finucane]

I've never had your problem so I do seem to be pushing the limit.

Years ago somebody posted his query here, seemed to be about 1 1/2
page of statement code, and asked how he could fix it. Somebody
responded make it less complex.

Is there any way to make this humongous query less complex? I've
sometimes created a set of queries; query1, query2, query3, query4,
query5. Query1 would be my base, 2-4 subordinates that use the
preceding query, and used Query5 to run the set.
'
You might not like doing it, but maybe when the command button to run
a report is pressed, create a couple of temp tables from a couple of
queries and link the make tables together. It might add a couple of
seconds to presenting the report but without errors.

I would do what I
Don't look askance when your client says "Phil, we need to add a few
more fields to the report."

[Phil]

On 19/05/2012 17:15:38, Patrick Finucane wrote:
> On May 19, 1:01 am, "Phil" <p...@stantonfamily.co.uk> wrote:
>> On 19/05/2012 03:26:29, Patrick Finucane wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> > On May 18, 5:16 pm, "Phil" <p...@stantonfamily.co.uk> wrote:
>> >> Members Table
>> >>    MemberID    ' Key
>> >>    MemFirstName
>> >> MemHeadOfHouseID eID Long. All the family who live together have the s
> ame
>> >> MemHeadOfHouseID. This points to the MemberID who is head of the house
> .
>> >> MemHead True / False True for the head of the house, false for everyon
> e e
>> > lse
>> >>    MemEmail
>>

> I've never had your problem so I do seem to be pushing the limit.
>
> Years ago somebody posted his query here, seemed to be about 1 1/2
> page of statement code, and asked how he could fix it. Somebody
> responded make it less complex.
>
> Is there any way to make this humongous query less complex? I've
> sometimes created a set of queries; query1, query2, query3, query4,
> query5. Query1 would be my base, 2-4 subordinates that use the
> preceding query, and used Query5 to run the set.
> '
> You might not like doing it, but maybe when the command button to run
> a report is pressed, create a couple of temp tables from a couple of
> queries and link the make tables together. It might add a couple of
> seconds to presenting the report but without errors.
>
> I would do what I
> Don't look askance when your client says "Phil, we need to add a few
> more fields to the report."
>
>

You're lucky not to have had the problem. It's to do with the number of
tables & queries, not the numver of fields. Combo boxes on subforms tend to
use a lot of tableIDs (limit 2048) Maybe my DBs are too complicated, but I
have run into that problem several times.

As to the original problem, I went with your DLookup solution.

Thanks

Phil