Subquery to get distinct values ? (1 Viewer)

BeeJayEff · Jun 1, 2016

The following snippet works fine :

Code:

strSQL = "SELECT Customers.CustomerName & ', ' & Customers.AddressLine1 & ', '" & _
            " & Customers.Addressline2 & ', ' & Customers.City & ', ' & Customers.StateOrCounty & ', '" & _
            " & Customers.ZipOrPostcode, SalesRecords.TrackingNumber, SalesRecords.Paid FROM SalesRecords" & _
            " INNER JOIN Customers ON SalesRecords.fkCustomerID = Customers.CustomerID WHERE " & OpenArgs
Debug.Print strSQL
db.CreateQueryDef "qryTest", strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryTest", _
                            "C:\Users\" & Environ("username") & "\desktop\Test"
db.QueryDefs.Delete "qryTest"

... except that the same TrackingNumber may (rarely) be shared by two or more SalesRecords (where two sales are combined into one package for shipping to the customer). In that case, I only want the customer and tracking number to appear once in the resultant spreadsheet.

The following works to get unique TrackingNumbers

Code:

strSQL = "SELECT DISTINCT SalesRecords.TrackingNumber FROM SalesRecords WHERE " & OpenArgs

- should I somehow incorporate that as a subquery ?
By the way, OpenArgs contains a string such as

Code:

Courier = 'RM Signed for' AND SaleDate >= #30/11/2015# AND Not IsNull(DateShipped)

namliam · Jun 1, 2016

How about simply doing it like so?

Code:

strSQL = "SELECT [B][U]DISTINCT [/U][/B]Customers.CustomerName & ', ' & Customers.AddressLine1 & ', '" & _
            " & Customers.Addressline2 & ', ' & Customers.City & ', ' & Customers.StateOrCounty & ', '" & _
            " & Customers.ZipOrPostcode, SalesRecords.TrackingNumber, SalesRecords.Paid FROM SalesRecords" & _
            " INNER JOIN Customers ON SalesRecords.fkCustomerID = Customers.CustomerID WHERE " & OpenArgs

Well ... then maybe...

Code:

strSQL = "SELECT [B][U]DISTINCT [/U][/B] " & _
                    "Customers.CustomerName & ', ' &  " & _
                    "Customers.AddressLine1 & ', ' & " & _
                    "Customers.Addressline2 & ', ' &  " & _
                    "Customers.City & ', ' &  " & _
                    "Customers.StateOrCounty & ', ' &  " & _
                    "Customers.ZipOrPostcode " & _
                    ", SalesRecords.TrackingNumber " & _
                    ", SalesRecords.Paid " & _
            " FROM SalesRecords" & _
            " INNER JOIN Customers ON SalesRecords.fkCustomerID = Customers.CustomerID  " & _
            " WHERE " & OpenArgs

Readable code is maintainable code, afterall

BeeJayEff · Jun 1, 2016

Too simple, coupled with brain failure looking for a more complicated solution, that's why ! Thanks.

Subquery to get distinct values ? (1 Viewer)

BeeJayEff

Registered User.

namliam

The Mailman - AWF VIP

BeeJayEff

Registered User.

Users who are viewing this thread