This is the SQL code for starters. - SELECT DISTINCTROW [Qry_CRM_MNGT_150+_No_Action_NEW].CYCLE_DATE, [Qry_CRM_MNGT_150+_No_Action_NEW].SERIES_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_NAME, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].INTERNAL_LOAN_ID, [Qry_CRM_MNGT_150+_No_Action_NEW].ARCC_LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].LIEN_STATUS_CODE, [Qry_CRM_MNGT_150+_No_Action_NEW].MaxOfACTL_END_PRIN_BAL, [Qry_CRM_MNGT_150+_No_Action_NEW].REPORTED_ACTION_CODE, [Qry_CRM_MNGT_150+_No_Action_NEW].MONTHS_DELQ, IIf([COMMENT_CODE]="CX",[COMMENT_DATE_TIME],"") AS NEW_COMMENT_DATE_TIME, (IIf([COMMENT_CODE]="CX",[STAFF_NAME],"")) AS NEW_STAFF_NAME, (IIf([COMMENT_CODE]="CX",[COMMENT_TEXT],"")) AS NEW_COMMENT_TEXT, (IIf([COMMENT_CODE]="CX","CX","")) AS NEW_COMMENT_CODE
-
FROM ([Qry_CRM_MNGT_150+_No_Action_NEW] LEFT JOIN MARS_COMMENTS_INFO ON [Qry_CRM_MNGT_150+_No_Action_NEW].INTERNAL_LOAN_ID = MARS_COMMENTS_INFO.INTERNAL_LOAN_ID) LEFT JOIN MARS_IFS_STAFF_INFO ON MARS_COMMENTS_INFO.COMMENT_AUTHOR = MARS_IFS_STAFF_INFO.INTERNAL_STAFF_LOGON
-
GROUP BY [Qry_CRM_MNGT_150+_No_Action_NEW].CYCLE_DATE, [Qry_CRM_MNGT_150+_No_Action_NEW].SERIES_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_NAME, [Qry_CRM_MNGT_150+_No_Action_NEW].SERVICER_LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].INTERNAL_LOAN_ID, [Qry_CRM_MNGT_150+_No_Action_NEW].ARCC_LOAN_NBR, [Qry_CRM_MNGT_150+_No_Action_NEW].LIEN_STATUS_CODE, [Qry_CRM_MNGT_150+_No_Action_NEW].MaxOfACTL_END_PRIN_BAL, [Qry_CRM_MNGT_150+_No_Action_NEW].REPORTED_ACTION_CODE, [Qry_CRM_MNGT_150+_No_Action_NEW].MONTHS_DELQ, IIf([COMMENT_CODE]="CX",[COMMENT_DATE_TIME],""), (IIf([COMMENT_CODE]="CX",[STAFF_NAME],"")), (IIf([COMMENT_CODE]="CX",[COMMENT_TEXT],"")), (IIf([COMMENT_CODE]="CX","CX",""));
The purpose of this query is to identify the records that meet the specified parameters, with it's respective comment (s)if there is(are) any, and also the those records that have no comment (null) but meet the parameters. The problem I have is that for records that have a comment(s), and additional record shows up in the results with null comment value.
I am trying to figure out how I can eliminate the duplicate record with the Null comment value if that same record has a comment already showing in the results.
1  4887  nico5038 3,080
Recognized Expert Specialist
Looks to me you can have multiple comments.
If that's the case and one has a code <> CX it will add an empty row.
One solution is to use a groupby query to filter the comments (MARS_COMMENTS_ INFO) for "CX" and select the comment of the MAX(COMMENT_DAT E_TIME). (So first a groupby on INTERNAL_LOAN_I D with the MAX(COMMENT_DAT E_TIME) for the "CX" comments and that query JOINED by INTERNAL_LOAN_I D and MAX(COMMENT_DAT E_TIME) to get the (single) "CX" comment.
When the COMMENT_DATE_TI ME is unique this will work, otherwise I would add also a MAX(COMMENT_TEX T).
Getting the idea ?
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Schlauberger |
last post by:
I am currently developing a VB.Net App that utilized an MS Access file to
store the data. My Report data source is a Parameter Query in MS Access when
the report loads the parameter is prompted twice? The result set in the
report ends up with duplicate records. How can I overcome this Problem?
Sample Code:
Private Sub frmCryReport_Load(...)...
Dim parPN as New ParameterDiscreteValue
Dim parList as New ParameterFields
|
by: Rado |
last post by:
Hi All,
This might quite simple process for some but I am finding it really
difficult to do. What is required is not a standard Duplicate query but a
variation on it. For example I have following records:
ID_Number Date Name Amount
100 1/1/02 Andrew $100.00
101 1/1/02 Andrew $100.00
102 2/1/02 Andrew $100.00
|
by: ms |
last post by:
Access 2000:
I am trying to delete duplicate records imported to a staging table leaving one
of the duplicates to be imported into the live table. A unique record is based
on a composite key of 3 fields (vehicleID, BattID, and ChgHrs). VehicleID and
BattID are a TEXT datatype and ChrHrs are a number(long int.) datatype. Since
records to be imported can have duplicate records of the composite key I need to
clean all but one of the...
|
by: Andrew Chanter |
last post by:
I have been working with an A97 database that performs a data processing
function. It imports data from a flat text file then uses a dao transaction
that executes a number of sql statements (about 30 in all) to append to
various tables, delete records, copy from table A to table B etc, ultimately
downloading to a series of text files that contain 'processed data'.
The issue is that on the odd occasion (this process is executed every...
|
by: sara |
last post by:
I have a procedure to automate bringing several Excel files into our
Access tables, on a daily basis.
The problem is that if the user has a problem, and tries to run the
import again (maybe 3 files imported then there was a data problem and
they want to re-import after fixing the problem), I can't get the Error
handling to fire if the user is attempting to import duplicate key
records.
Message when I try to import records already on...
| | |
by: Manish |
last post by:
The topic is related to MySQL database.
Suppose a table "address" contains the following records
-------------------------------------------------------
| name | address | phone |
-------------------------------------------------------
| mr x | 8th lane | 124364 |
| mr x | 6th lane | 435783 |
| mrs x | 6th lane | 435783 |
|
by: pooh80133 |
last post by:
Hi!
I am pasting my SQL code at the end of this message.
I am trying to use SELECT DISTINCT in a query, but I am a beginner for using Access. Right now I have duplicate ID's (Indiv ID) in my table (All Illumina ITPR3), but the rest of the fields don't necessarily match (Haplotype, DR genotype).
If I run SELECT DISTINCT on "Indiv ID", I can't get the other fields to be displayed. If I run SELECT DISTINCT on all the fields I want...
|
by: nethravathy |
last post by:
Hi,
The following table namely elcbtripselect contains 5147 records.I want to know wether this table contains duplicate records or not.
I tried with following query
1)SELECT elcbtripselect.ELCBTRIP_voltsMIN, elcbtripselect.ELCBTRIP_voltsMAX, elcbtripselect.ELCBTrip_is_partwinding, elcbtripselect.ELCBTrip_is_ydelta, elcbtripselect.ELCBTRIP_starter_size, elcbtripselect.ELCBTRIP_UnitFunction, elcbtripselect.ELCBTRIP_strcb_speedi_frame_ty,...
|
by: BSB |
last post by:
Hi,
I generate a "Find Duplicate" query for one table that will return some records
I want to capture those records in my VB code.....
This is my code...pls guide me how to proceed....
***************************************************************************************
adocmd.CommandText = sQueryName
adocmd.CommandType = adCmdStoredProc
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
