Hi all,

Using Access 2007,

I would like to select all records where unique values in Field1 and Not Blank in Field2
My SQL is returning a syntax error?



thx
w

For one thing you do not have a FROM Cluase in your SQL statement.

Thanks Daniel,

Fixed that.


Error states that I am missing an operator
Debug comes back between DISTINCT and myTbl

thx
w

Hi w,

What do you get with this?

SELECT DISTINCT *
FROM myTbl
WHERE Field2<>"blank"

Just curious... Do your fields really have "blank" in them?

Just my 2 cents...

Thanks DBguy,

The SQL returns this error


Field2 does not contain the word "blank", rather there is nothing in those Fields when I look at the table in datasheet view

thx
w

Hi w,

There is a typo in your version of my suggestion. I didn't combine DISTINCT with the * separated by a dot. To be more precise, try this one:

SELECT DISTINCT myTbl.*
FROM myTbl
WHERE Len(Field2 & "") > 0

Just my 2 cents...

Thanks,

The SQL is returning with no blanks in Field2, but there are still duplicates in Field1

I also tried with a space between DISTINCT and *, same result

thx
w

Hi w,

I think the DISTINCT keyword applies to the whole record. For example, of those with duplicate values in Field1, are there any that are also duplicates in Field2?

Just my 2 cents...

Hi DBguy,

I believe DISTINCT should only apply to a single field.
DISTINCTROW applies to the entire record.

Regardless, the values in Field1 are different from the values in Field2
Field1 being the CustomerName, Field2 being the CustomerNumber.

So as far as that goes, we could test for uniqueness on either Field1 or Field2, whichever is faster and most accurate

thx
w

Hi w,

No, here's what I meant... Let's say you have the following possible values in Field1:

Field1
John
Jane

And the following possible values in Field2:

Field2
One
Two

Do you see anything like this in your in your query result?

Field1 Field2
John One
John Two
Jane One
Jane Two

If I can see your data, I think I would be able to answer your question better.

The DISTINCT keyword applies to all the fields you selected in the query. So, if you select two, it applies to both of them. See this link for a better explanation of both DISTINCT and DISTINCTROW.

Just my 2 cents...

Thanks DBguy,

The query result is more along the lines of

Field 1 Field2
-----------------
Acme 123-ABC
Acme 123-ABC
Packers 456-DEF
Packers 456-DEF

Currenly 58,568 records ~ should be about 1/2 that many

thx
w

That's interesting... The DISTINCT keyword should catch that. I would like to see your data if you can post a zip copy of your database.

Try the following version and see if it makes a difference:

SELECT DISTINCT Field1, Field2
FROM myTbl
WHERE Len(Field2 & "") > 0

Just my 2 cents...

That worked - thanks!

But I tried adding Field 3 (Country) and it failed again
Like this


The data in Field3 is slighly different

Field3
-------------
rec USA
rec <-Blank
rec UK
rec

Sorry, data is proprietary so cannot upload the db.

thx
w

But I already told you, the DISTINCT keyword applies to the entire record. So, adding more fields could potentially introduce duplicates in a single field as long as all the fields together are unique.

Just my 2 cents...

This seems to solve the problem



thx
w

Hi,



Glad to hear you got it to work. Not knowing the intent kinds of limit me in what I could offer for suggestions. I thought you were saying that you were getting duplicates again.

Good luck with your project.