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]

I would say the first thing is to make sure the Form is still open so that the report can get the values from the Form. That is the only reason I can see that the dates wouldn't be availble to the textboxes. What version of Access are you using.
Let me know about the form and in the meantime, I'll see if I can Google anything else that might be an issue.

Paul

 

[irethedo]

Paul-
After you mentioned it, I checked it out and it so happens that the form that calls the report closes right after it launches the report which would explain why this is happening...

Any ideas of what to check and or change to keep this form open?

 

[PaulBricker]

Something is closing the form (meaning it doesn't close by default), and if you launch the report from a button on the form, then it is probably something in the Click Event for the button that is closing it.

Paul

 

[irethedo]

Well Paul...

I found the culprit... there was a little line hiding in my code that was closing the form and now that it is staying open, the dates are working...

I have a bit of work to get all the totals to work out but I will keep you posted on this...

Thanks for all your help...

You are not only an expert but a very patient and helpful person and I appreciate all you have done to guide me through this!

 

[PaulBricker]

Glad to hear it. Let me know how it goes.


Paul

 

[irethedo]

Ok Paul...

I am still investigating the calculations and this may take me a while but I notice that the blank record with the blank operation is still showing up in the report. Is there a way to add something to my query that only includes records that their operation is not NULL?

Also, I was wondering if there is a way to advance to a new page for each operation in the report without screwing upi the totals..

Thanks for all of your advice...

 

[PaulBricker]

1. In the underlying query, on the Criteria line for your Operations field you could put
Not Is Null
That should get rid of the record. If not, we can test for Nulls in the report and probably hide it.

2. In the properties box for your Operation Header(click on the Header bar to see the properties for that section), there should be the option to Force New Page. Set it to Before Section and see if that does it.


Paul

 

[irethedo]

Paul-
The not is null operation query worked great and I chose to force a new page after the operation footer to eliminate a nearly blank first page in my report...

I have a few calculations yet to fix but here is how the report is right now:

======================================
Operation xxxx First Pass yield
Start date: mm/dd/yyyy end date: mm/dd/yyyy xx%

Total # units = 16 Total failed = 11 Total passed = 5

Assembly number = 12345 FPY = 1 Passed = 1 failed = 0

Assembly number = 23456 FPY = 1 Passed = 1 failed = 0




Assembly number = 654321 FPY = 1 Passed = 0 failed = 2
1 Units failed Due to Problem Code: xxxxxx

Assembly number = 654321 FPY = 1 Passed = 0 failed = 2
1 Units failed Due to Problem Code: yyyyy

Assembly number = 654321 FPY = 1 Passed = 0 failed = 2
1 Units failed Due to Problem Code: bbbbbbb

================================
The operation Header of the report contains the operation, first pass yield of operation, start & end dates, and total # of units line.

The "Assembly number =" lines are located within the Assembly Number footer.

and the "x units failed due to" lines are located in the subreport within the problem code footer.
=============

I have some problems with the sub report displaying all failures for all records within an operation for each and every "Assembly number =" lines so I added a child-parent link to the operation along with the assembly number and this helped out quite a bit...

The problems that I am still trying to figure out are:

1. Some but not all of the "Assembly number =" lines are being displayed for units that passed and I want to only display the failed assemblies.

2. First pass yield calcuations on the "Assembly number =" lines is not correct.

3. Some of the "Assembly number = " lines are not displaying the subreport with the failure information.

4. The "Assembly number = " lines repeat on the report for each type of failure per operation where I would like to only display the "Assembly number = line" and then the sub report of all errors for that assembly number such as:
------
Assembly number = 654321 First pass yield = 0 Total Passed = 0 Total failed = 2
1 Units failed Due to Problem Code: yyyyy
2 Units failed Due to Problem Code: xxxxx
1 Units failed Due to Problem Code:zzzz

Assembly number = 123456 First pass yield = 0 Total Passed = 0 Total failed = 1
1 Units failed Due to Problem Code: yyyyy
------

 

[PaulBricker]

1. So from what you say, can I assume that you do not want Assembly Number values 12345 and 23456 to show in the report? We might try setting the visible property for those to No. In the Assembly Number Footer property, you would do something like this in the Format Property.

IF [Passed] = True Then
[Assembly Number].visible = False
FPY.visible = False
Passed.visible = False
Failed.visible = False
Else
[Assembly Number].visible = True
FPY.visible = True
Passed.visible = True
Failed.visible = True
End If

2. What should the FPY value be?

3. Not sure what's happening here. It seems like a relationship problem but that's just a guess. Are there failures to be displayed for those values?

4. Can you try setting the Hide Duplicates property for the Assembly Number control to Yes and see if that helps.


Paul

 

[irethedo]

Thanks Paul-

1. The field that I want to make visible or invisible is a string called text93 and contains the following data:

-------
=" Assembly Number = " & [Assembly Number] & " First Pass Yield = " & (Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))) & " Total Failed = " & Sum([Fail Quantity]) & " Total Passed = " & Sum([Pass Quantity])/Count([Assembly number])
---------

I tried your suggestion but changed it to:

--------
Private Sub GroupFooter4_Format(Cancel As Integer, FormatCount As Integer)

If [Pass Quantity] = True Then
Text93.Visible = False
Else
Text93.Visible = True
End If

End Sub
---------

But it chokes on the "IF [Pass Quantity] = True" line.

It gives me a run time error 2465 - "Data Collection can't find the field 'Pass Quantity' referred to in your expression.

So I added a field in the this footer and linked it to =[Pass Quantity] but still no cigar...
---
2. The FPY should be total number passed (of that assembly) divided by total number tested (for that assembly) and the code I use for this is in the text93 string above.

I also tried to break these out into individual label/variables but this did not correct the problem...

3. Yes there are failures to display for these and I will look at it closer to make sure I am not doing something bone headed again..

4. How can I hide the properties for assembly number when it is part of the whole text string text93? I have that control set to hide duplicates but it doesn't seem to work... (by the way, I do want to make the whole line of text text93 invisible)

There must be something simple that I am overlooking here and it is so close too..

 

[PaulBricker]

1. I may have messed up a little here as well.
If [Pass Quantity]=True isn't really correct. I suppose it should be If [Pass Quantity] > 0 Then we want to hide the control. But, it seems you can't find [Pass Quantity] so what you might want to do is put a textbox in your footer and set the control source to Pass Quantity and see if it sees anything. If you can't see a value, then the calculation you do in Text93 won't be accurate.
2. Again, I think you need to make sure you are getting an accurate [Pass Quantity] to get the accurate FPY
3. Let me know how you make out.
4. Can you move the "Assembly Number =" text out of the expression in Text93? Maybe put it in it's own textbox and try controlling it's visiblity that way?

Paul

 

[irethedo]

Paul-

It chokes on the statement "If [Pass Quantity] > 0 Then" too... I already added a textbox named pquant which has the control source of pass quantity

After further investigation I discovered that I made another mistake. I had the source for this report pulling from the wrong query.. once I corrected this I now get the following error:

Run-Time error '2465' Data Collection can't find the field "|" referred to in your expression

in the same line of code regardless if I have "> 0" or "= True"

It seems to me that the big problem is this section of the report not seeing [Pass Quantity] and I will not know if the fpy calculation or any of the other related calculations will work until this field is recognized...

After changing to the correct query, I notice that the report only displays one operation now instead of each one at a time so I am going to have to retrace my steps on that one...

Thanks for your help Paul

 

[PaulBricker]

It doesn't look like you have a detail section in this report. I'm wondering if you could put a textbox in that section with the Control Source set to [Pass Quantity] and see if you can see it in the detail section. Pass Quantity is a field in the table so you should be able to see it in any Header/Footer/Detail section of the report.

Sorry, I'm grasping at straws here.

Paul

 

[irethedo]

That was it Paul...
The text box associated with Pass Quantity had to be in the detail section.. awesome!

I notice there is a gap in the report where these would have been displayed if they were visible but I can probably figure out how to deal with that...

I will not know if the FPY calculation is working until the
"Assembly Number = xxxx FPY = xxx Total failed = xx Total passed = xx" line has all of the data for this assembly instead of displaying the data for each individual problem code.

In other words, the format that I am looking for is:
--------------
operation xxxx
start date end date fPY for operation

Assembly Number = xxxx FPY = 60 failed = 4 passed = 6
2 units failed due to problem code xxxx
1 unit failed due to problem code yyyy
1 unit failed due to problem code zzzz

-------
Instead of what I have right now which is like this:

-------
operation xxxx
start date end date fPY for operation

Assembly Number = xxxx FPY = 0 failed = 2 passed = 0
2 units failed due to problem code xxxx

Assembly Number = xxxx FPY = 0 failed = 1 passed = 0
1 unit failed due to problem code yyyy

Assembly Number = xxxx FPY = 0 failed = 1 passed = 0
1 unit failed due to problem code zzzz
-------

as with the current format, each assembly number is
only a failure of a specific type with no passes which would make the FPY always = 0...

I may have to rethink that "If [Pass Quantity] > 0 Then" to "If [Pass Quantity] > 0 and [Fail Quantity] = 0 Then", also add a hidden text box for Fail quantity in the detai lsection, and make the text93 default visible so that it is only hidden when there is a pass with no failures..
.

 

[PaulBricker]

I'm scratching my head a little over the format for the Problem codes. I would have expected it to be the way you want by default, but obviously it isn't. I'm not sure what to tell you at this point. What I will do is try and make up a report that is grouped the way yours is and see if I can duplicate either setup with an eye towards getting it the way you want.
I'll try and post something this afternoon.

Paul

 

[PaulBricker]

I can't seem to get a textbox to repeat itself in the footer even when I have a subreport linked to it. I'm not sure why you are seeing that Assembly number value repeated for each record in the subreport. The LinK Child/Link Master is set to Assembly Number?? That's what you say, but you might want to double check to be sure. Is the Assembly number in your subreport? I doesn't have to be there. You can still link the subreport without the field actually in the report. I'm running out of ideas. Let me know if you come up with anything.

Paul

 

[irethedo]

Paul-

I linked the Child and Parent link to Assembly number, operation, and problem code...

When I only had assembly number in there it was spitting out every problem code for each operation under all operations.

I also tried adding a text box in my assebmly footer that linked the assembly number and set it to hide duplicates but it repeats itself after each sub report is hown..

 

[PaulBricker]

What field, exactly, do you have in the SubReport? Can you list them out for me please.

Thanks

Paul

 

[irethedo]

In the subreport I am trying to capture each problem code and the number of times this has been recorded for each assembly number for a specific operation so I have the following filed set up in my sub report:

Field Control Source:
------- -----------------------------
pcount - =Count([ProblemCode1])

pbcode - ProblemCode1

Also in the footer where the subreport is, I have two invisible fields one with the control source of Assembly Number and the other is Operation.

The child/parent links between the report and the subreport are operation, assembly, and problem code in that order (if order matters)

 

[PaulBricker]

Try getting rid of the Problem Code in the Child/Master Link and see if that helps. You shouldn't need that link to get the problem codes associated with the Assembly Number.
Let me know.

Paul