Results 1 to 8 of 8

Thread: query count

  1. #1
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Unanswered: query count

    I have a query which is counting which part number falls in between two dates from another table-- and it is! But what its not doing is showing all the part numbers as zero values that have not fallen between the dates, just the ones that fall in between the dates.

    SELECT [tblLinkShipment Lines].[PNO], Sum([tblLinkShipment Lines].[Count]) AS [Count]
    FROM [tblLinkShipment Lines], tblweeksummary
    WHERE ((([tblLinkShipment Lines].[Date] Between [wk1st] And [wk1end])))
    GROUP BY [tblLinkShipment Lines].[PNO]
    UNION Select distinctrow [tblLinkShipment Lines].PNO, 0 AS [Count]
    FROM [tblLinkShipment Lines];


    as an addition to the zero it is now giving me duplicate records. its giving the default value of zero, but when it finds a record it it gives the zero and the count :

    PNO Count

    HM0001/INS-ALS 0
    HM0001/INS-FMC/GNK 0
    HM0001/INS-KHS 0
    HM0001/INS-KHS 6

    HM0001/INS-TKW 0
    HM0006-ALS 0
    HM0006-ALS 20.3

    HM0006-BEN 0
    HM0006-DAS 0

    is it possilble that i can just have one record instead of duplicates? :
    thanks dave

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    You are getting the last error beacuse of your Second seelct statemetn in the Union

    Does PNO exist in tblweeksummary.

    If it does
    use a left join and only one SQL select statement

    Else

    You need the followin in you second SQL Select statement
    WHERE ((([tblLinkShipment Lines].[Date] NOT Between [wk1st] And [wk1end])))

    (not sure about correct sysntax)

    Good Luck

    S-

  3. #3
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Red face

    no it doesnt exist in tblweeksummry. Still stumped

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    Else

    You need the following in you second SQL Select statement
    WHERE ((([tblLinkShipment Lines].[Date] NOT Between [wk1st] And [wk1end])))

    (not sure about correct sysntax)

    Good Luck
    S-

  5. #5
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    thanks sbaxter
    but im still getting duplicates, even with using NOT between. I would have thought the distinct keyword would have got rid of two records that are the same.

  6. #6
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    can anyone suggest anything?

  7. #7
    Join Date
    Nov 2003
    Posts
    267
    Are you still need ing help. If so Could you post some sample data from your table and I will try using your data
    S-

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sbaxter, it looks like the join is not on PNO but on the date falling in the range

    you're right, a left outer join is required
    Code:
    select [tbllinkshipment lines].[pno]
         , sum([tbllinkshipment lines].[count]) as [count]
      from [tbllinkshipment lines]
    left outer
      join tblweeksummary
        on [tbllinkshipment lines].[date] 
           between [wk1st] and [wk1end]
    group 
        by [tbllinkshipment lines].[pno]
    rudy
    http://r937.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •