Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
USE OF DISTINCT COMMAND IN MS QUERY
Hi,
I wanted to use Distinct Command thro MS query (excel)for filtering duplication from other database. PLs confirm how i can use & syntax for the same. I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE" Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
USE OF DISTINCT COMMAND IN MS QUERY
Have you tried
select field1,field2,..... from tablename group by field1, field2,.... Tim "Prakash" wrote in message ... Hi, I wanted to use Distinct Command thro MS query (excel)for filtering duplication from other database. PLs confirm how i can use & syntax for the same. I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE" Regards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
USE OF DISTINCT COMMAND IN MS QUERY
I tried, but no result. It could not filter duplicate entries. i need to
filter only 1st row of the duplicate entry. "Tim Williams" wrote: Have you tried select field1,field2,..... from tablename group by field1, field2,.... Tim "Prakash" wrote in message ... Hi, I wanted to use Distinct Command thro MS query (excel)for filtering duplication from other database. PLs confirm how i can use & syntax for the same. I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE" Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
USE OF DISTINCT COMMAND IN MS QUERY
What database are you using and do you have a query which works when run in
another query tool? How many column/what types etc...? Tim "Prakash" wrote in message ... I tried, but no result. It could not filter duplicate entries. i need to filter only 1st row of the duplicate entry. "Tim Williams" wrote: Have you tried select field1,field2,..... from tablename group by field1, field2,.... Tim "Prakash" wrote in message ... Hi, I wanted to use Distinct Command thro MS query (excel)for filtering duplication from other database. PLs confirm how i can use & syntax for the same. I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE" Regards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
USE OF DISTINCT COMMAND IN MS QUERY
Thanks for you suggetion.
I am using AS400 database files, one file contains around 1500 rows & 15 columns & in the row, nearly 500 rows are duplicate (same data repeating), so i wanted to filter while downloading thro MS query. Blw i gave the SQL statement i have written (SELECT ITH.TCOM, ITH.TTYPE, ITH.TWHS, ITH.THRNO, IIM.IITYP, ITH.TTDTE, IIM.IPURC, ITH.TREF, AVM.VENDOR, AVM.VNDNAM, ITH.THCURR, HPOX.POGSPR, ITH.TPROD, IIM.IDESC, ITH.TQTY, ITH.THLIN, IIMX.IXLOC1, ITH.TLOCT, AVM.VTERMS FROM AMPRDB.AMP40LPF.AVM AVM, AMPRDB.AMP40LPF.HPOX HPOX, AMPRDB.AMP40LPF.IIM IIM, AMPRDB.AMP40LPF.IIMX IIMX, AMPRDB.AMP40LPF.ITH ITH WHERE ITH.TPROD = HPOX.PPRODX AND ITH.TREF = HPOX.PORDX AND ITH.TVEND = AVM.VENDOR AND ITH.THLIN = HPOX.PLINEX AND ITH.TPROD = IIM.IPROD AND HPOX.PPRODX = IIM.IPROD AND IIM.IPROD = IIMX.IXPROD AND ((ITH.TTDTE Between 20060304 And 20060331) AND (ITH.TTYPE In ('RP','RS','N')) AND (ITH.THCURR<'INR')) GROUP BY ITH.TCOM, ITH.TTYPE, ITH.TWHS, ITH.THRNO, IIM.IITYP, ITH.TTDTE, IIM.IPURC, ITH.TREF, AVM.VENDOR, AVM.VNDNAM, ITH.THCURR, HPOX.POGSPR, ITH.TPROD, IIM.IDESC, ITH.TQTY, ITH.THLIN, IIMX.IXLOC1, ITH.TLOCT, AVM.VTERMS ORDER BY ITH.TTDTE) i need to apply distinct for ITH.TCOM field from ITH file. "Tim Williams" wrote: What database are you using and do you have a query which works when run in another query tool? How many column/what types etc...? Tim "Prakash" wrote in message ... I tried, but no result. It could not filter duplicate entries. i need to filter only 1st row of the duplicate entry. "Tim Williams" wrote: Have you tried select field1,field2,..... from tablename group by field1, field2,.... Tim "Prakash" wrote in message ... Hi, I wanted to use Distinct Command thro MS query (excel)for filtering duplication from other database. PLs confirm how i can use & syntax for the same. I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE" Regards |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
USE OF DISTINCT COMMAND IN MS QUERY
Looks fine to me. I don't know why it wouldn't produce only distinct rows.
You say you want only the "first row" of duplicate entries: if all selected columns have the same values in multiple rows then this should do it. Perhaps it might help to first simplify your query to use the smallest number of fields which still reproduces the problem. -- Tim Williams Palo Alto, CA "Prakash" wrote in message ... Thanks for you suggetion. I am using AS400 database files, one file contains around 1500 rows & 15 columns & in the row, nearly 500 rows are duplicate (same data repeating), so i wanted to filter while downloading thro MS query. Blw i gave the SQL statement i have written (SELECT ITH.TCOM, ITH.TTYPE, ITH.TWHS, ITH.THRNO, IIM.IITYP, ITH.TTDTE, IIM.IPURC, ITH.TREF, AVM.VENDOR, AVM.VNDNAM, ITH.THCURR, HPOX.POGSPR, ITH.TPROD, IIM.IDESC, ITH.TQTY, ITH.THLIN, IIMX.IXLOC1, ITH.TLOCT, AVM.VTERMS FROM AMPRDB.AMP40LPF.AVM AVM, AMPRDB.AMP40LPF.HPOX HPOX, AMPRDB.AMP40LPF.IIM IIM, AMPRDB.AMP40LPF.IIMX IIMX, AMPRDB.AMP40LPF.ITH ITH WHERE ITH.TPROD = HPOX.PPRODX AND ITH.TREF = HPOX.PORDX AND ITH.TVEND = AVM.VENDOR AND ITH.THLIN = HPOX.PLINEX AND ITH.TPROD = IIM.IPROD AND HPOX.PPRODX = IIM.IPROD AND IIM.IPROD = IIMX.IXPROD AND ((ITH.TTDTE Between 20060304 And 20060331) AND (ITH.TTYPE In ('RP','RS','N')) AND (ITH.THCURR<'INR')) GROUP BY ITH.TCOM, ITH.TTYPE, ITH.TWHS, ITH.THRNO, IIM.IITYP, ITH.TTDTE, IIM.IPURC, ITH.TREF, AVM.VENDOR, AVM.VNDNAM, ITH.THCURR, HPOX.POGSPR, ITH.TPROD, IIM.IDESC, ITH.TQTY, ITH.THLIN, IIMX.IXLOC1, ITH.TLOCT, AVM.VTERMS ORDER BY ITH.TTDTE) i need to apply distinct for ITH.TCOM field from ITH file. "Tim Williams" wrote: What database are you using and do you have a query which works when run in another query tool? How many column/what types etc...? Tim "Prakash" wrote in message ... I tried, but no result. It could not filter duplicate entries. i need to filter only 1st row of the duplicate entry. "Tim Williams" wrote: Have you tried select field1,field2,..... from tablename group by field1, field2,.... Tim "Prakash" wrote in message ... Hi, I wanted to use Distinct Command thro MS query (excel)for filtering duplication from other database. PLs confirm how i can use & syntax for the same. I have tried "SELECT DISTINCT FIELD1,FIELD2,..... FROM TABLE" Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help With MS Query Sum Distinct | Excel Discussion (Misc queries) | |||
where to reset microsoft query allow edit command DDE | Setting up and Configuration of Excel | |||
MS Query, command View-Query Properties | Excel Discussion (Misc queries) | |||
Command button macro query | Excel Programming | |||
"Substr" command in Query | Excel Programming |