SQL Statement I can't figure out :(

[cright99]

I have a table (Well actually a query called ClientSearch) which is for example holds this data:

CUSTOMERID NAME APPLICATIONNO
1001 Bob 5001
1001 Bob 5002
1002 Jane 5003
1003 Fred 5004
1003 Fred 5005
1004 James 5006

The SQL statement I have so far is:

SELECT * FROM ClientSearch WHERE CustomerID LIKE "1001"

This would get the following records

CUSTOMERID NAME APPLICATIONNO
1001 Bob 5001
1001 Bob 5002

But I only want it to ever show only 1 unique CustomerID, so I don't care which application it chooses pick. So it should show like this:

CUSTOMERID NAME APPLICATIONNO
1001 Bob 5001

I have thought about using the keyword DISTINCT or UNIQUE for example here.. but it doesn't work

SELECT UNIQUE(CustomerID), Name,  ApplicationNo FROM ClientSearch WHERE CustomerID LIKE "1001"

 

[pbaldy]

How about

SELECT CustomerID, Name, Min(ApplicationNo) As MinAppNo
FROM ClientSearch
WHERE CustomerID = "1001"
GROUP BY CustomerID, Name

 

[cright99]

How about

SELECT CustomerID, Name, Min(ApplicationNo) As MinAppNo
FROM ClientSearch
WHERE CustomerID = "1001"
GROUP BY CustomerID, Name

Sorry I should of elaborated on the conditions of this sql Statement. It is based off the search example here http://www.access-programmers.co.uk/forums/showthread.php?t=99353

So the sql statement in code can switch around to look like this:

SELECT * FROM ClientSearch WHERE ApplicationNo LIKE "5002"

I suppose I could use your example but I would have to put some more if statements in.

 

[Pyro]

What about:

Select Top 1 * FROM ClientSearch WHERE...

 

[cright99]

What about:

Select Top 1 * FROM ClientSearch WHERE...

Thanks for your help but Its still doesnt do what i want . this is really doing my head in .

The problem is that the WHERE conditions are variables in VBA. So the user can change what the values are and what fields to look in in the search engines interface. For example the user could do this (Translated from the userinterface to sql and what records are found):

From this table:

CUSTOMERID NAME APPLICATIONNO
1001 Bob 5001
1001 Bob 5002
1002 Jane 5003
1003 Fred 5004
1003 Fred 5005
1004 James 5006

The user searches for a string in the applicationNo text field of 500. This would translate to a sql (Which doesn't work) of

SELECT UNIQUE(CustomerID), Name, ApplicationNo FROM ClientSearch WHERE ApplicationNo LIKE "500*"

This should get the records:

1001 Bob 5001
1002 Jane 5003
1003 Fred 5004
1004 James 5006

Another example:

SELECT UNIQUE(CustomerID), Name, ApplicationNo FROM ClientSearch WHERE Name LIKE "J*"

This should get the records:

1002 Jane 5003
1004 James 5006

Another example:

SELECT UNIQUE(CustomerID), Name, ApplicationNo FROM ClientSearch WHERE CustomerID LIKE "1006*" AND Name LIKE "J*"

This should get the records:

1004 James 5006

Sorry for not explaining it in greater detail from the start. I didn't know the fix would be so complicated. .

Thanks for the help!

 

[cright99]

I think I found a solution but can't get it to work with Access because it doesn't allow select statements inside a select statement. It goes like this

SELECT * 
FROM ClientSearch AS CS 
JOIN (SELECT CustomerID, [ApplicationNo] = MIN([ApplicationNo])
FROM ClientSearch GROUP BY CustomerID) AS CO ON CS.CustomerID = CO.CustomerID
WHERE CS.CustomerID LIKE "500*";

 

[namliam]

How about

SELECT CustomerID, Name, Min(ApplicationNo) As MinAppNo
FROM ClientSearch
WHERE CustomerID = "1001"
GROUP BY CustomerID, Name

Did you give Paul's suggestion a shot?

 

[cright99]

Did you give Paul's suggestion a shot?

Yes I did and the problem is when the WHERE statement changes to

SELECT CustomerID, Name, Min(ApplicationNo) As MinAppNo
FROM ClientSearch
WHERE ApplicationNo LIKE "5001*"
GROUP BY CustomerID, Name

I get all the records in the table. But I only want the records that is LIKE "5001*"

Im really getting a headacke from this as ive been working on it for the last 4+ hours and thinking it might be impossible, ill sleep on it. However thanks for your help.

 

[namliam]

The NO part suggests that this is a NUMBER field, you cannot do a LIKE "*" on a number field, it is a string comparison.

Paul's solution is the way to go (imnsho) and

thinking it might be impossible

Thats just too funy....

 

[gfultz]

You can use an asterisk with a query on a number field. That isn't the issue with this solution.

The request is contradictory by nature. Utilizing a wildcard to tell access to locate all of the records that start like this, but don't grab more than one of each value, is something that it will not understand in a single query.

First, build a query that selects all unique id numbers. IE Select Distinct ID NO from Tbl.

Build a second query that uses the first query as its "From" table.

In the second query, use subqueries for the fields in question. If you use the same sort in all of the subqueries, the results will match. You won't be able to edit the query, but it will provide the results that you want. You could then program a form so that you could double click the record to open a standard editable form for that record.

Additionally, if you like your Join idea, don't create the join in the query. Create the join query as a standalone query, then call it with the join. You could even have it use the same filtering criteria.

I have attached a sample db that does it both ways. Test and Test2 do it the first way.

Join & join2 do it the second way.

One has subqueries and one doesn't. Whatever is easiest, the results are the same.

Run the "2" version of each for the finished product.

 

[namliam]

You can use an asterisk with a query on a number field. That isn't the issue with this solution.

No you CANNOT, if you do access will do an implicit conversion to a text field with all hazardous behaviours of that. * will always go to a string NEVER a number.