sql distinct

[aonefun]

Can I use DISTINCT in the following query that involves more than one field. I tried puting it in after SELECT and it still gave me multiple SoldToCMAccountNo

SELECT , ID, DocStatus, CustomText10, CustomText07, CustomText08, ShipToCompany, ShipToContact, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToPostalCode, ShipToCountry, ShipToPhone, ShipToPhoneExt, DocID, ManufacturerPartNumber, prodfamid_fam, ZIP_CODE, LATITUDE, LONGITUDE, STATE FROM Query1 WHERE LATITUDE <= " + Replace(Recordset1__HighLatitude, "'", "''") + " AND latitude >= " + Replace(Recordset1__LowLatitude, "'", "''") + " AND longitude >= " + Replace(Recordset1__LowLongitude, "'", "''") + " AND longitude <= " + Replace(Recordset1__HighLongitude, "'", "''") + " ORDER BY DocID DESC

 

[BrianLe]

I think your problem is in Query1. When you run it do you get multiple SoldtoCMAccountNo? Also, I don't see SoldtoCMAccountNo in your SELECT list.

Can you post Query1?

Brian

 

[aonefun]

LET ME REVISE MY QUESTION:

I would like my recordset results to show only one result for every SoldToCMAccountNo and have all other corresponding fields available in the recordset. How would I use DISTINCT in the following query?

SELECT SoldToCMAccountNo, ID, DocStatus, CustomText10, CustomText07, CustomText08, ShipToCompany, ShipToContact, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToPostalCode, ShipToCountry, ShipToPhone, ShipToPhoneExt, DocID, ManufacturerPartNumber, prodfamid_fam, ZIP_CODE, LATITUDE, LONGITUDE, STATE FROM Query1 WHERE LATITUDE <= " + Replace(Recordset1__HighLatitude, "'", "''") + " AND latitude >= " + Replace(Recordset1__LowLatitude, "'", "''") + " AND longitude >= " + Replace(Recordset1__LowLongitude, "'", "''") + " AND longitude <= " + Replace(Recordset1__HighLongitude, "'", "''") + " ORDER BY DocID DESC

 

[aonefun]

Yes, I accidentally omited SoldToCMAccountNo from the query on my post. Please see my revised question.

Also, I need query1 to be a many to many relationship and show multiple SoldToCMAccountNo because a given SoldToCMAccountNo could have purchased more than one ManufacturerPartNo. This info is required for my app.

 

[BrianLe]

Instead of using "ORDER BY DocID DESC" can you use "GROUP BY SoldToCMAccountNo"?

See thread 710-1366243.

Brian

 

[aonefun]

Do you mean leaving the whole query intact except for exchanging ORDER BY with GROUP BY

or do you mean changing the whole query to follow one of these (and which one)?

SELECT MergeString, First(FullString) FROM qryMergeTime GROUP BY MergeString
or:
SELECT MergeString, Last(FullString) FROM qryMergeTime GROUP BY MergeString
or:
SELECT MergeString, Min(FullString) FROM qryMergeTime GROUP BY MergeString
or:
SELECT MergeString, Max(FullString) FROM qryMergeTime GROUP BY MergeString

 

[BrianLe]

Try just changing the query to ...GROUP BY SoldToCMAccountNo

The thread was just my source of my suggestion.

Brian

 

[aonefun]

Tried the following query:

SELECT SoldToCMAccountNo, ID, DocStatus, CustomText10, CustomText07, CustomText08, ShipToCompany, ShipToContact, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToPostalCode, ShipToCountry, ShipToPhone, ShipToPhoneExt, DocID, ManufacturerPartNumber, prodfamid_fam, ZIP_CODE, LATITUDE, LONGITUDE, STATE FROM Query1 WHERE LATITUDE <= " + Replace(Recordset1__HighLatitude, "'", "''") + " AND latitude >= " + Replace(Recordset1__LowLatitude, "'", "''") + " AND longitude >= " + Replace(Recordset1__LowLongitude, "'", "''") + " AND longitude <= " + Replace(Recordset1__HighLongitude, "'", "''") + " GROUP BY SoldToCMAccountNo

But received this error message:

You tried to execute a query that does not include the specified expression 'ID' as part of an aggregate function.

 

[BrianLe]

Try including all the the fields in the SELECT in the GROUP BY.

SELECT SoldToCMAccountNo, ID, DocStatus, CustomText10, CustomText07, CustomText08, ShipToCompany, ShipToContact, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToPostalCode, ShipToCountry, ShipToPhone, ShipToPhoneExt, DocID, ManufacturerPartNumber, prodfamid_fam, ZIP_CODE, LATITUDE, LONGITUDE, STATE  FROM Query1  WHERE LATITUDE <= " + Replace(Recordset1__HighLatitude, "'", "''") + " AND latitude >= " + Replace(Recordset1__LowLatitude, "'", "''") + " AND longitude >= " + Replace(Recordset1__LowLongitude, "'", "''") + " AND longitude <= " + Replace(Recordset1__HighLongitude, "'", "''") + "  GROUP BY SoldToCMAccountNo, ID, DocStatus, CustomText10, CustomText07, CustomText08, ShipToCompany, ShipToContact, ShipToAddress1, ShipToAddress2, ShipToCity, ShipToState, ShipToPostalCode, ShipToCountry, ShipToPhone, ShipToPhoneExt, DocID, ManufacturerPartNumber, prodfamid_fam, ZIP_CODE, LATITUDE, LONGITUDE, STATE

 

[aonefun]

How would I accomplish the DISTINCT SoldToCMAccountNo feature as there are more than one result listed for each SoldToCMAccountNo?

 

[BrianLe]

How about trying another approach.

SELECT SoldToCMAccountNo, First(ID), First(DocStatus) ... GROUP BY SoldToCMAccountNo