Help!

[SMatthews]

I am trying to make a query that will show records from a table that aren't shown on a series of other queries. There are 15 other queries that pull different records based on the field "Client #". Each query pulls a different series of client numbers from the same table. The final query should show me all client numbers that were not previously selected in one of the first 15 queries. Thanks in advance.

 

[RV]

You could try this:

First create a UNION query combining all Client #'s from your 15 queries:

SELECT Client#
FROM YourQuery1
UNION
SELECT Client#
FROM YourQuery2
UNION
...
UNION
SELECT Client#
FROM YourQuery15;

Save this UNION query.

Then create a new query using the Union query(I've called it QryUnion):

SELECT Client#
FROM YourMasterTable
WHERE NOT EXISTS(
SELECT Client#
FROM QryUnion
WHERE QryUnion.Client#=YourMasterTable.Client#);

YourMasterTable = the table you want to retrieve the recors from which arent't retrieved by (one of) your 15 queries.

Just out of curiosity:
why do you want to create this query and why are you using fifteen queries?

Suc6,

RV

 

[SMatthews]

Thanks RV. I'll give it a try.

The reason I have 15 queries is because I have 15 employees that have a query assigned to each one of them. Each employee gets a certain chunk of work based on certain numbers assigned to external clients. The 15 queries don't cover all client numbers, just the major ones, so I need a query to show me everything that is not included on the first 15. Might there be a better way for me to take care of this without 15+ queries?

 

[RV]

>Might there be a better way for me to take care of this without 15+ queries?<

I'm not an expert on this.

>Each employee gets a certain chunk of work based on certain numbers assigned to external clients<

So there seems to be a relation (constraint) between clients and employees.
Perhaps it's possible to you a login screen on database level or form level.
You could:

- store the usernames (same as employee name)
- assign a client to a certain employee
- refer in the new query to the username used
to log in and retrieve all clients which are
assigned to an employee using
username=employeename

Just a global idea, you wouldn't have to use 15 queries but only one.
Just post a message if this seems workable and you can't figure it out in detail.
I guess there are lots of colleagues who can help you out..

Greetings,

RV

 

[simongallop]

How about a table with 2 fields being Employee_ID and Client_ID:

You can then run one query where you type in the employee ID and it returns all the clients and another query that shows all clients in the Client table that are not assigned to an Employee (ie Not in the employee table)

Just an idea