Unique Value

[Pearl1]

Hi All,

I've created a query using below SQL. All the information returned is duplicated. Is there anyway I can see one unique row.

Regards


SELECT [Sheetmetal with Routing Query].[ITEM MASTER STD COST QUERY].ITEMID,NAME, PROCESSTIME,OPRID, ITEMGROUPID, WRKCTRID, [ITEM MASTER TEST (ITEM COVERAGE)].ITEMID, MININVENTONHAND, INVENTLOCATIONID, [PROCESSTIME]*[MININVENTONHAND] AS Expr1
FROM [Sheetmetal with Routing Query] LEFT JOIN [ITEM MASTER TEST (ITEM COVERAGE)] ON [Sheetmetal with Routing Query].[ITEM MASTER STD COST QUERY].ITEMID = [ITEM MASTER TEST (ITEM COVERAGE)].ITEMID
WHERE ((([Sheetmetal with Routing Query].[ITEM MASTER STD COST QUERY].ITEMID) Like "7*") AND (([Sheetmetal with Routing Query].PROCESSTIME)>=3) AND (([Sheetmetal with Routing Query].OPRID)="PUNCHING") AND (([Sheetmetal with Routing Query].ITEMGROUPID)="SMPMTS") AND (([ITEM MASTER TEST (ITEM COVERAGE)].INVENTLOCATIONID)="RA"));

 

[namliam]

First, please use code tags when you post code (which includes SQL) on the forum. See my signature of a link

Second, see the first

Third when posting code, dont splash it ... make it readable.

Fourth see Second

Fifth see third

Sixth... Formatting should be something like below, adding the DISTINCT to create unique lines:

SELECT [U][B]DISTINCT[/B][/U]
       [Sheetmetal with Routing Query].[ITEM MASTER STD COST QUERY].ITEMID
     , NAME
     , PROCESSTIME
     , OPRID
     , ITEMGROUPID
     , WRKCTRID
     , [ITEM MASTER TEST (ITEM COVERAGE)].ITEMID
     , MININVENTONHAND
     , INVENTLOCATIONID
     , [PROCESSTIME]*[MININVENTONHAND] AS Expr1
FROM      [Sheetmetal with Routing Query] 
LEFT JOIN [ITEM MASTER TEST (ITEM COVERAGE)] ON [Sheetmetal with Routing Query].[ITEM MASTER STD COST QUERY].ITEMID = [ITEM MASTER TEST (ITEM COVERAGE)].ITEMID
WHERE     (    (([Sheetmetal with Routing Query].[ITEM MASTER STD COST QUERY].ITEMID) Like "7*") 
           AND (([Sheetmetal with Routing Query].PROCESSTIME)>=3) 
           AND (([Sheetmetal with Routing Query].OPRID)="PUNCHING") 
           AND (([Sheetmetal with Routing Query].ITEMGROUPID)="SMPMTS") 
           AND (([ITEM MASTER TEST (ITEM COVERAGE)].INVENTLOCATIONID)="RA")
          );

Eight
Adding distinct is probably a stopgap measure, your issue is most likely in your source where one (or both) of your two table has more than 1 line per ITEMID resulting (as it should be) in multiple lines.... If you have multiple lines you should (re)consider the design of your database or your query to get the proper result

Nine,
Naming conventions... they exist for a reason... for future reference please read and follow something along these lines:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=Naming+convention

Finaly number 10... Good luck and good hunting

 

[Pearl1]

Hi Namliam,

Appreciate your advise. Is there any lessons available online that can help me learn SQL...I mean good ones.

Also thank you for the link "on naming convention". It is a great read and an excellent help.

Regards

 

[namliam]

Well you seem to already know SQL, or atleast the basics.
Best way is to use it a lot...

Your problem here though isnt really SQL it is with your datamodel

 

[Pearl1]

Hi Namliam,

I've used the "Round", apparently it returns values to two decimal places.

I need to achive two things in here.

1st, to return Values to four decimal places.

2ndly, change the expression "AS Expr1" to a better defined Field.

Appreciate if you could assit me in the issue.

ROUND[PROCESSTIME]*[MININVENTONHAND] AS Expr1

Regards

Code:
SELECT DISTINCT [Sheetmetal with Routing Query].[ITEM MASTER STD COST QUERY].ITEMID , NAME , PROCESSTIME , OPRID , ITEMGROUPID , WRKCTRID , [ITEM MASTER TEST (ITEM COVERAGE)].ITEMID , MININVENTONHAND , INVENTLOCATIONID , ROUND[PROCESSTIME]*[MININVENTONHAND] AS Expr1FROM [Sheetmetal with Routing Query] LEFT JOIN [ITEM MASTER TEST (ITEM COVERAGE)] ON [Sheetmetal with Routing Query].[ITEM MASTER STD COST QUERY].ITEMID = [ITEM MASTER TEST (ITEM COVERAGE)].ITEMIDWHERE ( (([Sheetmetal with Routing Query].[ITEM MASTER STD COST QUERY].ITEMID) Like "7*") AND (([Sheetmetal with Routing Query].PROCESSTIME)>=3) AND (([Sheetmetal with Routing Query].OPRID)="PUNCHING") AND (([Sheetmetal with Routing Query].ITEMGROUPID)="SMPMTS") AND (([ITEM MASTER TEST (ITEM COVERAGE)].INVENTLOCATIONID)="RA") );

 

[namliam]

For the round function, try using F1 aka the help function or Google...
Round takes 2 parameters, Round(field,Precision)
The precision determines how many decimal points you want from say -3 for 1000 to 4 for 1000.0001. Not exactly sure what the limits are either way.

for As Expr1, surely you can type anything you want...