Exclude Records via Join?

A

awalker

Hi All,
I have a make table Query that pulls records which include invoice numbers
(table name is PromoTableAgentIncentive). I have a query
(Promo_AgentIncentive_Exclude) that contains invoice numbers that are in the
PromoTableAgentIncentive but should be excluded from the final product. How
do I join these fields so that it excludes them? I have a feeling that it
can only be done in SQL. I do not know SQL very well (only from copying and
pasting and then modifying for current query) but am trying to learn. I've
copied the SQL for the query below but the join is not working. Any help
would be appreciated. Thank you.

SELECT PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name], PromoTableAgentIncentive.[Agent
Name], PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID

FROM PromoTableAgentIncentive LEFT JOIN Promo_AgentIncentive_Exclude ON
PromoTableAgentIncentive.InvoiceNumber = Promo_AgentIncentive_Exclude.[Bad
Invoices]
GROUP BY PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name], PromoTableAgentIncentive.[Agent
Name], PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID;
 
J

John Spencer

Really close all you need to do is add a where clause to exclude any
records where there is a match between the two tables and the field(s)
you joined on.

By the way - good decision to post the SQL statement. It makes it a lot
simpler to trouble shoot the problem.

SELECT PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name]
, PromoTableAgentIncentive.[Agent Name]
, PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID

FROM PromoTableAgentIncentive
LEFT JOIN Promo_AgentIncentive_Exclude ON
PromoTableAgentIncentive.InvoiceNumber =
Promo_AgentIncentive_Exclude.[Bad Invoices]


WHERE Promo_AgentIncentive_Exclude.[Bad Invoices] is Null


GROUP BY PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name], PromoTableAgentIncentive.[Agent
Name], PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi All,
I have a make table Query that pulls records which include invoice numbers
(table name is PromoTableAgentIncentive). I have a query
(Promo_AgentIncentive_Exclude) that contains invoice numbers that are in the
PromoTableAgentIncentive but should be excluded from the final product. How
do I join these fields so that it excludes them? I have a feeling that it
can only be done in SQL. I do not know SQL very well (only from copying and
pasting and then modifying for current query) but am trying to learn. I've
copied the SQL for the query below but the join is not working. Any help
would be appreciated. Thank you.

SELECT PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name], PromoTableAgentIncentive.[Agent
Name], PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID

FROM PromoTableAgentIncentive LEFT JOIN Promo_AgentIncentive_Exclude ON
PromoTableAgentIncentive.InvoiceNumber = Promo_AgentIncentive_Exclude.[Bad
Invoices]
GROUP BY PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name], PromoTableAgentIncentive.[Agent
Name], PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID;
Click to expand...
 
A

awalker

Marshall, thank you for your help. When I add this to the end I'm recieving
an error "Syntax error (missing operatior) in query expression "WHERE
Promo_AgentIncentive_Exclude.[Bad Inovices] Is Nul'. Are you able to assist
with this?

The reason for the Group By is to eliminate duplicates. The invoices can be
in the table more than one time as this number does not change. It brings
over duplicate payments. I'm self taught (and not so well as you can see) so
I used the group by, I will look up the DISTINCT predicate, thanks for the
suggestion.



Marshall Barton said:
awalker said:
Hi All,
I have a make table Query that pulls records which include invoice numbers
(table name is PromoTableAgentIncentive). I have a query
(Promo_AgentIncentive_Exclude) that contains invoice numbers that are in the
PromoTableAgentIncentive but should be excluded from the final product. How
do I join these fields so that it excludes them? I have a feeling that it
can only be done in SQL. I do not know SQL very well (only from copying and
pasting and then modifying for current query) but am trying to learn. I've
copied the SQL for the query below but the join is not working. Any help
would be appreciated. Thank you.

SELECT PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name], PromoTableAgentIncentive.[Agent
Name], PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID

FROM PromoTableAgentIncentive LEFT JOIN Promo_AgentIncentive_Exclude ON
PromoTableAgentIncentive.InvoiceNumber = Promo_AgentIncentive_Exclude.[Bad
Invoices]
GROUP BY PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name], PromoTableAgentIncentive.[Agent
Name], PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID;
Click to expand...


Add:

WHERE Promo_AgentIncentive_Exclude.[Bad Invoices] Is Null

Why are you using a Totals (GROUP BY) query? You are not
using any aggregate functions so there is no need to for the
complications. If you are using it to eliminate duplicate
records, then either you have a flaw in your table design or
you should be using the DISTINCT predicate.
Click to expand...
 
M

Marshall Barton

awalker said:
Hi All,
I have a make table Query that pulls records which include invoice numbers
(table name is PromoTableAgentIncentive). I have a query
(Promo_AgentIncentive_Exclude) that contains invoice numbers that are in the
PromoTableAgentIncentive but should be excluded from the final product. How
do I join these fields so that it excludes them? I have a feeling that it
can only be done in SQL. I do not know SQL very well (only from copying and
pasting and then modifying for current query) but am trying to learn. I've
copied the SQL for the query below but the join is not working. Any help
would be appreciated. Thank you.

SELECT PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name], PromoTableAgentIncentive.[Agent
Name], PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID

FROM PromoTableAgentIncentive LEFT JOIN Promo_AgentIncentive_Exclude ON
PromoTableAgentIncentive.InvoiceNumber = Promo_AgentIncentive_Exclude.[Bad
Invoices]
GROUP BY PromoTableAgentIncentive.InvoiceDate,
PromoTableAgentIncentive.BookDate, PromoTableAgentIncentive.LocGroup,
PromoTableAgentIncentive.[Vendor Name], PromoTableAgentIncentive.[Agent
Name], PromoTableAgentIncentive.[Branch Name],
PromoTableAgentIncentive.PayID, PromoTableAgentIncentive.TravelType,
PromoTableAgentIncentive.Provider, PromoTableAgentIncentive.InvoiceNumber,
PromoTableAgentIncentive.RealUnits, PromoTableAgentIncentive.Data,
PromoTableAgentIncentive.Amount, PromoTableAgentIncentive.Year,
PromoTableAgentIncentive.PayID;
Click to expand...


Add:

WHERE Promo_AgentIncentive_Exclude.[Bad Invoices] Is Null

Why are you using a Totals (GROUP BY) query? You are not
using any aggregate functions so there is no need to for the
complications. If you are using it to eliminate duplicate
records, then either you have a flaw in your table design or
you should be using the DISTINCT predicate.
 
M

Marshall Barton

awalker said:
Marshall, thank you for your help. When I add this to the end I'm recieving
an error "Syntax error (missing operatior) in query expression "WHERE
Promo_AgentIncentive_Exclude.[Bad Inovices] Is Nul'. Are you able to assist
with this?
Click to expand...


There are two Ls in Null
 
Top