Composite query criteria

[TrekBiker]

In a Customers form I have two fields, a name in AccountManager and a tick box for Sponsor.

I'm using the AccountManager name as criterion for a query that selects customers assigned to that person. So far easy. But, any customers marked Sponsor also needs to be included in one person's output, for example.

So in simplified form the query is on a table like this, with fields AccountManager, Sponsor, Customer and with Dave been the person who needs to include all sponsors.

Mike Yes Customer1
Mike No Customer2
Mike No Customer3
John Yes Customer4
John No Customer5
Dave Yes Customer6
Dave No Customer7
Dave Yes Customer8

The results when passing different names to the query need to be like this

If Mike, Customers 1, 2, 3
If John, Customers 4, 5
If Dave, Customers 1, 4, 6, 7, 8

How do I create criteria so that everyone other than Dave only get people assigned to them but Dave also gets anyone ticked as Sponsor?

 

[Andrzejek]

So your data looks like this?

[pre]
AccountManager Sponsor Customer ???
Mike Yes Customer 1
Mike No Customer 2
Mike No Customer 3
John Yes Customer 4
John No Customer 5
Dave Yes Customer 6
Dave No Customer 7
Dave Yes Customer 8
[/pre]
Is Mike a Sponsor? Or Customer (1) is a Sponsor?

And what makes Dave so 'special' that "Dave also gets anyone ticked as Sponsor" ?



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[SkipVought]

where AccountManager= 'Dave' Or Sponsor = 'Yes'

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[TrekBiker]

Hi Andrzejek

Your ???? column doesn't exist. What I meant by the line 'If Mike, Customers 1, 2, 3' was that if Mike is chosen, the query returns Customer1, Customer2, Customer3.

Some customers are also sponsors so Mike is account manager for Customer1 who is also a sponsor. Customer2 isn't a sponsor, etc.

Dave handles all customers who are sponsors as well as any who he is account manager for.

 

[TrekBiker]

Skip

As I said, the query uses a parameter value on the AccounManager field that is taken from a form, ie forms!frmCustomers.AccountManager.

If this parameter is say John won't your where clause just pick Customer4 and miss Customer5?

 

[TrekBiker]

OK, I think I've got it. Case of avoiding syntax errors in a complex Where condition.

SELECT qryAllActions.FirstName, qryAllActions.Surname, qryAllActions.AccountManager, qryAllActions.Sponsor
FROM qryAllActions
WHERE (((IIf([forms]![frmEntries].[AccountManager]="Dave",[AccountManager]=[forms]![frmCustomers].[AccountManager] Or [Sponsor]=Yes,([AccountManager]=[forms]![frmCustomers].[AccountManager])))<>False));