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:
Kindly help me in the above issue.
Thanks in advance.
Regards,
Leng
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:
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