D
davie
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], tblweeksummar
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 Coun
HM0001/INS-ALS
HM0001/INS-FMC/GNK
HM0001/INS-KHS
HM0001/INS-KHS
HM0001/INS-TKW
HM0006-ALS 0
HM0006-ALS 20.
HM0006-BEN
HM0006-DAS
is it possilble that i can just have one record instead of duplicates? or can anyone suggest an alternative way
thanks dav
SELECT [tblLinkShipment Lines].[PNO], Sum([tblLinkShipment Lines].[Count]) AS [Count
FROM [tblLinkShipment Lines], tblweeksummar
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 Coun
HM0001/INS-ALS
HM0001/INS-FMC/GNK
HM0001/INS-KHS
HM0001/INS-KHS
HM0001/INS-TKW
HM0006-ALS 0
HM0006-ALS 20.
HM0006-BEN
HM0006-DAS
is it possilble that i can just have one record instead of duplicates? or can anyone suggest an alternative way
thanks dav