Summarizing report-would like one line per job# 1

[Bufsabres]

I am creating a one page weekly Production Summary Report with job# down the left and the days of the week broken down by three shifts across the top. I am quering the information into text boxes using the "IIf" function. I would like to have only one line per job on my report but end up with as many lines as there are records per job#, as each record has a quantity that matches one of the 21 text boxes. What should I use as a function to put one total per text box and have only one line without having 0 show up as a false return on my "IIf" statement?

Ex

Sunday Monday Tuesday
Job# A B C A B C A B C
A001 0 5 0 0 0 0 0 0 0
A001 0 0 0 4 0 0 0 0 0
A001 0 0 0 0 0 0 0 0 9

Would like to see

Sunday Monday Tuesday
Job# A B C A B C A B C
A001 0 5 0 4 0 0 0 0 9

 

[SteveR77]

Looking at the data and how you have stated that you wish the output to appear I would tell you to start by creating a select query that has the Job# and then your day fields.

Next, click on the sigma button when you are in the design of your query. (It's the button that looks like a stylized E)

For the Job# set the total line to be Group By for all the other fields set the total line to sum.


HTH,

Steve

 

[Bufsabres]

Steve:

That works just fine. I guess I was not through filtering my data. One has to remember to query just the information needed for the report.

Thanks