Combining Data Without Duplicate Lines

Z

zcjxg0

I'm trying to combine this data:

SELECT tbl_PullData.FiscalYear, tbl_PullData.Period, tbl_PullData.Store,
tbl_Stores.[Store Name], tbl_PullData.Date, tbl_PullData.[Pull Number],
tbl_PullData.[Cases Ordered], tbl_PullData.[Cases Shipped],
tbl_PullData.[Cases Received], tbl_PullData.[Cases Refused], [Cases
Shipped]-[Cases Received]+[Cases Refused] AS [CasesGain/Loss], IIf([Total
Refused Cost] Is Null,0,[Total Refused Cost])+IIf([Total S/O Cost] Is
Null,0,[Total S/O Cost]) AS [OverallGain/Loss], IIf([Total Refused Cost] Is
Null,0,[Total Refused Cost])+IIf([Total S/O Cost] Is Null,0,[Total S/O Cost])
AS [FinalGain/Loss]
FROM tbl_Stores INNER JOIN (tbl_PullData INNER JOIN tbl_CurrentNumbers ON
tbl_PullData.FiscalYear = tbl_CurrentNumbers.ViewYear) ON tbl_Stores.[Store
Number] = tbl_PullData.Store
WHERE (((tbl_PullData.FiscalYear)=[tbl_CurrentNumbers]![ViewYear]) AND
((tbl_PullData.Store)=[Indicate Store "######"]))
ORDER BY tbl_PullData.Store, tbl_PullData.Date;

With this data:

SELECT tbl_MispicksAndDamage.FiscalYear, tbl_MispicksAndDamage.[Sequence
Number], tbl_MispicksAndDamage.InputDate, tbl_MispicksAndDamage.Period,
tbl_MispicksAndDamage.[Store Number], tbl_MispicksAndDamage.[Pull Number],
tbl_MispicksAndDamageDetail.Type, tbl_MispicksAndDamageDetail.Division,
tbl_MispicksAndDamageDetail.Department, tbl_MispicksAndDamageDetail.Cases,
tbl_MispicksAndDamageDetail.Amount
FROM tbl_MispicksAndDamage INNER JOIN tbl_MispicksAndDamageDetail ON
tbl_MispicksAndDamage.[Sequence Number] =
tbl_MispicksAndDamageDetail.FKSequence;

To create a report by store by period combining the above data with column
headings of Date, Pull Number, Cases Ordered, Cases Shipped, Cases Recv,
Cases Refused, Net Cases, Mispick Cases, Mispick $, Damage Cases, Damage $,
Quality Cases, and Quality $.
 
Top