Select Distinct help

[Tony Girgenti]

Using the attached select statement, i want distinct rows by "cust.nam" and
"sa_lin.item_no", but i have to include the "sa_hdr.post_dat" because i need
to refer to it in code. How do i tell the "DISTINCT" to only be distinct on
"cust.anm" and sa_lin.item_no" and still get the "sa_hdr.post_dat" ?

Any help appreciated.
Thanks, Tony

RptQry = "(sa_lin.item_no >= '" & [Forms]![Form1]![StrItm] & "' " & _
"OR " & [Forms]![Form1]![StrItmChk] & " = True) " & _
"AND (sa_lin.item_no <= '" & [Forms]![Form1]![EndItm] & "' "
& _
"OR " & [Forms]![Form1]![EndItmChk] & " = True) " & _
"AND (sa_hdr.post_dat >= '" &
Format$([Forms]![Form1]![StrDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![StrDatChk] & " = True) " & _
"AND (sa_hdr.post_dat <= '" &
Format$([Forms]![Form1]![EndDat], "yyyymmdd") & "' " & _
"OR " & [Forms]![Form1]![EndDatChk] & " = True) " & _
"AND (cust.zip_cod >= '" & [Forms]![Form1]![StrZip] & "' " &
_
"OR " & [Forms]![Form1]![StrZipChk] & " = True) " & _
"AND (cust.zip_cod <= '" & [Forms]![Form1]![EndZip] & "' " &
_
"OR " & [Forms]![Form1]![EndZipChk] & " = True) " & _
"AND (sa_hdr.sls_amt >= " & [Forms]![Form1]![StrSalAmt] & "
" & _
"OR " & [Forms]![Form1]![StrSalAmtChk] & " = True) " & _
"AND (sa_hdr.sls_amt <= " & [Forms]![Form1]![EndSalAmt] & "
" & _
"OR " & [Forms]![Form1]![EndSalAmtChk] & " = True) " & _
"AND (cust.cat = '" & [Forms]![Form1]![CusCat] & "' " & _
"OR " & [Forms]![Form1]![CusCatChk] & " = True) "

CusQry = "SELECT DISTINCT cust.nam, " & _
"cust.adrs_1, " & _
"cust.adrs_2, " & _
"cust.city, " & _
"cust.state, " & _
"cust.zip_cod, " & _
"cust.email_adrs, " & _
"cust.phone_no_1, " & _
"sa_lin.item_no AS itemnumber, " & _
"sa_hdr.post_dat AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no " & _
"WHERE " & RptQry & "ORDER BY cust.nam;"

 

[Emilia Maxim]

Using the attached select statement, i want distinct rows by "cust.nam" and
"sa_lin.item_no", but i have to include the "sa_hdr.post_dat" because i need
to refer to it in code. How do i tell the "DISTINCT" to only be distinct on
"cust.anm" and sa_lin.item_no" and still get the "sa_hdr.post_dat" ?

Tony,

I guess you misunderstood something about DISTINCT. This tells Jet to
return only records having a unique combination of all the fields
listed in SELECT. So if you have several records with the same
customer but different item number, you'll get records displaying the
same customer for all distinct item numbers. If there are several
dates for one item number, then you'll get each item number (+
customer) repeated for every distinct date.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Tony Girgenti]

Hello Emilia. Thanks for your help.

Here is what i get as a result of the Select: The first field is cust.nam,
the next to last field is the postdate and the last field is itemnumber. I
want to eliminate rows three and six.

"Walk-in customer","","","","","","","","20020107","WIL0001"
"Walk-in customer","","","","","","","","20020107","WIL0002"
"Watson Terry","5832 Misty Oak
Drive","","Memphis","TN","38138","","(901) 555-2731","20020107","777"
"Watson Terry","5832 Misty Oak
Drive","","Memphis","TN","38138","","(901) 555-2731","20020110","777"
"Watson Terry","5832 Misty Oak
Drive","","Memphis","TN","38138","","(901) 555-2731","20020110","888"
"Watson Terry","5832 Misty Oak
Drive","","Memphis","TN","38138","","(901) 555-2731","20020107","BIKE001"
"Watson Terry","5832 Misty Oak
Drive","","Memphis","TN","38138","","(901) 555-2731","20020110","BIKE001"
"West Enterprises","3286 N. Webster
Avenue","","Memphis","TN","38109","","901-555-9996","20020110","HAN2000"
"Wilson & Wilson","231 Lake
Drive","","Memphis","TN","38901","","901-555-9876","20020107",""
"Wilson & Wilson","231 Lake
Drive","","Memphis","TN","38901","","901-555-9876","20020107","*R"

 

[John Smith]

Add a sub-select to your query to find the Max(post_dat) for the primary key
value.