By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

Help with creating report in Access 2007

P: 33
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.
Attached Images
File Type: jpg output.JPG (21.5 KB, 114 views)
File Type: jpg Report format.JPG (15.4 KB, 114 views)
File Type: jpg MyTable.JPG (26.4 KB, 117 views)
Feb 6 '10 #1

✓ answered by 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)

Share this Question
Share on Google+
3 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Feb 8 '10 #2

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

Regards
Stan
Feb 14 '10 #3

nico5038
Expert 2.5K+
P: 3,072
You're doing fine Stan, I see you are "learning the trade " quickly :-)

Nic;o)
Feb 14 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.