Hi friends, I am new to access and I need to create a report. Please pardon me for such a long post.Appreciate your patience.
I have a table in Access (MyTable) with data about products manufactured in three different sizes on 4 different assembly lines.
I need a report showing the total production of all 3 sizes on both shifts for Factory A and B separately, for a given month of a year .Same as the format specified in the attachment (Report Format)
I wrote the query as following, but it requires some modifications to get the desired output.
SELECT Mytable.Factory , Month([Date]) AS [Month], Mytable.Shift, Mytable.Line, Sum(Mytable.[Size A]) AS [Size A], Sum(Mytable.[Size B]) AS [Size B], Sum(Mytable.[Size C]) AS [Size C], Year([Date]) AS Year
FROM Mytable
GROUP BY Mytable.Factory , Month([Date]), Mytable.Shift, Mytable.Line, Year([Date])
ORDER BY Year([Date]),Month([Date]), [Mytable].Shift;
Please find my Output in the attachment (output) .Here, L4 is missing (because Fac A didn’t have any production in L4). But I would like to see L 4 in my report with the value 0..ie, Even if nothing was produced on the line display it on the report.
Please help me modify the query accordingly and create a customized report (as in Report Format) where user inputs the Factory, year and month to generate the report.
This would help me tremendously. It might not look that complex for an expert, but for me this is a herculean task. Thank you for all the help in advance.
I have a table in Access (MyTable) with data about products manufactured in three different sizes on 4 different assembly lines.
I need a report showing the total production of all 3 sizes on both shifts for Factory A and B separately, for a given month of a year .Same as the format specified in the attachment (Report Format)
I wrote the query as following, but it requires some modifications to get the desired output.
SELECT Mytable.Factory , Month([Date]) AS [Month], Mytable.Shift, Mytable.Line, Sum(Mytable.[Size A]) AS [Size A], Sum(Mytable.[Size B]) AS [Size B], Sum(Mytable.[Size C]) AS [Size C], Year([Date]) AS Year
FROM Mytable
GROUP BY Mytable.Factory , Month([Date]), Mytable.Shift, Mytable.Line, Year([Date])
ORDER BY Year([Date]),Month([Date]), [Mytable].Shift;
Please find my Output in the attachment (output) .Here, L4 is missing (because Fac A didn’t have any production in L4). But I would like to see L 4 in my report with the value 0..ie, Even if nothing was produced on the line display it on the report.
Please help me modify the query accordingly and create a customized report (as in Report Format) where user inputs the Factory, year and month to generate the report.
This would help me tremendously. It might not look that complex for an expert, but for me this is a herculean task. Thank you for all the help in advance.
Comment