Tricky report.. not sure how to do it.. 2

[irethedo]

I have a table with the following information:

Date
Operation
Assembly number
Pass Quantity
Fail Quantity
Problem Code
Serial number

For records within a certain date range (start & end dates are specified in a form calling the report), I calculate a first pass yield by taking the pass quantity and dividing it by the total number of items tested.

Here is where it gets tricky...
================================
I need to create a report with first pass yield by operation with the following format:

The First Operation
===============

First pass yield by Operation
This is all assemblies under that operation)

-overall # tested
This is the total of all assemblies tested within this operation)

-overall First pass yield
(This is the total first pass yield of the total number of assemblies tested within this operation)

Then within the section of this operation I need to display:

the First Assembly
-# units tested
-First Pass yield
-Breakdown of failures
-10 failures of problem code x
-3 failures of problem code y

Then by next Assembly
-# units tested
-First Pass yield
-Breakdown of failures
-10 failures of problem code x
-3 failures of problem code y

The next Operation...
(and all of the other operations in the same format as above...)

=======================
I have limited expertise with this and can't seem to figure out how to do the grouping for this with the wizard.
Also I am not sure if I can combine wizards or macros to do this..

Do I need to make this as a report/sub-report?
Do I need to have some su-queries to do this?

Any suggestions to point me in the right direction would be greatly appreciated.

Thanks!
Irethedo

 

[PaulBricker]

There are two ways to do this. You could do all the calculations in a query and filter it by the date range you are getting from your Form. Or, you could do all the grouping and calculations directly in your report. Doing the calculations is quite simple no matter which way you choose, but it really depends on what you are more comfortable trying. Let me know and I will try and walk you through it.

Paul

 

[irethedo]

Thanks for the reply...
I think I would prefer making the calculations in the query itself but I am not sure how to proceed.

I appreciate your help with this!

irethedo

 

[PaulBricker]

We can take it in small steps so that you see the process as we go. If there is something you don't understand or isn't turning out the way you need just let us know. First, create a query with the table you need. Add the field Operation and the field Assembly Number.
Now, turn on the Totals. To do this, you can find the button on the toolbar, or go to View....Totals on the Menu Bar. This will add a Totals line to the QBE grid. On that totals line, you will set the field Operation to Group By and the field Assembly Number to Count. This query will return the count of Assembly Numbers per Operation.
Try this out. If you get something that seems like we are going in the right direction, let me know because I'm not 100 percent sure I understand everything you need.

Now, we'll take it one step further. Add this expression to the Field line in the third column.

FirstPassYeild:[Pass Quantity]/[CountOfAssembly Numbers]

and set the Total line to Expression.

This may or may not give you want you want depending on the values in the Pass Quantity field. What you may have to do in the third column on the Field line is have the field Pass Quantity, and set the Total line to Sum.
Again, it will depend on the data set up you are using.

Try this out, and post back with questions, concerns etc.

Paul

 

[irethedo]

Thanks Paul-

I tried this and for some reason when I try to view the report I get a prompt for "FPY by operation qry"

In my report, I want to print the first pass yield that I set up in my query so fot the data for this field I placed the following: "=[FPY by operation qry]![FirstPassYield]" and this is the only reference that I make anywhere for that query so I am assuming it has something to do with this...

Once I get the report to give the pass yuield info for one operation, then I need to list all of the assemblies under that operation one by one with thier pass yield information and a listing and total of each failure code.

Then to repeat the process for all other operations with the same format of assemblies and problem codes...

With the complexity and levels of sorting for these different sections of the report, might this require some of the calculations to not only be in the query, but also be performed in the report?

Thanks!

 

[PaulBricker]

Some questions.

1. What is the Record Source for the report. I take it it's not the query [FPY by operation qry]. If it's not, then you would need to use the DFirst function to get the first pass yeild.
=DFirst("FirstPassYeild","[FPY by operation qry]","Operation = '" & Operation & "'")

2. It would help to see your data so we have a little better understanding of what it looks like.
If you could just post a small sample of the data that would help.

3. If you need a record by record display of all the Assemblies, then you will probably need a sub report the way we have it set up. We might want to look at doing all the calculations in the report so we don't need a sub report.
4. In reports, you can Group By each Operation, and then Group or Count or Sum values for the Assembly Number or other fields and still be able to display all the Assembly records.

I'm starting to think that this might be the way to do it. It would sure help to see an example of the data.

Paul

 

[irethedo]

Thanks Paul:

Here is the information of what I currently have and the format of the report that I hope to create.
Note: my comments are in "()" below:
-----------------------------------

The Table = FPY Table
========
Date
Operation
Assembly number
Pass Quantity
Fail Quantity
Problem Code
Serial number

The Query = FPY by Operation Qry
========



The report: FPY by Operation rpt
(As I would like it to be...)
========

First Pass Yield Report By Operation
for period <start date> - <end date>

Operation: QC (refered to later as OPQC)

# of units tested = xxx (# units tested for OPQC)
First Pass Yield = xxx (pass qty/total for OPQC)

Assembly # = <assembly> (highest failed asm for OPQC)
# of failures = xxx (total failed asm for OPQC)
qty <#> failed for <problem code 1> (# failed prob code1)qty <#> failed for <problem code 2> (# failed prob code2)qty <#> failed for <problem code 3> (# failed prob code3)
Assembly # = <assembly> (2nd highest failed asm for OPQC)
qty <#> failed for <problem code 1> (# failed prob code1)qty <#> failed for <problem code 2> (# failed prob code2)qty <#> failed for <problem code 3> (# failed prob code3)


Assembly # = <assembly> (3rd highest failed asm for OPQC)============================================================

Operation: Test (refered to later as TEST)

# of units tested = xxx (# units tested for TEST)
First Pass Yield = xxx (pass qty/total for TEST)

Assembly # = <assembly> (highest failed asm for TEST)
# of failures = xxx (total failed asm for TEST)
qty <#> failed for <problem code 1> (# failed prob code1)qty <#> failed for <problem code 2> (# failed prob code2)qty <#> failed for <problem code 3> (# failed prob code3)
Assembly # = <assembly> (2nd highest failed asm for TEST)
qty <#> failed for <problem code 1> (# failed prob code1)qty <#> failed for <problem code 2> (# failed prob code2)qty <#> failed for <problem code 3> (# failed prob code3)

=============================

Operation: next...


------------------
Not sure if this helps; please let me know if this is do-able...

Thanks

 

[PaulBricker]

I doesn't look especially hard at this point.
1. In the report, you will have to add Group Headers and Group Footers. To do this, on the menu bar you can click
View....Sorting and Grouping
Now, under the Field/Expression column, click on the first line and select Operations from the list. Under Group Properties, set Group Header to Yes and Group Footer to Yes.
2. Put the Operations textbox in the Group Header. Now, this will group the report by Operations.
3. In the Group Footer we will calculate the Number Of Units Tested. I'm not sure which field will give us this, but it will be something like
=[Pass Quantity] + [Fail Quantity]
Some way, we have to get a count of the number of units that equals our total for each group.
4. Once you get the total quantity, you can do the math for the first path yeild something like
=[Pass Quantity]/([Pass Quantity]+[Fail Quantity])

Take it this far and see if you are getting what you want, then post back with problems. If we get this working, then the rest should be fairly straight forward.

Paul

 

[irethedo]

Paul-

Sorry for the delay in responding to you on this, I have been sidetracked with a couple other projects and then had to work through a few problems to get the report to this point...

The report is displaying the operation and the Assembly number just fine...

I have it displaying each assembly (sorted in order) under the operation along with all of the information for
each record.

What I need to do now is total up the number of pass and fails within the date range for the operation of this assembly and report a pass/fail yield rate, and pass & fail totals for each assembly.

Also I need to figure out how to categorize the failures by the problem code and dsiplay this information instead of the record information.

This might look like this:

=======================
Start Date: xx/xx/xx End Date: xx/xx/xx

Operation: Test

# of units per this operation = x
First pass yield rate = xx%

Assembly: widget 1
# of units per this operation = x
# units passed # units failed
First pass yield rate = xx%
Failure information
x failed due to problem 1
x failed due to problem 2

Assembly: widget 2
# of units per this operation = x
# units passed # units failed
First pass yield rate = xx%
Failure information
x failed due to problem 1
x failed due to problem 2

===================================

It definitely is getting close and I appreciate your guidance on this.

Thanks!

 

[PaulBricker]

Well, lets start with the number of units and the pass/fail %. You should have a footer for each assembly. In this footer, you are going to use calculations like this in the Control Source for a textbox.
=Count([Assembly number]) 'This should give you a total for that specific assembly
=[Fail Quantity]/Count([Assembly number])
=[Pass Quantity]/Count(Assembly number])

I'm assuming that Fail Quantity and Pass Quantity are fields that have totals in them. If you need to total Fail Quantity and Pass Quantity for each Assembly Number then the expressions would change to
=Sum([Fail Quantity])/Count([Assembly number])
=Sum([Pass Quantity])/Count([Assembly number])

Try these out and see how they go.

Now, to get the number failed for a specific reason you would use an expression like this in a textbox (in the Assembly number footer)
=Sum(IIf([Problem Code]=1,1,0))

where you put the actual problem code in the expression. Summing 1's and 0's is the same thing as counting (which we can't do inside an IIF() statement.

Let me know how it goes.

Paul

 

[irethedo]

So far the report is really taking shape but I am experiencing problems with a few things....

The first record displayed on the report is blank and does not exist in the table at all... I am not sure why this is even displayed as the first record on the first page but it appears as a normal record without an operation, with the proper start & end date information, but the first pass yield = "#Num!" and all other fields displayed for this record in the operation and assembly headers are blank or zero...

Also, I have the start and end date for each operation displayed in the operation header for each operation type and this is the date range specified by the form that calls the report. This looks great on the first page of the report for however many operations can be displayed on the first page, but these dates are not picked up and displayed for subsequent operations on the following pages of the report. On page 2 through the end of the report, I end up with the following: "start date: #Name? end date: #Name?". This is odd and I don't know why this is happening...

To display the problem codes, you suggested to use an _expression like the following in a textbox (in the Assembly number footer) "=Sum(IIf([Problem Code]=1,1,0))" where I would put the actual problem code in the _expression. I am not sure how to put the actual problem code in this expression as there could be 100 or more problems possible and I could never be sure which one might exist within a given date range. Please advise.

Thanks again for your guidance on this Paul!

 

[PaulBricker]

1. I can't imagine where the blank record is coming from. If there are start and end dates in the record, then it probably exists in the table. It's impossible for me to diagnose that one without actually seeing the data.
2. What are you using to capture the start and end dates on the first page of the report? =Forms!FormName!Startdate or something like that? Let me know.
3. To deal with the problem codes, you may have to create a problem code footer in the report that counts the problem codes per Assembly Number. Try that. Create a Problem code footer, before the assembly number footer and then put a textbox in that footer with the expression
=Count([Problem code]).

Let me know how it goes.

Paul

 

[irethedo]

1. I think I know where this blank record is coming from....
When I open the table, I see the very last line of the table with today's date and blank fields. I am not sure how this gets added or if it is there by default. Because this is the only record in the database without an operation in the operation field, I have to assume that this is the record that the report is listing at the top of the first page...

2. The field for the start date in the operation header is:

=Forms![print or view reports]!startdate

and the end date is:

=Forms![print or view reports]!enddate

This is in the operation header and I am not sure if there is anythng additionally that I have to do to ensure that this data gets displayed on subsequent pages...

3. I will try this and get back to you on how this goes...

Thanks!

 

[irethedo]

Thanks Paul!

For item 3 above, I added a problem code footer and placed the following into it:

text box: =Count([ProblemCode1])
followed with the label Problem Code:
and then text box: =Sum([ProblemCode1])

I originally had the assembly info in the assembly header but found it to repeat this information for each record when all I wanted was a total, so I placed this in the assembly footer.

I would like to have the assembly number and its first pass data listed in the assembly header
and then have the problem codes associated with each assembly listed below:
------------------------------------------------------

Operation xxxx first pass yield = x%
start date: xxxx end date = xxxx

Total units = x total failed = x total passed = x

Assembly # XXX-XXX first pass yield = x% total # units = x total failed = x total pass = x
x Units failed due to problem code xxx
y Units failed due to problem code xxx
z Units failed due to problem code xxx
------------------------------------------------------

so I added the problem footer under the assembly footer but now it display the assembly number and its first pass yield info and each problem code as follows:

---------------------------------------------------
Operation xxxx first pass yield = x%
start date: xxxx end date = xxxx

Total units = x total failed = x total passed = x

Assembly # XXX-XXX first pass yield = x% total # units = x total failed = x total pass = x
x Units failed due to problem code xxx

Assembly # XXX-XXX first pass yield = x% total # units = x total failed = x total pass = x
x Units failed due to problem code xxx

Assembly # XXX-XXX first pass yield = x% total # units = x total failed = x total pass = x
x Units failed due to problem code xxx
---------------------------------------------------------

How can I get this into the above shown format?

Thanks again!

 

[PaulBricker]

Sorry, I was out of the office all of yesterday. I'm just catching up with this.

1. In the table, do you have default values set for the date fields? That would autopoplulate the fields and could cause the problem we are seeing.

2. Do you close the form at some point after the report is opened? I ran a report with 64 pages and put a form reference in a textbox and it showed properly on every page.

3. I think the problem code footer needs to be inside (above) the assembley number footer. Also, look at the Hide Duplicates property for the textboxes if you are seeing data repeated for each record. That may help get the info in the desired layout.

Paul

 

[irethedo]

Thanks Paul...

1. I did have the table default for the date field set to date() but I removed this and there is no difference, however I have the unbound start date and end date text boxes on the form that calls the query set to short date format as I needed to force the date to a certain format to ensure the report would pick up the right data. Other wise if a user entered 1-1-2005 the report would not pick up records with a 1/1/2005 date field.
The dates are still not displayed on the subsequnet pages, how can I force that input format but remove the formay from the text box properties?


2. This problem with the blank operation no longer appears... I cleared out the records in the table and placed some new ones in there ... must have been caused by one record that I overlooked.

3. When you say that the problem code footer needs to be inside (above) the assembley number footer, do you mean that it appears vertically on the report above the assembly footer -between the assembly header and assembly footer(if I had an assembly header) or above it on the sorting/grouping box?

I combined the text label and text box fields into one text box so that I could set it to no duplicates but it still displays:


Assembly # XXX-XXX fpy = x% # units=x failed=x pass=x
x Units failed due to problem code xxx

Assembly # XXX-XXX fpy = x% # units=x failed=x pass=x
x Units failed due to problem code xxx

Assembly # XXX-XXX fpy = x% # units=x failed=x pass=x
x Units failed due to problem code xxx

 

[PaulBricker]

1. What is the format for the Date field in your table? Sounds like you have the time in with the date. If that's the case, then you might want to move the data into a query so you can format a field in the query to return the date the way you need. You could use something like this in a query Field
MyDate:CDate(Format([Date/TimeField],"mm/dd/yyyy"))

This would return your dates in the format
04/06/2005

Then on the form, you can use an input mask to force the user to enter the date in the form you need.

2. OK, no more blank record?

3. When I say inside the Assembly Number footer, I mean above the Assembly Number footer. It would look like this.

Operation Header xxxx     first pass yield = x%
start date: xxxx    end date = xxxx

Total units = x   total failed = x   total passed = x

Assembly # Header  XXX-XXX   first pass yield = x%   total # units = x   total failed = x  total pass = x
[b]Problem Code Footer[/b]
    x  Units failed due to problem code xxx
    y  Units failed due to problem code xxx
    z  Units failed due to problem code xxx
[b]Assembly Number Footer[/b]

I'm not sure, with all the Problem code values, that it's going to be easy to list them all. You might be better off using a subreport in the Problem Code footer to display the values.
To do something like that, you would create a separate report that lists the problem codes for the same date period as your other report, and then drag and drop the report into the problem code footer. Make the Assembly number field your Parent/Child relationship field.

Paul

 

[irethedo]

Ok Paul...

I made a sub report with only the product code info and then I opened my report in design view. From the main menu which displays all my reports, I dragged the sub report into the problem code footer section and all that is displayed in design view is the name of this sub report.

I then went into my form which launches the report and entered the start date and end date and clicked the button to review the report.

The report displayed as normal for the operations that only had one problem code per assembly but when I clicked to go to page 2, there was an operation that contained an assembly with multiple problem codes...

Then I was asked by the query for the start date, end date, and assembly number... once entered it displayed what I am looking for...

This is real close but I think I am missing how to link the parent/child relationship for the dates and assembly number....

Please advise

Thanks

 

[PaulBricker]

Open the Main Report in design view. Then click on the upper left corner of the subreport ONCE. Open the properties box, and look for the properties, Link Child Field and Link Master Field. Click on the Link Child Field property and you should get a dialog box that allows you to select which fields you want to use as the link. I believe you want to use your Assembly number because the Problem codes are related to that.
Try it out and let me know.


Paul

 

[irethedo]

Thanks Paul...

With the sub report linked to the assembly number it now displays the problem codes associated for each under the assesmbly first pass yield info, but I noticed that it sometimes repeats itself...

I am unable to really check out how well this is working because I am still plagued with that date issue where the start and end dates called out by the form do not show up on subsequent pages of the report which now makes the query ask me for this and the assembly information for however many instances are on the next page of the report before allowing mr to view the next page of the report..

it's getting close...