Query API no APM

[KathyCo]

I need to write a query that gives me all APIs with no APMs (API and APM are source code fields). The common field would be the invoice.

API = paid invoices
APM = voided invoices

So the objective is to get a list of all invoices that have not been voided. The starting code is listed below.

SELECT dbo_gl_history.j_jnl_source AS Source, dbo_gl_history.a_org AS Org, dbo_gl_history.a_object AS [Object], dbo_gl_history.a_project AS Project, dbo_gl_history.j_jnl_year_period AS YrPd, dbo_gl_history.a_journal_number AS Journal, dbo_gl_history.h_ref1_vendor AS Vendor, dbo_gl_history.h_ref2_po_no AS PO, dbo_gl_history.h_ref3_invoice_no AS Invoice, dbo_gl_history.h_effective_date AS [Eff Date], dbo_gl_history.h_gross_amount AS Amount
FROM dbo_gl_history
WHERE (((dbo_gl_history.j_jnl_source)="API" Or (dbo_gl_history.j_jnl_source)="APM") AND ((dbo_gl_history.a_object) Like "7*") AND ((dbo_gl_history.a_fund_seg1) Like "3*" Or (dbo_gl_history.a_fund_seg1) Like "4*"))
ORDER BY dbo_gl_history.a_org, dbo_gl_history.a_object, dbo_gl_history.a_project;



Any help you give would be greatly appreciated.

Thanks,

Kathy

[ranman256]

make a query that pulls all invoices not APM
then query off that to get APIs.

[ssanfu]

So the objective is to get a list of all invoices that have not been voided.

voided invoices = "APM"

If "APM" means the invoice is Void, why do you have it as criteria in the WHERE clause??
Can't you use:

WHERE dbo_gl_history.j_jnl_source)="API" AND dbo_gl_history.a_object Like "7%" AND (dbo_gl_history.a_fund_seg1) Like "3%" Or dbo_gl_history.a_fund_seg1 Like "4%")

With SQL, the wildcards are:

%	A substitute for zero or more characters (Access is "*")
_	A substitute for a single character (Access is "?")

[KathyCo]

I found a way to work around this based on your comment. I just matched it to the invoice table and told it not to include a status of void. I was hoping there was a way in SQL to say (just using the one journal entry table) - if you have a journal entry source code "API" for invoice "12345" with a journal entry source code "APM", then do not include it. I have this scenario to come up a lot with other queries and was hoping you could tell me away to do it this way. I am always having to come up with work-arounds like the one I did here and was just hoping there was a more efficient way.

[KathyCo]

If I asked it just to include the API, it would still include the voided invoice. API and APM are source codes for journal entries; they are not statuses. I found a way around it by matching the invoice number to the invoice header table and telling it not to include invoice numbers with a V (void) status. Thanks.

[ssanfu]

Without seeing the tables/relationships and having some sample data, it is hard give a more specific/good answer.

Glad you were able to get it working....