Results 1 to 12 of 12
  1. #1
    volzalum is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    7

    Help using 2 queries to remove duplicates and preserve memo field

    I have 3 data sets that are joined through a few relationships. It includes ~ 240 documents that are tied to ~260 commitments that are tied to ~150 systems. The data is in normalized tables in an Access 2007 database.

    I create a query [qComSys] that gives me 3 fields (columns) [Doc ID], [Commitment], and [System]. This query returns ~1700 unique records (rows) following the 1 to many to many relationship between the 3 fields. I need to create a summary query (or report) that tells me for this [Doc ID] I have these [Commitment] that have these [Systems] (created field as plural) where the systems are a "," delimited memo field (some cases exceed the text field limits). I will also be creating a summary of this [System] has these [Commitments] in a similar fashion.

    I create the "," delimited [Systems] using another query [qComSysJoin] that runs a function on the [qComSys] to create the memo field. The problem is this method yields 1700 records when I only want 260 (~1440 duplicates). Because of the memo field, I cannot use the “DISTINCT” SQL query or it truncates to the 255 characters. Everything I researched says to use 2 queries, but I cannot get it to work.

    I can create a DISTINCT query and return the 260 [Commitment]/[Doc ID] items, but I cannot get the second query to only add a single instance of the summary [Systems] field without truncating. Help please.



    I am also open to other methodologies such as report, etc, just trying to not create another actual table that stores the memo field.

    Thanks,

    -Dax

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How are you producing the comma separated string - using Allen Browne's ConcatRelated function? What is the criteria for the concatenation - CommitmentID?

    Try building report with the Distinct query as RecordSource. Call the concatenation function from a textbox on report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    volzalum is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    7
    The comma seperated memo field is created with a function that takes finds each match in a record set, sorts it, and combines it. Trying to call the function from the report doesn't get all of the systems.

    Any help on the two query resolution?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I need to analyse data and queries and report. If you want to provide db follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    volzalum is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    7
    Here is an snippet from the DB to Excel of the query [qComSysJoin] for 12 unique [Doc ID] & [Commitment] (out of the current ~280 items). It generates ~280 records because when the systems are added to the query before the concatenate function there are ~280 records. In Excel I can easily remove the duplicates, but I am trying to keep this in Access queries without creating new tables for these summaries. I do not want to create additional tables in the database. The second Tab [qRemoveDuplicates] shows the results I am looking for by querying the first Tab (in this case a single record for each of the 12 unique values). Note that several of the [Systems] field records are greater than 255 characters so the use of DISTINCT on the three fields does not work because it truncates the [Systems] field to a text field from the memo field.

    Microsoft says this can be accomplished with two queries, one to find the DISTINCT [Doc ID] and [Commitment] out of query [qComSysJoin] (say [qUniqueDocCom]) and another query to grab the [Systems] (say [qRemoveDuplicates]). I cannot figure out how to use [qUniqueDocCom] to create [qRemoveDuplicates] and return the results listed in the attachment.
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I am sure can be done but dataset needs a unique record ID which is not reflected in the data you provided. Review http://allenbrowne.com/subquery-01.html especially the example for Top N records per group.

    This is why I wanted to work with raw data, to understand your data structure. How did this 'duplication' happen, can it be avoided? I don't understand why running the concatenation function from report would not capture all systems.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    volzalum is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    7
    The dataset has the unique ID. The data provided is a query, queries don't generate unique IDs. I need to remove duplicates similar to this from several different queries, which is why I am asking how to use two queries as described in allenbrowne's write up.

    http://allenbrowne.com/ser-63.html

    "Uniqueness Since you asked the query to return only distinct values, Access must compare the memo field against all other records. The comparison causes truncation.
    Open the query's Properties Sheet and set Unique Values to No. (Alternatively, remove the DISTINCT key word in SQL View.)
    You may need to create another query that selects the distinct values without the memo, and then use it as the source for another query that retrieves the memo without de-duplicating."

    The first 2 columns of the second tab provided in the [qRemoveDuplicates] tab. How do I use that query as a source for another query to retrieve the memo without de-duplicating or truncating?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    As far as I can tell, can't. As I said need unique ID. I know query doesn't create unique ID but is there a unique ID in raw data? Can you include it in the query? I won't be able to help without understanding the data and that is easiest done by working with data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    volzalum is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    7
    I have included More snippets in the attachment. I can't include the DB for 2 reasons, 1 it is ~45 MB in size, and 2 I don't have it (or Access) on the computer I am at today so I have created the snippets as spreadsheets.

    This workbook has 7 Tabs:
    [tCommitments] Has all [Commitment] whether complete or not. [Commitment] links to a [Doc ID] in 1 [Doc ID] to many [Commitment] relationship where [Commitment] is unique ID
    [tP3IDs] has other data than provided here. Only Fields (columns) pertinent to this search are provided. Has [Commitment], [PP19 Type], [Prep PP19], [Active], and [Owner]. [Commitment] links to [Prep PP19] in a 1 [Commitment] to many [Prep PP19] relationship where [Prep PP19] is unique ID
    [tTasks] has other data than provided here. Only Fields (columns) pertinent to this search are provided. Has [Prep PP19], [Task ID], [System], and [Correct]. [Prep PP19] links to [Task ID] and [System] in a 1 [Prep PP19] to many [Task ID] and [System], and 1 [Task ID] to 1 [System] Relationship where [Task ID] is the Unique ID.
    [qComSys] is a query to return all systems for selected commitment (Uses other fields included in tables above to make selection). It uses the relationships between [tCommitments], [tP3IDs], and [tTasks] to return [Doc ID], [Commitment], and [System]
    [qComSysJoin] is a query that concatenates the [System] as a new field from the [qComSys] query
    [qUniqueDocCom] is a query that returns the DISTINCT [Doc ID] and [Commitment] from [qComSysJoin] query
    [qRemoveDuplicates] is the desired end result.

    The AllenBrowne Discussion (http://allenbrowne.com/ser-63.html) says to do this: "create another query that selects the distinct values without the memo, and then use it as the source for another query that retrieves the memo without de-duplicating"

    The Microsoft Memo Field Description says something similar but I can't find it now.

    Following the AllenBrowne quote, I should be able to use [qUniqueDocCom] to retrieve the [Systems] field from [qComSysJoin] as a new field in a new query that returns [qUniqueDocCom].[Doc ID], [qUniqueDocCom].[Commitment], and [New Systems Field]. I just cannot figure out how to only
    return a single [New Systems Field] for each [qUniqueDocCom] record.
    Attached Files Attached Files

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Providing me with workbook means I have to build an Access file to either import or link to the sheets so I can test queries. Not sure when I will get around to that. My home computer died this morning.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    volzalum is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    7
    Well I figured it was just a simple task that I was overlooking on how to use 1 query as a source for another query to retrieve values with. I surmised that for each source query record I would need to specify the 1st match in the lookup location rather than return all matches.

    I am really surprised that the use of 2 queries is not so simple, especially since that is what is described in all of the methodologies that discuss removing duplicates when a memo field is involved, I have just been unable to find any examples of their description, so I am going to attempt to rewrite the function that performs the concatenation (joins the fields).

    The original function I have used requires all of the fields to be in the same location (table, query, etc.) or to provide a single lookup value.

    I have now rewritten it to allow the lookup field to be a list in a separate location which should allow me to create the unique desired [Doc ID] & [Commitment] combo in query [qCommitments] (~260 records as subset of the [tCommitments] Table based on my desired selections) and use [Commitment] from [qCommitments] as the lookup value. I still need the [Commitment] & [System] fields in the bigger query [qComSys] (~1700 records) as the table to create the summarize from, but now the summarize query [qComSysJoin] will be based on [qCommitments] with the fields [qCommitments].[Doc ID], [qCommitments].[Commitment], and creating [Systems] field using the concatenate function (Should be same number of records as [qCommitments] or ~260).

    Previously, I was basing [qComSysJoin] on [qComSys] because the concatenate function couldn't use 2 separate locations so [qComSysJoin] was creating a [Systems] record for each record present which was ~1700. I will not be back in the office until Monday to test this out, but it should work.

    Alternatively, I could write a function to write all of the memo fields into an Array, then remove duplicates from the Array, then find the matching unique value from the source query. That new function could then be used to create a memo field [Systems] in the [qRemoveDuplicates] to only return 1 match for each unique record.

    Hopefully 1 of the two options will work if no one can expand on how to use AllenBrowne's description.

  12. #12
    volzalum is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    7
    With the updated concatenation process, I was able to create the query without duplicates. This currently works for my needs. This does not solve the question on how to use 1 query to retrieve values from another query, so if someone is searching this thread to try to resolve that issue, keep looking.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 03-03-2013, 07:13 PM
  2. remove Left Join duplicates
    By lokiluke in forum Queries
    Replies: 2
    Last Post: 09-16-2011, 04:53 AM
  3. Remove lines from Memo field
    By bvallez in forum Programming
    Replies: 5
    Last Post: 08-08-2011, 06:40 PM
  4. Remove Duplicates Based on Criteria
    By suryaprasad in forum Access
    Replies: 0
    Last Post: 04-07-2011, 10:50 PM
  5. How to remove duplicates
    By TonyBender in forum Access
    Replies: 0
    Last Post: 10-21-2009, 10:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums