Help with creating report in Access 2007

[mulamootil]

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.

[nico5038]

When you want to "force" output for a certain category, the general approach is to create a table with all categories. Next an outer join is created, holding all values for the category and only values for the "child" when present. Thus a NZ() function can be used to return a zero for not found values.

Getting the idea ?

Nic;o)

[mulamootil]

Thanks Nic;o. I explored what you suggested and got what I was looking for. I really appreciate your help.

Regards
Stan

[nico5038]

You're doing fine Stan, I see you are "learning the trade " quickly :-)

Nic;o)