Distinct command not working

[Greyowlsl]

Hey,

Ok so I have this form that has two subforms on it (sf1 and sf2), these are run by one query called 'test'. Below screenshot of what i dont want happening;

Bugger.jpg

Now what i want is a distinct command for the 'Document Number' column so it cant be shown more than once. basicly i want it to look like this...
what do i do?

Thanks for your time.

,Leon

 

[RuralGuy]

Post the SQL for your query and I'll bet someone will be able to offer suggestions.

 

[Greyowlsl]

Ok here is the sql data for the 'test' query

SELECT DISTINCTROW Log.LOGDEX, Log.[DOCUMENT NUMBER], Log.NAME, Log.DATE, Log.COMMENT, DRAWINGS.DESCRIPTION, DRAWINGS.HYPERLINK, DRAWINGS.[ISSUE DATE], DRAWINGS.[DOCUMENT NUMBER], DRAWINGS.[LAST PRINT DATE], DRAWINGS.REVISION, DRAWINGS.ORIGINATOR, DRAWINGS.PRODUCT, DRAWINGS.STATUS, DRAWINGS.CATEGORY, DRAWINGS.DOCUMENT, DRAWINGS.[FILE NAME], DRAWINGS.PROGRAM, DRAWINGS.INDEX, DRAWINGS.[SHEETS IN DOCUMENT], DRAWINGS.COMMENTS, DRAWINGS.[LOCATION OF DOCUMENTS], DRAWINGS.[QA TYPE], DRAWINGS.[NO OF COPIES]
FROM DRAWINGS LEFT JOIN Log ON DRAWINGS.[DOCUMENT NUMBER] = Log.[DOCUMENT NUMBER]
WHERE (((DRAWINGS.STATUS)="CURRENT") AND ((DRAWINGS.CATEGORY)="FORM"));

 

[RuralGuy]

I'm not particularly strong on SQL but try the Distinct keyword:

SELECT DISTINCT DRAWINGS.DESCRIPTION, DRAWINGS.HYPERLINK, DRAWINGS.[ISSUE DATE], DRAWINGS.[DOCUMENT NUMBER], DRAWINGS.[LAST PRINT DATE], DRAWINGS.REVISION, DRAWINGS.ORIGINATOR, DRAWINGS.PRODUCT, DRAWINGS.STATUS, DRAWINGS.CATEGORY, DRAWINGS.DOCUMENT, DRAWINGS.[FILE NAME], DRAWINGS.PROGRAM, DRAWINGS.INDEX, DRAWINGS.[SHEETS IN DOCUMENT], DRAWINGS.COMMENTS, DRAWINGS.[LOCATION OF DOCUMENTS], DRAWINGS.[QA TYPE], DRAWINGS.[NO OF COPIES], Log.LOGDEX, Log.[DOCUMENT NUMBER], Log.NAME, Log.DATE, Log.COMMENT
FROM DRAWINGS LEFT JOIN Log ON DRAWINGS.[DOCUMENT NUMBER] = Log.[DOCUMENT NUMBER]
WHERE (((DRAWINGS.STATUS)="CURRENT") AND ((DRAWINGS.CATEGORY)="FORM"));

 

[Greyowlsl]

Yea that is the UniqueValue property which i tried, but it says the OLE can be used on hyperlinks and my tables contain hyperlinks. Also when i remove the hyperlink column from the query it still shows duplicate records.

Thanks tho

 

[RuralGuy]

I haven't used it but I would think DISTINCT would only apply to the DRAWINGS.DESCRIPTION field (the first field after the KeyWord). Is this not correct?

 

[Greyowlsl]

Maybe but how would i do that?

 

[RuralGuy]

Have you tried the SQL I posted?

 

[Greyowlsl]

yes but it didnt work, it said that the OLE for the hyperlink column wont work, so i removed the hyperlink column but it still show duplicate records in datasheet view.

 

[RuralGuy]

I just tried it on a table I have with hyperlinks in a field and it works just fine. The DISTINCT keywork only applies to the next field and I displayed the hyperlink field in my results. Something else is going on with your db. Have you tried Compact and Repair? Any MISSING references? How about a /decompile? I don't recognize the error. What does it say exactly.

 

[Greyowlsl]

No i havent tried any of that, didnt think i would need to but this is what it says when i add your code to my query. prb1.jpg

Could it be because our our different os's and ac versions?

 

[RuralGuy]

Is one of your fields a Memo field or an OLE object?

 

[RuralGuy]

I'm guessing that the HYPERLINK field is either a Memo field or an OLE object.

 

[Greyowlsl]

I dont know how would i check that?

 

[Greyowlsl]

can i change it without affecting the records?

 

[RuralGuy]

Open the table in design mode.

 

[Greyowlsl]

yea done that but there is nothing to do with memo or OLE anywhere

 

[RuralGuy]

can i change it without affecting the records?

Unlikely! Just remove the field from your query to test the idea first.

 

[Greyowlsl]

Yea i have done that but there is nothing to do with memo or OLE anywhere.

 

[Greyowlsl]

Unlikely! Just remove the field from your query to test the idea first.

I have and it runs but the records are still duplicated like there has been no change what so ever.