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
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;