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

Hi,

Use DISTINCT keyword in SELECT clause to avoid Duplicate Records.

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>>
)
;

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????