INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Composite query criteria

Composite query criteria

(OP)
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?



RE: Composite query criteria


So your data looks like this?

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
 
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.

RE: Composite query criteria

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Composite query criteria

(OP)
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.

RE: Composite query criteria

(OP)
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?

RE: Composite query criteria

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

CODE -->

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)); 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close