Duplicate Problem in joining 2 queries (1 Viewer)

[chleng]

Hi All,

I'm scratching my head to solve the duplicates problem in my query.:banghead:
I am using MS Access 2010.
Below are the 2 queries that I need to merge together

RMInput:
DateReceived
Supplier
Species
Grade
Price
TRNo (Primary Key)
Input

RMOutput:
ProdDate
Species
TRNo (Foreign Key)
Output

I need to merge these 2 queries into 1 query so that I can know that batch of input can produce how much of output.

RMInputOutput Yield:
RMOutput.ProdDate - user to key in the date range for production date
RMInput.DateReceived
RMInput.Supplier
RMInput.Species
RMInput.Grade
RMInput.TRNo
RMInput.Price
RMInput.Input
RMOutput.Output
Yield (%): ([Output]/[Input])*100

The major problem that I face in this query is the same batch of TRNo is not produced on the same day. 1 TRNo may have several ProdDate & output. Then the DateReceived, Supplier, Species, Grade, TRNo, Price & Input will repeat in the query and this may affect the accuracy of Yield.

I have tried to use crosstab query but there are a lot of TRNo & Species. So, cannot use them as the column heading.

Here is my current SQL code:

SELECT RMInput.DateReceived, RMOutput.ProdDate, RMInput.Supplier, RMInput.Species, RMInput.Grade, RMInput.TRNo, RMInput.Price, Round([Input],2) AS InputM3, Round([SumOfOutput],2) AS OutputM3, ([OutputM3]/[InputM3])*100 AS [Yield (%)]
FROM RMOutput INNER JOIN (RMInput INNER JOIN RawMaterial_Master ON (RMInput.Species = RawMaterial_Master.Species) AND (RMInput.TRNo = RawMaterial_Master.TRNo)) ON RMOutput.TRNo = RawMaterial_Master.TRNo
GROUP BY RMInput.DateReceived, RMOutput.RMProdDate, RMInput.Supplier, RMInput.Species, RMInput.Grade, RMInput.TRNo, RMInput.Price,  Round([Input],2), Round([SumOfOutput],2)
HAVING (((RMOutput.ProdDate) Between [forms]![UserMenu].[StartDate] And [forms]![UserMenu].[EndDate]) AND ((RMInput.Species) Like [forms]![UserMenu].[Species] & "**") AND ((RMInput.Grade) Like [forms]![UserMenu].[Grade] & "**") AND ((RMInput.TRNo) Like [forms]![UserMenu].[TRNo] & "**"));

Kindly help me in the above issue.

Thanks in advance.

Regards,
Leng