Union query and eliminating duplicates

[brianr]

I'm advanced enough to use SQL to create the union query between two queries I've created (don't laugh...I know it's copy/paste and using "UNION"), but essentially both tables provide the SAME 10 columns of information (including and specifically, account number, customer name, and balance). So, the UNION query Data Sheet view includes 200 lines, 100 lines from each; thus, there are duplicates with most of the combined query. The difference is most (95%) are repeats from previous report (i.e. have contents in the "Comments" column), and the others, because they are new additions, and therefore do not have "Comments" (a linked column, so if a customer/account combo is new, it won't be included in original main query.

So I have mostly duplicate customer/account combos, one line with a comment, one without, and the remaining handful (5-10 lines total) being single line items (because they are new additions). How do I get eliminate the customer/account lines where there are only the previous combos with comments, and the new additions (obviously without comments) remaining? Using a "eliminate" duplicate query? Entering more complex SQL language or select criteria ("WHERE", etc)? Appreciate any help!

 

[bstory84]

Have you tried a "LEFT JOIN" ?

 

[brianr]

Have you tried a "LEFT JOIN" ?

I think that's a great start, and maybe less advanced than I thought. Please elaborate, including how/where I should put the LEFT JOIN, and what afterwards. Less C.S., more Access education! (Btw...appreciate all help...just need more guidance)

 

[bstory84]

Need more detail. Can you post your SQL . I can modify and repost

 

[xenou]

Union discards duplicates by default. I think you need to clarify your language. A duplicate means all fields are the same. The entire row (or at least the columns you select in your query) must be exactly the same). So if you use Union you will have no duplicates.

 

[brianr]

My SQL Language. And yes, I apologize: I meant DUPLICATE all other information (account numbers, customer name, all other fields except Comments (some with, some without) due to the nature that if an account/customer was on a previous report, it has a Comment; if it is new to the cumulative report, it will not have a comment. So if an account/name was on the UNION of a previous report and the new report (because it did not go away, new accounts were just added), then it will have two similar (not exact DUPLICATE) lines (again, one with a comment from old report, and the new information that I'm using the previous COMMENT as a basis). That help? Thanks!

SELECT [Loan Codes].Category, [LNRSKT 083115].[Account-Note#], [LNRSKT 083115].Name, [Comments 073115].Comments, [LNRSKT 083115].Type, [LNRSKT 083115].[Note Officer Name], [LNRSKT 083115].[Note Bankruptcy Code], [LNRSKT 083115].[Note Foreclosure Indicator], [LNRSKT 083115].[Note Accrual Indicator], [LNRSKT 083115].[Days Past Due], [LNRSKT 083115].[Past Due Balance], [LNRSKT 083115].[Note Bank Share Ledger Balance], [LNRSKT 083115].[Note Bank Share Original Balance], [LNRSKT 083115].Issued, [LNRSKT 083115].Maturity, [LNRSKT 083115].[Risk Rating], [LNRSKT 083115].[Note Collateral Description], [LNRSKT 083115].[LTV (final)], [LNRSKT 083115].[Original Appraisal Date], [LNRSKT 083115].[Note Appraisal Value], [LNRSKT 083115].[Current Appraisal date], [LNRSKT 083115].[Current Appraisal Value], [LNRSKT 083115].[Note Current Interest Rate], [LNRSKT 083115].[Note Fixed Variable Rate Indicator], [LNRSKT 083115].[Note Unapplied Funds Balance], [LNRSKT 083115].[Note Escrow Balance]
FROM [Comments 073115] INNER JOIN ([Loan Codes] INNER JOIN [LNRSKT 083115] ON [Loan Codes].[Loan Code] = [LNRSKT 083115].[Type Codes (manual)]) ON [Comments 073115].[Account-Note#] = [LNRSKT 083115].[Account-Note#]
WHERE ((([LNRSKT 083115].[Risk Rating])="5" Or ([LNRSKT 083115].[Risk Rating])="6") AND (([LNRSKT 083115].[Branch Number])="1001" Or ([LNRSKT 083115].[Branch Number])="1008"))
ORDER BY [Loan Codes].Category, [LNRSKT 083115].Name
UNION SELECT [Loan Codes].Category, [LNRSKT 083115].[Account-Note#], [LNRSKT 083115].Name, "" AS Comments, [LNRSKT 083115].Type, [LNRSKT 083115].[Note Officer Name], [LNRSKT 083115].[Note Bankruptcy Code], [LNRSKT 083115].[Note Foreclosure Indicator], [LNRSKT 083115].[Note Accrual Indicator], [LNRSKT 083115].[Days Past Due], [LNRSKT 083115].[Past Due Balance], [LNRSKT 083115].[Note Bank Share Ledger Balance], [LNRSKT 083115].[Note Bank Share Original Balance], [LNRSKT 083115].Issued, [LNRSKT 083115].Maturity, [LNRSKT 083115].[Risk Rating], [LNRSKT 083115].[Note Collateral Description], [LNRSKT 083115].[LTV (final)], [LNRSKT 083115].[Original Appraisal Date], [LNRSKT 083115].[Note Appraisal Value], [LNRSKT 083115].[Current Appraisal date], [LNRSKT 083115].[Current Appraisal Value], [LNRSKT 083115].[Note Current Interest Rate], [LNRSKT 083115].[Note Fixed Variable Rate Indicator], [LNRSKT 083115].[Note Unapplied Funds Balance], [LNRSKT 083115].[Note Escrow Balance]
FROM [Loan Codes] INNER JOIN [LNRSKT 083115] ON [Loan Codes].[Loan Code] = [LNRSKT 083115].[Type Codes (manual)]
WHERE ((([LNRSKT 083115].[Risk Rating])="5" Or ([LNRSKT 083115].[Risk Rating])="6") AND (([LNRSKT 083115].[Branch Number])="1001" Or ([LNRSKT 083115].[Branch Number])="1008"))
ORDER BY [Loan Codes].Category, [LNRSKT 083115].Name;

 

[bstory84]

Where is my attempt to simplify if it does not produce the results you want, I apologize. No way to test without re-creating your tables.

 SELECT [Loan Codes].Category, [LNRSKT 083115].[Account-Note#], [LNRSKT 083115].Name,
[Comments 073115].Comments, [LNRSKT 083115].Type, [LNRSKT 083115].[Note Officer Name],
[LNRSKT 083115].[Note Bankruptcy Code],
[LNRSKT 083115].[Note Foreclosure Indicator],
[LNRSKT 083115].[Note Accrual Indicator],
[LNRSKT 083115].[Days Past Due], 
[LNRSKT 083115].[Past Due Balance],
[LNRSKT 083115].[Note Bank Share Ledger Balance],
[LNRSKT 083115].[Note Bank Share Original Balance], 
[LNRSKT 083115].Issued, [LNRSKT 083115].Maturity, 
[LNRSKT 083115].[Risk Rating], [LNRSKT 083115].[Note Collateral Description],
[LNRSKT 083115].[LTV (final)], [LNRSKT 083115].[Original Appraisal Date],
[LNRSKT 083115].[Note Appraisal Value], [LNRSKT 083115].[Current Appraisal date],
[LNRSKT 083115].[Current Appraisal Value], [LNRSKT 083115].[Note Current Interest Rate], 
[LNRSKT 083115].[Note Fixed Variable Rate Indicator], [LNRSKT 083115].[Note Unapplied Funds Balance], [LNRSKT 083115].[Note Escrow Balance]
FROM [Loan Codes] LEFT JOIN ([LNRSKT 083115] LEFT JOIN [Comments 073115]
ON [Comments 073115].[Account-Note#] = [LNRSKT 083115].[Account-Note#])
ON [Loan Codes].[Loan Code] = [LNRSKT 083115].[Type Codes (manual)]
WHERE (
(
([LNRSKT 083115].[Risk Rating])="5" Or ([LNRSKT 083115].[Risk Rating])="6")
AND (([LNRSKT 083115].[Branch Number])="1001" Or ([LNRSKT 083115].[Branch Number])="1008")
)
ORDER BY [Loan Codes].Category, [LNRSKT 083115].Name;

 

[xenou]

So, I'm assuming you don't care about the comments in your results ... they are excluded?

 

[brianr]

So, I'm assuming you don't care about the comments in your results ... they are excluded?

I for sure do. I created a cumulative Comments table for every account/customer from the previous report, so that I could link them in a query to attach with a new data download (with new balances and other numeric data, but DOES NOT include Comments...these are manual additions). But because I am using old information (i.e., previous comments) with new information (i.e., numeric data, and additionally, new accounts that would not have had comments), I had to UNION them to get a combined group. Now, I just want to filter out the data where the old information (account/customer) with blanks in the Comment field (since they are repeated from previous report WITH comments), and have unique account/customer combos, both previous and new.

 

[brianr]

Appreciate! I will insert into my SQL page, get results, and get back to you/blogsphere with what occurs. Thanks for being diligent.