Filter Result from a query

N

nice

Anyone who can help me generate SQL query or any query from form that will do
the following:

1. Open result query in xls format
2. Cut number of characters to be saved by the query
ex: NE8PARDV B H CX 2LQ7 -
output: NE8PARDV B H CX
3. After filter get unique records only from result query
ex: NE8PARDV B H CX 2LQ7
NE8PARDV B H CX 2SWQ
(after getting until CX, this will cause double records, and I want only
one of them to consider.)

I know I can use left command to get only the characters I want but getting
unique record afterward is something I don't know how to figure out.
Hope someone can help me. Thanks in advance!
-nice
 
J

JackP

query in sql view:
select distinct left(FieldName,15) as ShortName from TableName

the word "distinct" is the key one. Alternatively you could use "group by"
on microsoft access design view.

then once you've got your query, do file / export , save as type =
"microsoft excel" and go from there.
 
G

google

You should use the DISTINCT-clause in the SELECTpart of the query. You
can set this via the menu. Select query-properties and then 'Unique
values' (or something of the sort; using DUTCH-MSA here...)

Good luck!
 
N

nice

Hi JackP,
Thanks for your reply I tried to do this but I'm having error...here's my
SQL Query. Please help me where to put "distinct". I already placed the Left
word here and it's running. I'm only having error when I combine distinct
from the left term (distinct left)
Sorry for very long query (I think the problem is just here)

SELECT VOYAGER_F_PRODUCT.FACILITY, VOYAGER_F_PRODUCT.PRODUCT,
VOYAGER_F_OPERATION.OPERATION, VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION,
Left([VOYAGER_F_PRODUCT].[PRODUCT],15) AS Filter1
FROM VOYAGER_F_PRODUCT INNER JOIN VOYAGER_F_OPERATION ON
VOYAGER_F_PRODUCT.FACILITY = VOYAGER_F_OPERATION.FACILITY
GROUP BY VOYAGER_F_PRODUCT.FACILITY, VOYAGER_F_PRODUCT.PRODUCT,
VOYAGER_F_OPERATION.OPERATION, VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION,
Left([VOYAGER_F_PRODUCT].[PRODUCT],15)
HAVING (((VOYAGER_F_PRODUCT.FACILITY) Like "CV*" Or
(VOYAGER_F_PRODUCT.FACILITY) Like "A01*" Or (VOYAGER_F_PRODUCT.FACILITY) Like
"A13*" Or (VOYAGER_F_PRODUCT.FACILITY) Like "A27*" Or
(VOYAGER_F_PRODUCT.FACILITY) Like "A06*") AND ((VOYAGER_F_PRODUCT.PRODUCT)
Like "NE*" Or (VOYAGER_F_PRODUCT.PRODUCT) Like "JM*") AND
((VOYAGER_F_OPERATION.OPERATION) Like "7131") AND
((VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION) Like "PSC*" Or
(VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION) Like "PST*" Or
(VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION) Like "PRES*"));
 
D

Douglas J Steele

Try

SELECT DISTINCT VOYAGER_F_PRODUCT.FACILITY, ....

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


nice said:
Hi JackP,
Thanks for your reply I tried to do this but I'm having error...here's my
SQL Query. Please help me where to put "distinct". I already placed the Left
word here and it's running. I'm only having error when I combine distinct
from the left term (distinct left)
Sorry for very long query (I think the problem is just here)

SELECT VOYAGER_F_PRODUCT.FACILITY, VOYAGER_F_PRODUCT.PRODUCT,
VOYAGER_F_OPERATION.OPERATION, VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION,
Left([VOYAGER_F_PRODUCT].[PRODUCT],15) AS Filter1
FROM VOYAGER_F_PRODUCT INNER JOIN VOYAGER_F_OPERATION ON
VOYAGER_F_PRODUCT.FACILITY = VOYAGER_F_OPERATION.FACILITY
GROUP BY VOYAGER_F_PRODUCT.FACILITY, VOYAGER_F_PRODUCT.PRODUCT,
VOYAGER_F_OPERATION.OPERATION, VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION,
Left([VOYAGER_F_PRODUCT].[PRODUCT],15)
HAVING (((VOYAGER_F_PRODUCT.FACILITY) Like "CV*" Or
(VOYAGER_F_PRODUCT.FACILITY) Like "A01*" Or (VOYAGER_F_PRODUCT.FACILITY) Like
"A13*" Or (VOYAGER_F_PRODUCT.FACILITY) Like "A27*" Or
(VOYAGER_F_PRODUCT.FACILITY) Like "A06*") AND ((VOYAGER_F_PRODUCT.PRODUCT)
Like "NE*" Or (VOYAGER_F_PRODUCT.PRODUCT) Like "JM*") AND
((VOYAGER_F_OPERATION.OPERATION) Like "7131") AND
((VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION) Like "PSC*" Or
(VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION) Like "PST*" Or
(VOYAGER_F_PRODUCT.PRODUCT_DESCRIPTION) Like "PRES*"));




JackP said:
query in sql view:
select distinct left(FieldName,15) as ShortName from TableName

the word "distinct" is the key one. Alternatively you could use "group by"
on microsoft access design view.

then once you've got your query, do file / export , save as type =
"microsoft excel" and go from there.
Click to expand...
Click to expand...
 
Top