Results 1 to 6 of 6

Duplicating Rows because of several reference numbers

  1. #1
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34

    Duplicating Rows because of several reference numbers

    All,



    I have orders that can have several reference numbers assigned to each order. When I pull the query, the result duplicates the orders listed because it lists that order as a unique result for every reference number it has (example below: order 825013416 is listed 3 times because the customer provided three reference numbers for that order). I know this is what it's supposed to do but I'm having a brain fart on how to change it so that it lists each ref type as it's own column and the respective ref number under that ref type column. Screen shots of what is currently happening, what I would like to happen, and the access query are attached and below is typed sql view. Please let me know your thoughts. Thanks.

    Order Number Latest Pickup Date ref_type ref_number825013416 5/1/2019 14:00 RID 786175160
    825013416 5/1/2019 14:00 SO 477730-000010
    825013416 5/1/2019 14:00 BOL 80739462

    Click image for larger version. 

Name:	2019-06-17 18_30_36-Manage Attachments - Microsoft Access Forums.png 
Views:	11 
Size:	23.5 KB 
ID:	38769

    Click image for larger version. 

Name:	2019-06-17 18_30_02-Microsoft Excel - Referemce Number duplicating rows.png 
Views:	11 
Size:	12.0 KB 
ID:	38770
    Click image for larger version. 

Name:	Access create query view.png 
Views:	11 
Size:	18.6 KB 
ID:	38771


    SQL view of that query:
    SELECT dbo_orderheader.ord_number AS [Order Number], dbo_orderheader.ord_origin_latestdate AS [Latest Pickup Date], dbo_referencenumber.ref_type, dbo_referencenumber.ref_number
    FROM dbo_Employees, dbo_orderheader INNER JOIN dbo_referencenumber ON dbo_orderheader.ord_hdrnumber = dbo_referencenumber.ord_hdrnumber
    GROUP BY dbo_orderheader.ord_number, dbo_orderheader.ord_origin_latestdate, dbo_referencenumber.ref_type, dbo_referencenumber.ref_number
    HAVING (((dbo_orderheader.ord_number) Like "825*") AND ((dbo_orderheader.ord_origin_latestdate) Between [Start Date] And [End Date]))
    ORDER BY dbo_orderheader.ord_number;

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,034
    Desired output looks like a CROSSTAB query. There is a query designer for that. CROSSTAB can be emulated with IIf() expressions in aggregate query.
    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
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    I've looked up you tube videos but am not understanding how to do this. Looks like this works for summing numbers but not just listing the result. I'm still lost.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,034
    Instead of Sum use Max in the CROSSTAB design.

    ord_number and ord_origin_latestdate would be Row Headers

    ref_type would be Column Header

    ref_number would be Value with Max function (Max, Min, First, Last should all give same result in this case)


    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
    rfigueroa1976 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2017
    Posts
    34
    I've gotten the query to finally run with your guidance but, there are no results and I know data exists... The other thing that stopped working is the Between function on the criteria for my date range (Between [Start Date] And [End Date]).

    Click image for larger version. 

Name:	2019-06-19 14_54_43-newtmw - Remote Desktop Connection.png 
Views:	6 
Size:	17.1 KB 
ID:	38807

    Click image for larger version. 

Name:	2019-06-19 14_55_08-newtmw - Remote Desktop Connection.png 
Views:	6 
Size:	4.3 KB 
ID:	38806

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,034
    Use # instead " mark for date parameters. Use = instead of LIKE.

    = #05/08/2019#

    Dynamic parameters in CROSSTAB require special handling. Review http://allenbrowne.com/ser-67.html

    Since you have only 3 ref_type values, an alternative to CROSSTAB is to emulate with IIf() expressions. Example https://www.accessforums.net/showthread.php?t=77153
    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.

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

Similar Threads

  1. Replies: 10
    Last Post: 02-14-2018, 06:21 AM
  2. Replies: 2
    Last Post: 12-29-2017, 11:01 AM
  3. Automatic Reference ID Numbers in a Query
    By Phil-AND in forum Queries
    Replies: 10
    Last Post: 01-14-2015, 03:31 PM
  4. Replies: 1
    Last Post: 05-01-2013, 10:23 AM
  5. Duplicating numbers....why??
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 01-21-2011, 01:32 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
  •  
Tech Forums: Microsoft Office Forums