Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Unanswered: help plz on a date query

    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. Pno isnt part of tblweeksummary.

    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? or can anyone suggest an alternative way.
    thanks dave

  2. #2
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Use DISTINCT keyword in SELECT clause to avoid Duplicate Records.
    SATHISH .

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: help plz on a date query

    The second query should only select records that were not selected by the first query:

    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]
    WHERE NOT EXISTS
    (
    SELECT [tblLinkShipment Lines].[PNO], Sum([tblLinkShipment Lines].[Count]) AS [Count]
    FROM [tblLinkShipment Lines], tblweeksummary
    WHERE ((([tblLinkShipment Lines].[Date] Between [wk1st] And [wk1end])))
    AND <<add correlated join condition here>>
    )
    ;

  4. #4
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    still getting duplicates.... thanks for the reply. Can anyone suggest anything i have created another table which has the default values of 0 in for all the part numbers and tried to create a union select query between my table and i still get the same...its just the same part number being duplicated, with a zero count and a count between the two weeks. cant the query be:

    iif (like pno, delete where pno is 0) or something to that effect????

Posting Permissions

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