SQL Statment?

[RwGast]

Lets say your database is full of invoice line items and the invoice properties, so there for each line would look like<br><br>InvcNo Item             [Additonal Invc info]<br>366    Blah             [Additonal Invc info]<br>366    somthing         [Additonal Invc info]<br>366    not the same     [Additonal Invc info]<br>367    Whatever         [Additonal Invc info]<br>367    Blah             [Additonal Invc info]<br><br>Does anyone know to add to a SELECT to only i of each item by it InvcNo. So the query would ony return<br><br>InvcNo Item             [Additonal Invc info]<br>366    Blah             [Additonal Invc info]<br>367    Whatever         [Additonal Invc info]<br><br><br>

[RwGast]

I tried "SELECT DISTINCT * FROM InvcData" But it seemed to crash any ideas?

[RwGast]

Damn it all! It seams that Distinct makes sure that all the data in the record is unique, So you cant use it to select all the data, and make it "DISTINCT" or unique by just one field. Any ideas?

[Chris Edwards]

ok... don't really understand what you are trying to do.<br><br>you want it to return the first of each record #?<br><br>nevermind... i understand now <br><br>SELECT DISTINCT InvcNo, * FROM InvcData<br><br>selecting the distinct from * is probably where it's dying.. you can't tell it to eliminated duplicated entries in all the columns... just one.. so than we add a * later on...<br><br>tell me how that works.

[RICoder]

[RwGast]

[Chris Edwards]

[RwGast]

[Paul]

[RwGast]

[Paul]

[Paul]

SELECT DISTINCT InvcNo, Item, [Additonal Invc info]<br>FROM InvcData;<br><br>nah, thats rubbish, but i'll post it anyway because i'm bored waiting for my vid to download.<br><br>*voices in pauls head*<br>library girl!<br>library giiirrrll!!!<br><br>what? huh?!? ARRRRRRGGGGGGH!!!!!!<br><br>*drools*<br>

[RwGast]

Hmm i saw your modifaction of your post with that access thing. I dont know how to use access acually, i only use sql and vb. Maybe you could do it in access with a some made up data and show me the code it generates?

[Paul]

i have access 97 - doesnt work at the mo - i have access 2000 - not installed - cant be bothered right now, can you wait until tomorrow?<br><br>unless i suddenly can be bothered, in which case i'll do it soon.

[RwGast]

O thats cool, if you get aroud to it let me know. For now im just gonna do this all with vb code althought thats a horribly slow ass way.