Help with Query Design

S

stephens.nate

Hello,

I need to exclude results in a query, and I'm not sure how. Here's the
scenario:

I have an agent who processes payments from multiple billers. I want
the query to return all agents who do not process payments for a
specific biller. I've tried doing a Not In on the biller, but it still
brings back all agents since they all process other billers too.

I appreciate any help.

Thanks,

Nate
 
S

stephens.nate

SELECT database.agent, database.biller
GROUP BY database.agent, database.biller
HAVING (((database.biller) Not In (Biller#)));
 
K

KARL DEWEY

Use two queries. First one here is named StevensExclude --
SELECT Database.Agent, Database.Biller
FROM [Database]
WHERE (((Database.Biller)=[Enter Biller to exclude]));

SELECT Database.Agent, Database.Biller
FROM [Database] LEFT JOIN StevensExclude ON Database.Agent =
StevensExclude.Agent
WHERE (((StevensExclude.Agent) Is Null));

This could be done with a subquery also.
I would recommend you using a table name other than 'database'.
 
S

stephens.nate

I received a syntax error (missing operation) on 'Database.Agent =
StevensExclude.Agent'

I appreciate any additional help.



KARL said:
Use two queries. First one here is named StevensExclude --
SELECT Database.Agent, Database.Biller
FROM [Database]
WHERE (((Database.Biller)=[Enter Biller to exclude]));

SELECT Database.Agent, Database.Biller
FROM [Database] LEFT JOIN StevensExclude ON Database.Agent =
StevensExclude.Agent
WHERE (((StevensExclude.Agent) Is Null));

This could be done with a subquery also.
I would recommend you using a table name other than 'database'.

[email protected] said:
SELECT database.agent, database.biller
GROUP BY database.agent, database.biller
HAVING (((database.biller) Not In (Biller#)));
Click to expand...
Click to expand...
 
K

KARL DEWEY

Check the pasted SQL for a hard return following the equal sign.

[email protected] said:
I received a syntax error (missing operation) on 'Database.Agent =
StevensExclude.Agent'

I appreciate any additional help.



KARL said:
Use two queries. First one here is named StevensExclude --
SELECT Database.Agent, Database.Biller
FROM [Database]
WHERE (((Database.Biller)=[Enter Biller to exclude]));

SELECT Database.Agent, Database.Biller
FROM [Database] LEFT JOIN StevensExclude ON Database.Agent =
StevensExclude.Agent
WHERE (((StevensExclude.Agent) Is Null));

This could be done with a subquery also.
I would recommend you using a table name other than 'database'.

[email protected] said:
SELECT database.agent, database.biller
GROUP BY database.agent, database.biller
HAVING (((database.biller) Not In (Biller#)));

KARL DEWEY wrote:
Post your SQL and I will try to edit to fit your requirement.

:

Hello,

I need to exclude results in a query, and I'm not sure how. Here's the
scenario:

I have an agent who processes payments from multiple billers. I want
the query to return all agents who do not process payments for a
specific biller. I've tried doing a Not In on the biller, but it still
brings back all agents since they all process other billers too.

I appreciate any help.

Thanks,

Nate
Click to expand...
Click to expand...
Click to expand...
 
S

stephens.nate

As in there should or shouldn't be one? I'm assuming not.

KARL said:
Check the pasted SQL for a hard return following the equal sign.

[email protected] said:
I received a syntax error (missing operation) on 'Database.Agent =
StevensExclude.Agent'

I appreciate any additional help.



KARL said:
Use two queries. First one here is named StevensExclude --
SELECT Database.Agent, Database.Biller
FROM [Database]
WHERE (((Database.Biller)=[Enter Biller to exclude]));

SELECT Database.Agent, Database.Biller
FROM [Database] LEFT JOIN StevensExclude ON Database.Agent =
StevensExclude.Agent
WHERE (((StevensExclude.Agent) Is Null));

This could be done with a subquery also.
I would recommend you using a table name other than 'database'.

:

SELECT database.agent, database.biller
GROUP BY database.agent, database.biller
HAVING (((database.biller) Not In (Biller#)));

KARL DEWEY wrote:
Post your SQL and I will try to edit to fit your requirement.

:

Hello,

I need to exclude results in a query, and I'm not sure how. Here's the
scenario:

I have an agent who processes payments from multiple billers. I want
the query to return all agents who do not process payments for a
specific biller. I've tried doing a Not In on the biller, but it still
brings back all agents since they all process other billers too.

I appreciate any help.

Thanks,

Nate
Click to expand...
Click to expand...
Click to expand...
 
S

stephens.nate

I tried w/ and w/o the hard return, same error for both.

As in there should or shouldn't be one? I'm assuming not.

KARL said:
Check the pasted SQL for a hard return following the equal sign.

[email protected] said:
I received a syntax error (missing operation) on 'Database.Agent =
StevensExclude.Agent'

I appreciate any additional help.



KARL DEWEY wrote:
Use two queries. First one here is named StevensExclude --
SELECT Database.Agent, Database.Biller
FROM [Database]
WHERE (((Database.Biller)=[Enter Biller to exclude]));

SELECT Database.Agent, Database.Biller
FROM [Database] LEFT JOIN StevensExclude ON Database.Agent =
StevensExclude.Agent
WHERE (((StevensExclude.Agent) Is Null));

This could be done with a subquery also.
I would recommend you using a table name other than 'database'.

:

SELECT database.agent, database.biller
GROUP BY database.agent, database.biller
HAVING (((database.biller) Not In (Biller#)));

KARL DEWEY wrote:
Post your SQL and I will try to edit to fit your requirement.

:

Hello,

I need to exclude results in a query, and I'm not sure how. Here's the
scenario:

I have an agent who processes payments from multiple billers. I want
the query to return all agents who do not process payments for a
specific biller. I've tried doing a Not In on the biller, but it still
brings back all agents since they all process other billers too.

I appreciate any help.

Thanks,

Nate
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Open the query in design view and check the joins. Otherwise I do not know.

Maybe copy and paste the SQL back in a post.

[email protected] said:
I tried w/ and w/o the hard return, same error for both.

As in there should or shouldn't be one? I'm assuming not.

KARL said:
Check the pasted SQL for a hard return following the equal sign.

:

I received a syntax error (missing operation) on 'Database.Agent =
StevensExclude.Agent'

I appreciate any additional help.



KARL DEWEY wrote:
Use two queries. First one here is named StevensExclude --
SELECT Database.Agent, Database.Biller
FROM [Database]
WHERE (((Database.Biller)=[Enter Biller to exclude]));

SELECT Database.Agent, Database.Biller
FROM [Database] LEFT JOIN StevensExclude ON Database.Agent =
StevensExclude.Agent
WHERE (((StevensExclude.Agent) Is Null));

This could be done with a subquery also.
I would recommend you using a table name other than 'database'.

:

SELECT database.agent, database.biller
GROUP BY database.agent, database.biller
HAVING (((database.biller) Not In (Biller#)));

KARL DEWEY wrote:
Post your SQL and I will try to edit to fit your requirement.

:

Hello,

I need to exclude results in a query, and I'm not sure how. Here's the
scenario:

I have an agent who processes payments from multiple billers. I want
the query to return all agents who do not process payments for a
specific biller. I've tried doing a Not In on the biller, but it still
brings back all agents since they all process other billers too.

I appreciate any help.

Thanks,

Nate
Click to expand...
Click to expand...
Click to expand...
 
S

stephens.nate

Okay, I got it to work, but it still returned Agents even if they had
the biller ID associated with them.

Perhaps I should explain, every biller for each agent has its own line.
So if Biller A is in 10 Agents, there will be 10 entires, with the
biller # being the same in each. I think what's happening is it
"excludes" the line where the biller # is in, but still ends up
grabbing the agent because of the other records that have same agent
but with a different biller.

Any thoughts?


KARL said:
Open the query in design view and check the joins. Otherwise I do not know.

Maybe copy and paste the SQL back in a post.

[email protected] said:
I tried w/ and w/o the hard return, same error for both.

As in there should or shouldn't be one? I'm assuming not.

KARL DEWEY wrote:
Check the pasted SQL for a hard return following the equal sign.

:

I received a syntax error (missing operation) on 'Database.Agent =
StevensExclude.Agent'

I appreciate any additional help.



KARL DEWEY wrote:
Use two queries. First one here is named StevensExclude --
SELECT Database.Agent, Database.Biller
FROM [Database]
WHERE (((Database.Biller)=[Enter Biller to exclude]));

SELECT Database.Agent, Database.Biller
FROM [Database] LEFT JOIN StevensExclude ON Database.Agent =
StevensExclude.Agent
WHERE (((StevensExclude.Agent) Is Null));

This could be done with a subquery also.
I would recommend you using a table name other than 'database'.

:

SELECT database.agent, database.biller
GROUP BY database.agent, database.biller
HAVING (((database.biller) Not In (Biller#)));

KARL DEWEY wrote:
Post your SQL and I will try to edit to fit your requirement.

:

Hello,

I need to exclude results in a query, and I'm not sure how. Here's the
scenario:

I have an agent who processes payments from multiple billers. I want
the query to return all agents who do not process payments for a
specific biller. I've tried doing a Not In on the biller, but it still
brings back all agents since they all process other billers too.

I appreciate any help.

Thanks,

Nate
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Post you data sample and results.

This is my data sample --
Agent Biller
1 x
1 y
1 z
2 x
2 y
3 y
3 z
5 x
7 x
This is my results on entering "x" at the prompt.
Agent Biller
3 y
3 z
This is my results on entering "z" at the prompt.
Agent Biller
2 x
2 y
5 x
7 x


[email protected] said:
Okay, I got it to work, but it still returned Agents even if they had
the biller ID associated with them.

Perhaps I should explain, every biller for each agent has its own line.
So if Biller A is in 10 Agents, there will be 10 entires, with the
biller # being the same in each. I think what's happening is it
"excludes" the line where the biller # is in, but still ends up
grabbing the agent because of the other records that have same agent
but with a different biller.

Any thoughts?


KARL said:
Open the query in design view and check the joins. Otherwise I do not know.

Maybe copy and paste the SQL back in a post.

[email protected] said:
I tried w/ and w/o the hard return, same error for both.

[email protected] wrote:
As in there should or shouldn't be one? I'm assuming not.

KARL DEWEY wrote:
Check the pasted SQL for a hard return following the equal sign.

:

I received a syntax error (missing operation) on 'Database.Agent =
StevensExclude.Agent'

I appreciate any additional help.



KARL DEWEY wrote:
Use two queries. First one here is named StevensExclude --
SELECT Database.Agent, Database.Biller
FROM [Database]
WHERE (((Database.Biller)=[Enter Biller to exclude]));

SELECT Database.Agent, Database.Biller
FROM [Database] LEFT JOIN StevensExclude ON Database.Agent =
StevensExclude.Agent
WHERE (((StevensExclude.Agent) Is Null));

This could be done with a subquery also.
I would recommend you using a table name other than 'database'.

:

SELECT database.agent, database.biller
GROUP BY database.agent, database.biller
HAVING (((database.biller) Not In (Biller#)));

KARL DEWEY wrote:
Post your SQL and I will try to edit to fit your requirement.

:

Hello,

I need to exclude results in a query, and I'm not sure how. Here's the
scenario:

I have an agent who processes payments from multiple billers. I want
the query to return all agents who do not process payments for a
specific biller. I've tried doing a Not In on the biller, but it still
brings back all agents since they all process other billers too.

I appreciate any help.

Thanks,

Nate
Click to expand...
Click to expand...
Click to expand...
 
Top