Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MS Access (http://www.go4expert.com/forums/ms-access-forum/)
-   -   query question (http://www.go4expert.com/forums/query-question-t21538/)

luckyelee 27Mar2010 01:16

query question
 
I have a query

SELECT [BladeData by Unit by Size Total2].UNIT AS [BladeData by Unit by Size Total2_UNIT], [BladeData by Unit by Size Total Sharpen2].SIZE AS [BladeData by Unit by Size Total Sharpen2_SIZE], [BladeData by Unit by Size Total Sharpen2].CountOfSHARPEN, [BladeData by Unit by Size Total2].SumOfSumOfCOUNT
FROM (BladeSize INNER JOIN [BladeData by Unit by Size Total Sharpen2] ON (BladeSize.Size = [BladeData by Unit by Size Total Sharpen2].SIZE) AND (BladeSize.UNIT = [BladeData by Unit by Size Total Sharpen2].UNIT)) INNER JOIN [BladeData by Unit by Size Total2] ON (BladeSize.Size = [BladeData by Unit by Size Total2].SIZE) AND (BladeSize.UNIT = [BladeData by Unit by Size Total2].UNIT);

however, I need it to produce a total even if the CountofSharpen is 0.
Can anyone help me with this?

Table is

BladeDataUNITSIZEDATECLOCK#COUNTSHARPEN11150.75 M-002 A11/26/20107117989-111150.75 M-002 A21/26/2010719179-111150.75 M-002 A31/26/2010713304-111150.75 M-002 B11/26/20109681716-111150.75 M-002 B31/26/2010712216-111150.75 M-002 B41/26/20107110809-111150.75 M-004 A11/26/2010717167-111150.75 M-004 A21/26/2010714674-111150.75 M-004 A31/26/2010717288-111150.75 M-004 A41/26/20107321762-111150.75 M-004 B21/26/20107111531-111150.75 S-001 A11/26/20107119586-111150.75 S-001 A21/26/2010717827-111150.75 S-001 A31/26/201071480-111150.75 S-001 A41/26/20107322549-111150.75 S-001 B31/26/2010714769-111150.75 S-001 B41/26/2010718256-111150.75 S-003 A11/26/20107115239-111150.75 S-003 A21/26/2010719124-111150.75 S-003 A31/26/2010712808-111150.75 S-003 B11/26/2010715223-111150.875 M-002 A11/26/20107116241-111150.875 M-002 A21/26/20107128280-111150.875 M-002 A31/26/2010718999-111150.875 M-002 A41/26/20107113336-111150.875 M-002 B11/26/2010719711-111150.875 M-002 B31/26/20103422310-111150.875 M-002 B41/26/2010342174-111150.875 M-004 A21/26/20103421996-111150.875 M-004 A41/26/20107325963-111150.875 M-004 B11/26/2010713179-111150.875 M-006 A31/26/2010715647-111150.875 M-006 A41/26/2010732600-111150.875 M-006 B11/26/20107113768-111150.875 M-006 B21/26/20103421919-111150.875 S-001 A11/26/2010716739-111150.875 S-001 A21/26/2010718172-111150.875 S-001 A31/26/20107111958-111150.875 S-001 A41/26/2010749417-111150.875 S-001 B11/26/2010712400-111150.875 S-001 B31/26/201071415-111150.875 S-001 B41/26/201071174-111150.875 S-003 A11/26/20105941557-111150.875 S-003 A21/26/2010713179-111150.875 S-003 A31/26/20103421996-111150.875 S-003 A41/26/20107325963-111150.875 S-005 A11/26/2010714388-111150.875 S-005 A31/26/20107325077-111150.875 S-005 A41/26/2010732600-111150.875 S-005 B11/26/2010719330-111150.875 S-005 B41/26/2010712489-111151.0 M-002 A11/26/20107124684-111151.0 M-002 A21/26/20107119760-111151.0 M-002 A31/26/2010714094-111151.0 M-002 A41/26/2010713639-111151.0 M-002 B11/26/2010712728-111151.0 M-004 A11/26/20107123562-111151.0 M-004 A21/26/2010714107-111151.0 M-004 A41/26/2010715195-111151.0 M-004 B11/26/2010719016-111151.0 S-001 A11/26/20107125636-111151.0 S-001 A21/26/20107119776-111151.0 S-001 A31/26/2010712424-111151.0 S-001 A41/26/2010713225-111151.0 S-001 B11/26/2010716864-111151.0 S-003 A11/26/20107121141-111151.0 S-003 A21/26/2010715148-111151.0 S-003 A31/26/20103421996-111151.0 S-003 B11/26/2010718442-111151.0 S-003 B41/26/2010717750-111151.125 M-002 A11/26/20107127196-111151.125 M-002 A21/26/20107122590-111151.125 M-002 A31/26/20107321986-111151.125 M-002 B11/26/20109681587-111151.125 M-002 B21/26/2010718010-111151.125 M-002 B41/26/20105942535-111151.125 M-004 A11/26/20107143350-111151.125 M-004 A21/26/2010718841-111151.125 M-004 A31/26/20107112268-111151.125 M-004 A41/26/2010719897-111151.125 M-004 B11/26/20109682550-111151.125 M-004 B41/26/20107323712-111151.125 M-006 A11/26/20107111507-111151.125 M-006 A21/26/2010718822-111151.125 M-006 A31/26/2010715900-111151.125 M-006 A41/26/2010719031-111151.125 M-006 B11/26/2010717392-111151.125 M-006 B31/26/2010713602-111151.125 M-006 B41/26/2010714451-111151.125 S-001 A11/26/20107130799-111151.125 S-001 A21/26/20107122590-111151.125 S-001 A41/26/20105942535-111151.125 S-001 B11/26/20109681587-111151.125 S-001 B21/26/2010718010-111151.125 S-003 A11/26/2010719248-111151.125 S-003 A21/26/20107110755-111151.125 S-003 A31/26/20107112134-111151.125 S-003 A41/26/20107122113-111151.125 S-003 B11/26/20107326043-111151.125 S-003 B21/26/20107115717-111151.125 S-005 A11/26/20107322500-111151.125 S-005 A21/26/20107113724-111151.125 S-005 A31/26/2010715864-111151.125 S-005 A41/26/20107110582-111151.125 S-005 B11/26/2010594385-111151.125 S-005 B21/26/2010714787-111151.125 S-005 B31/26/2010718376-111151.125 S-005 B41/26/2010714451-111151.25 M-002 A11/26/20107119064-111151.25 M-002 A21/26/20107147251-111151.25 M-002 A31/26/20107114676-111151.25 M-002 A41/26/20107323172-111151.25 M-002 B11/26/2010712886-111151.25 M-002 B31/26/2010342449-111151.25 M-002 B41/26/20109682066-111151.25 M-004 A11/26/2010719107-111151.25 M-004 A21/26/2010713542-111151.25 M-004 A41/26/20107322241-111151.25 M-006 A11/26/20107112271-111151.25 M-006 A21/26/2010713628-111151.25 M-006 A31/26/20107321089-111151.25 S-001 A11/26/20107126554-111151.25 S-001 A21/26/20107150362-111151.25 S-001 A31/26/20105941995-111151.25 S-001 A41/26/2010713388-111151.25 S-001 B21/26/2010712886-111151.25 S-001 B41/26/20109682066-111151.25 S-003 A11/26/20107113581-111151.25 S-003 A21/26/2010712510011151.25 S-003 A31/26/2010714777-111151.25 S-005 A11/26/20107112267-111151.25 S-005 A21/26/2010714717-111151.375 M-002 A11/26/20107128272-111151.375 M-002 A21/26/20107116142-111151.375 M-002 A31/26/20107134300-111151.375 M-002 A41/26/2010712366-111151.375 M-002 B11/26/2010719593-111151.375 M-002 B21/26/20107121721-111151.375 M-002 B31/26/20103422102-111151.375 M-002 B41/26/2010716536-111151.375 M-004 A11/26/2010715292-111151.375 M-004 A21/26/2010713346-111151.375 M-004 A31/26/2010712609-111151.375 M-004 A41/26/2010717451-111151.375 M-004 B11/26/2010713792-111151.375 M-004 B21/26/2010713980-111151.375 M-004 B31/26/2010718039-111151.375 M-006 A11/26/20107118666-111151.375 M-006 A21/26/2010715121-111151.375 M-006 A31/26/20105941894-111151.375 M-006 A41/26/2010714189-111151.375 M-006 B21/26/20109681472011151.375 S-001 A11/26/20107122447011151.375 S-001 A21/26/20107124734-111151.375 S-001 A31/26/20107140874-111151.375 S-001 A41/26/2010712366-111151.375 S-001 B11/26/2010718747-111151.375 S-001 B21/26/2010711894-111151.375 S-001 B31/26/2010716834-111151.375 S-001 B41/26/20107111540-111151.375 S-003 A11/26/2010719789-111151.375 S-003 A31/26/20107115368-111151.375 S-003 A41/26/20103424390-111151.375 S-003 B11/26/2010712283-111151.375 S-005 A11/26/20107117945-111151.375 S-005 A21/26/20107110815-111151.375 S-005 A31/26/20105941894-111151.375 S-005 A41/26/2010717969-111151.375 S-005 B41/26/2010713980-111151.5 M-002 A11/26/20107124212-111151.5 M-002 A21/26/20107131061-111151.5 M-002 A31/26/20107139523-111151.5 M-002 A41/26/20107110996-111151.5 M-002 B11/26/20107114958-111151.5 M-002 B21/26/20107111286-111151.5 M-002 B31/26/2010718729-111151.5 M-002 B41/26/2010719860-111151.5 M-004 A11/26/2010712638-111151.5 M-004 A21/26/2010718033-111151.5 M-004 A31/26/2010712573-111151.5 M-004 A41/26/2010716855-111151.5 M-004 B41/26/20107323631-111151.5 S-001 A11/26/20107132531-111151.5 S-001 A21/26/20107122481-111151.5 S-001 A31/26/20107115741-111151.5 S-001 A41/26/20107117479-111151.5 S-001 B11/16/2010711751-111151.5 S-001 B21/26/2010715543-111151.5 S-001 B31/26/20105942559-111151.5 S-001 B41/26/20103422967-111151.5 S-003 A11/26/2010715741-111151.5 S-003 A21/26/2010714703-111151.5 S-003 A31/26/2010717087-111151.5 S-003 A41/26/20105942111-111151.5 S-003 B31/26/2010714994-111151.5 S-003 B41/26/20103421939-111151.625 M-002 A11/26/20107129661-111151.625 M-002 A21/26/20107138158-111151.625 M-002 A31/26/2010714044-111151.625 M-002 A41/26/20105942271-111151.625 M-002 B11/26/2010714558-111151.625 M-002 B21/26/2010714707-111151.625 M-002 B31/26/2010717630-111151.625 M-002 B41/26/2010715837-111151.625 M-004 A21/26/2010715510-111151.625 M-004 A31/26/20107321929-111151.625 S-001 A11/26/20107119679-111151.625 S-001 A21/26/20107120280-111151.625 S-001 A31/26/20107112997-111151.625 S-001 A41/26/2010715841-111151.625 S-001 B21/26/20109683280-111151.625 S-001 B31/26/20107114378-111151.625 S-001 B41/26/20107121623-111151.625 S-003 A11/26/2010716135-111151.625 S-003 A21/26/20107321354011151.75 M-002 A11/26/2010719295-111151.75 M-002 A21/26/20107127484-111151.75 M-002 A31/26/2010717341-111151.75 M-002 B11/26/20107115643-111151.75 M-002 B21/26/2010715426-111151.75 M-006 A11/26/2010714823-111151.75 M-006 A21/26/2010716762-111151.75 M-006 A31/26/2010732270-111151.75 S-001 A11/26/2010716682-111151.75 S-001 A21/26/20107139372-111151.75 S-001 A31/26/20107321636-111151.75 S-001 A41/26/2010713737-111151.75 S-001 B11/26/2010714311-111151.75 S-001 B31/26/2010712968-111151.75 S-001 B41/26/2010717284-111151.75 S-005 A11/26/2010711686-111151.75 S-005 A21/26/2010714169-111151.75 S-005 A31/26/2010716000-111141.875 M-002 A11/26/20107166990-111141.875 M-002 A12/2/2010594806011141.875 M-002 A12/2/2010594935011141.875 M-002 A12/4/20105941794011141.875 M-002 A12/4/20105943277011141.875 M-002 A12/5/20107322143011141.875 M-002 A12/11/20105944297011141.875 M-002 A12/11/2010594737011141.875 M-002 A12/13/20105944100011151.875 M-002 A11/26/2010712611-111151.875 M-002 A21/26/2010342837-111151.875 M-002 A31/26/2010714822-111141.875 M-002 B11/26/20107162154-111141.875 M-004 A11/26/20107140309-111141.875 M-004 B11/26/2010714216-111141.875 M-006 A11/26/2010719766-111141.875 M-006 A13/1/2010594502011141.875 M-006 B11/26/20107136922-111141.875 M-008 A11/26/20107112587-111141.875 M-008 A11/12/20107326859011141.875 M-008 A12/1/20107326305011141.875 M-008 B11/26/20107131502-111141.875 M-010 A12/26/20107326397011141.875 M-010 A13/2/20107325357011141.875 M-010 A13/8/20107322811011141.875 M-010 A13/9/20105942402011141.875 M-010 A13/9/20105943522011141.875 S-001 A11/26/201071107110-111141.875 S-001 A12/4/20105941784011141.875 S-001 A12/4/20105943277011141.875 S-001 A12/5/20107322143011141.875 S-001 A12/11/20105944297011141.875 S-001 A12/11/2010594738011141.875 S-001 A12/12/20105944100011151.875 S-001 A11/26/2010712611-111141.875 S-001 A12/2/2010594806011141.875 S-001 A12/2/2010594935011151.875 S-001 A21/26/2010712957-111151.875 S-001 A31/26/2010712702-111141.875 S-001 B11/26/20107143578-111141.875 S-003 A11/26/20107133672-111141.875 S-003 B11/26/2010715608-111141.875 S-007 A11/26/20107111586-111141.875 S-007 A11/12/20107326859011141.875 S-007 B11/26/20107154007-111141.875 S-007 B11/29/20105943076011141.875 S-007 B12/1/20107326305011141.875 S-009 A12/26/20107326397011141.875 S-009 A13/1/2010594503011141.875 S-009 A13/2/20107325357011141.875 S-009 A13/8/20107322811011141.875 S-009 A13/9/20105942402011141.875 S-009 A13/9/20105943522011122.0 M-002 A11/26/2010594983-111142.0 M-002 A11/26/2010715675-111142.0 M-002 B12/12/20103421260011142.0 M-002 B12/12/20107327360011122.0 S-001 A11/26/2010594983-111142.0 S-001 A12/12/20103421260011142.0 S-001 A12/12/20107327360011142.0 S-003 A11/26/2010715675-111142.125 M-002 A11/26/20107120675-111142.125 M-002 B11/26/2010714288-111142.125 S-001 A11/26/20107120615-111142.125 S-001 B11/26/2010714288-111122.25 M-002 A11/26/20107321363-111142.25 M-002 A11/26/20107172173-111142.25 M-002 A11/27/20105944028011142.25 M-002 A12/2/20105941273011142.25 M-002 A12/18/20107325017011142.25 M-002 A12/22/20107327372011142.25 M-002 A12/23/20105941958011142.25 M-002 A12/23/20105941938011142.25 M-002 A12/22/2010342980011142.25 M-002 A12/24/20107324495011122.25 M-002 A21/26/2010594700-111122.25 M-002 A210/20/20095941053011122.25 M-002 A41/26/20109681832-111122.25 M-002 A412/1/2009342468011122.25 M-002 A412/2/20103421111011142.25 M-002 B11/26/20107116919-111142.25 M-002 B12/13/20103421888011142.25 M-002 B12/16/20107327150011142.25 M-002 B12/16/20103422015011142.25 M-004 A11/26/2010714373-111142.25 M-006 A11/26/20107118256-111142.25 M-006 A12/5/20107323570011142.25 M-006 B11/26/2010712213-111122.25 S-001 A11/26/20109681832-111142.25 S-001 A11/26/20107132340-111142.25 S-001 A11/27/20105944022011142.25 S-001 A12/2/20105941273011122.25 S-001 A21/26/2010712063-111122.25 S-001 A210/20/20095941053011122.25 S-001 A412/1/2009342468011122.25 S-001 A412/2/20093421111-111122.25 S-001 B11/26/2010732732-111142.25 S-001 B11/26/20107125857-111142.25 S-003 B11/26/20107126022-111142.25 S-005 A11/26/20107115661-111142.25 S-005 A12/5/20107323570011142.25 S-005 A12/13/20103421888011142.25 S-005 A12/16/20107327150011142.25 S-005 B11/26/20107321004-111122.5 M-002 A11/26/20107119239-111142.5 M-002 A11/26/20107112977-111142.5 M-002 A12/2/2010594416011122.5 M-002 A19/19/2009342535011122.5 M-002 A111/25/20095941843011122.5 M-002 A21/26/2010711580-111122.5 M-002 A21/20/2010594817011122.5 M-002 A31/26/2010713543-111122.5 M-002 A310/26/20097323032011122.5 M-002 A311/30/20097321074011142.5 M-002 B12/3/20107325658011122.5 S-001 A11/26/20107116710-111142.5 S-001 A11/26/20107115572-111142.5 S-001 A12/2/2010594416011122.5 S-001 A19/19/2009342535011122.5 S-001 A111/25/20095941843011122.5 S-001 A21/26/2010712375-111122.5 S-001 A211/30/20095941074011122.5 S-001 A21/20/2010594819011122.5 S-001 A31/26/2010713132-111122.5 S-001 A310/26/20097323032011142.5 S-001 B12/3/20107325856011122.75 M-002 A11/26/2010715021-111142.75 M-002 A11/26/2010715784-111122.75 M-002 A28/31/2009732448-111122.75 M-002 A29/1/2009732670011122.75 M-002 A29/1/2009594208011122.75 M-002 A29/4/2009968555011122.75 M-002 A29/8/2009968430011122.75 M-002 A210/29/20097322480011122.75 M-002 A211/2/2009732252011122.75 M-002 A41/26/2010732568-111142.75 M-004 A11/26/2010718454-111142.75 M-004 B11/26/2010713757-111122.75 S-001 A11/26/2010715021-111142.75 S-001 A11/26/2010713590-111122.75 S-001 A28/31/2009732448-111122.75 S-001 A29/1/2009732670011122.75 S-001 A29/1/2009594205011122.75 S-001 A29/4/2009968558011122.75 S-001 A29/8/2009968430011122.75 S-001 A210/29/20097322480011122.75 S-001 A211/2/2009732252011122.75 S-001 A41/26/2010732568-111142.75 S-003 A11/26/20107110648-111142.75 S-003 B11/26/2010713757-111123.0 S-001 A11/26/201096894-111123.25 M-002 A11/26/2010711597-111123.25 S-001 A11/26/2010711597-1

Thanks in advance......


All times are GMT +5.5. The time now is 14:23.