Results 1 to 7 of 7
  1. #1
    ph03nix42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    3

    Question on how to combine two excel files, but stop a duplication issue

    I have 2 excel files I'm importing into Access 2010. These contain similar but not 100% equal sets of data that is potentially duplicated up to 7 times before it moves to the next "record".



    In my design, I've linked the Names as the primary identifier, but when I go to run the query and see what the results are, i'm getting duplicated data in a second field.

    I've attached a picture of what is being designed and what the results are.

    Click image for larger version. 

Name:	query design.JPG 
Views:	9 
Size:	42.3 KB 
ID:	28890

    In this particular case, I need the TMT data (which contains more data 9 times out of 10) as the primary source of the output. With this set of results, I should have a situation where the output has non linked data from TMT being passed to the newly combined excel output as seen here.

    Click image for larger version. 

Name:	results.JPG 
Views:	9 
Size:	202.6 KB 
ID:	28891

    As you can see in the "assetclass" Field, it's duplicating the "segment/class/asset" data x-number of times when it should be just matching it in a 1:1 situation.

    I've attempted in the past to link the assetclass & segment/class/asset to each other, but I get an error with that design stating that I need to generate the results and then build a new query.

    Any help would be appreciated in building this set of data.

    Thanks. - Access Newbie

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    As you can see in the "assetclass" Field, it's duplicating the "segment/class/asset" data x-number of times when it should be just matching it in a 1:1 situation.
    I don't see where the segment/class/asset is being duplicated.

  3. #3
    ph03nix42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    3
    If you look at the AssetClass & Segment/Class/Asset and then also the WT & Allocation columns, you'll see that the first sets of data are being duplicated.

    IE. CASH & CASH EQUIVALENTS are being duplicated 5 times where it should be matching 1:1 vs the Segment/Class/Asset column

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    (Waiting for someone else to jump in!) I just can't see it, I'm afraid.

    Explain the line that is highlighted: for each name you want to see asset class repeated, segment/class/asset once per asset class? What is "wt" and "allocation"?

    Post your database, or a sample of the data, it may help.

  5. #5
    ph03nix42 is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    May 2017
    Posts
    3
    The problem is that I don't want the Asset Class field to duplicate, I want it to do exactly as the Segment/Class/Asset column data is posted, 1 time, with each "type"

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Looking at your data, I do not see any duplicates either. A duplicate occurs when all the fields are the same. I do not see any rows in your example where all 6 fields from one record exactly match another record.

    I think your problem may be with your relationship, in which you have a one-to-many relationship when you really want a one-to-one relationship. You may need another field in your join.
    Of course, without seeing examples of your table data before and what the expected results should look like afterwards, it is difficult to direct you exactly what you need to do to correct that.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your wt data is being duplicated as well, but you don't mention it. This is the way it's supposed to look based on the data you have and the type of query you're building. Look at it this way: in the first 5 rows, based on the fields selected and the record taken as a whole c&ce is repeating 5 times because there are that many combinations when all of the selected fields are taken into consideration. There are 2 records where the values of wt and allocation are the same, but their segment values are different. Put another way, if you want to see 5 values for a child field (segment) and ask to see the parent field as well (asset class) how can you expect the parent to show up only once?

    You could try an equal join, but I don't foresee that helping. Try playing with the DISTINCT or DISTINCT ROW predicates (property sheet > Unique Values OR Unique Records; cannot have both). You may have to create some sort of Totals query in order to group by. I don't see the point in including the joined field on the many side. It's all the same values.
    Special characters in your field names is not good practice (save for maybe the underscore). It's also odd to see the same value in two or more fields (c&ce)
    Last edited by Micron; 05-26-2017 at 01:01 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 13
    Last Post: 06-06-2017, 01:43 PM
  2. Replies: 1
    Last Post: 02-28-2017, 04:29 PM
  3. BAT command to combine multiple files
    By dekutoski in forum Import/Export Data
    Replies: 1
    Last Post: 08-09-2013, 08:32 AM
  4. Stop duplication of appointment
    By cheyanne in forum Forms
    Replies: 48
    Last Post: 06-06-2012, 06:59 AM
  5. Replies: 1
    Last Post: 02-21-2011, 09:55 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