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

 

[irethedo]

Paul-

The noticeable difference that I see when problem code is removed from the Child/parent links is that with this removed, the subreport lines: "X Units Failed Due to Problem Code xxxx" is different where the number of units is some unknown number where as with problem code linked these number of units are correct...

Perhaps, the Line on the main report with the assembly information should be on the sub-report with the problem codes. Is there an easy way to set up the subreport so that the assembly info only appears once and the problem codes appear under each assembly line? Not sure if this would work with all of this in a subreport, what do you think Paul?

 

[PaulBricker]

It should work. Essentially, on the subreport, you would group by the Assembly Number in a Group header and then list the Problem Code in the Detail section. Then link the Assembly Number to the Main Report.
Sounds doable.

Paul

 

[irethedo]

Paul-

I tried the sub report with the assembly number information in the sub report instead and I also tried it with the assembly number in an assembly header of the sub report but neither of these displayed much of a difference...

I am not sure what else to try, but I will post something once I figure out what next to do

Thanks for all your help!

 

[PaulBricker]

Well, something has to be getting into the group to separate the records the way you are seeing it. I can't be sure what that is, but what you might want to try is getting rid of the FPY, Failed and Passed info to see if that helps. I think the more info you get in the Group Header, the more the data in the detail area (or subreport in this case) gets pulled apart. Break it down as much as you can and then see what happens.
Sorry I can't give you any other suggestions than that.


Paul

 

[irethedo]

Paul-

I changed the order of the "grouping sort" where the problem code was before the assembly number and then I moved the assembly number info into the assembly header and deleted the assembly footer where this data was located... Then I got rid of the sub report and put the info that was in it into the detail section and removed the problem code header & footer sections.

now the report looks different but closer to what i am looking for...

The problem codes line up under the assembly number but I still having problems with the following:

1. there are large gaps in the report where the passing instances of an assembly are (remember we made these invisible)... not sure if theere is a way to get rid of these or not.

2. If an assembly record for a passing assembly is encountered in the table before a failing one, the "assembly number = xxxx " line gets set to invisible and then any subsequent failues get displayed without this assembly number line above them.

3. the actual number of the units failed in the problem code lines are all the same value and and these are incorrect values. I think this is why we added the subreport in a previous step and it may need to be aded again...

man its close!

 

[PaulBricker]

1. Sometimes you will see gaps because of the size of the section the info is in. For example, if you have a group header, and there is extra space between the Header Bar and the Detail Bar, you will see that space on your report. Keep your section bars as close together as you can without stunting the information. Also, check the Can Shrink/Can Grow properties out. Setting these to Yes may help. (these may not be the cure, but it's someplace to start)
2. That's probably due to having the Hide Duplicates property set to Yes but I'm not sure how to cure it.

3. That is a strange one. If you were using a subreport I would say it's because you don't have your Link Fields set up properly. It sounds like there is something wrong with a Join someplace. Is your report Record Source a Query. If it is, can you post the SQL for it.

Can you try putting a subreport in the Detail section that has your Problem codes in it and see if that works any better than what you have.

Paul

 

[irethedo]

Paul-

in regards to #3 above, my report record source is a query and I would post the SQL for it if you tell me how to do that..

Thanks

 

[PaulBricker]

If you open the query, on the left side of your toolbar you should have buttons that show the query in Design View, SQL and Normal views (or on the Menu bar go to View...SQL view). Select the SQL view and then just copy and paste it here.

Paul

 

[irethedo]

Ok Paul...

I have been playing around with it a bit and actually made some progress on my own shince your last posting..

I just added a subreport to the report in the detail section and it appears to be showing the report correctly now but if more than one failure of a type is found it will record the number of failures correctly but the line with that failure for that assembly will be displayed once for each occurance of the problem..

In other words here is what the report looks like:

------------------------
operation xxxx
start date end date FPY for operation

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

2 units failed due to problem code xxxx

------------------------
This is so close now...

Per your last request, here is the SQL info for my query:

Thanks!

------------------
SELECT [GSI FPY Table].Date, [GSI FPY Table].Operation, Count([GSI FPY Table].[Assembly Number]) AS [CountOfAssembly Number], Sum([GSI FPY Table].[Pass Quantity]) AS [SumOfPass Quantity], [GSI FPY Table].[Fail Quantity], [GSI FPY Table].[Serial Number], [GSI FPY Table].ProblemCode1, [GSI FPY Table].ProblemCode2, [GSI FPY Table].[Part Number], [GSI FPY Table].[Description of Problem], [GSI FPY Table].[Total Failure], [GSI FPY Table].LatePass, [GSI FPY Table].depos, [SumOfPass Quantity]/[CountOfAssembly Number] AS FirstPassYield, [GSI FPY Table].[Assembly Number], Count([GSI FPY Table].ProblemCode1) AS CountOfProblemCode1, [GSI FPY Table].[Pass Quantity]
FROM [GSI FPY Table], [disposition Query]
GROUP BY [GSI FPY Table].Date, [GSI FPY Table].Operation, [GSI FPY Table].[Fail Quantity], [GSI FPY Table].[Serial Number], [GSI FPY Table].ProblemCode1, [GSI FPY Table].ProblemCode2, [GSI FPY Table].[Part Number], [GSI FPY Table].[Description of Problem], [GSI FPY Table].[Total Failure], [GSI FPY Table].LatePass, [GSI FPY Table].depos, [GSI FPY Table].[Assembly Number], [GSI FPY Table].[Pass Quantity]
HAVING ((([GSI FPY Table].Date) Between [forms]![print or view reports]![StartDate] And [forms]![print or view reports]![EndDate]) AND (Not ([GSI FPY Table].Operation) Is Null))
ORDER BY [GSI FPY Table].[Assembly Number];

------------------

 

[PaulBricker]

The issue with the sql is all the fields you Group By. Each one of those tells Access it is distinct from the rest and that you want to see them as distinct. Also, I'm not sure what the [disposition Query] in the FROM statement is, but you don't join it and you don't seem to use it in any of the fields so it would be better if it wasn't there. We are dealing with more fields that I originally was aware of.
What is Serial Number? Is it related to Operation or Assembly Number?
I assume that you have all those fields in the Group By clause because you want to see them in the Report, but we could create another query and join those values to get them into the report.
In the Totals query for your main report, you would just want to see the fields that you actually want to group by and then just the other fields you perform calculations on. Something like this

SELECT [GSI FPY Table].Operation,[GSI FPY Table].[Assembly Number], Count([GSI FPY Table].[Assembly Number]) AS [CountOfAssembly Number], Sum([GSI FPY Table].[Pass Quantity]) AS [SumOfPass Quantity], [SumOfPass Quantity]/[CountOfAssembly Number] AS FirstPassYield
FROM [GSI FPY Table]
GROUP BY [GSI FPY Table].[Operation], [GSI FPY Table].[Assembly Number]
HAVING ((([GSI FPY Table].Date) Between [forms]![print or view reports]![StartDate] And [forms]![print or view reports]![EndDate]) AND (Not ([GSI FPY Table].Operation) Is Null))
ORDER BY [GSI FPY Table].[Assembly Number];

Then the query for the subreport would have the Assembly number field and the problem code fields in it so that you can Group By assembly number and count your problem codes. Then link the subreport to the main report on the Assembly number field. I think with the current setup, you will not get where you need to without some changes to the underlying queries.

Could you send a sample copy of your data to
pbrickeratcomcastdotnet

I could look at it this weekend and see what I could straighten out. It is really frowned on at Tek-Tips to take things off line, but at this point to get where we need to, I don't see any other way to do it.

The above address will need the @ and . in place of the 'at' and 'dot'. You don't want to post an actual email address on the net because spammers will slam you.
What version of Access are you working in.

Paul

 

[irethedo]

Paul-

Sorry I didn't get back to you sooner.. been a busy last few days for sure!

I took your advice and cleaned up that query and now the report looks exactly as I want it too.

I want to thank you for all your help and guidance- I have learned a lot from you and I appreicate your patience and tenacity.

Thank you so much!

Jeff

 

[PaulBricker]

That sound great. Glad I could help.


Paul

 

[irethedo]

Paul-
After a bit of time playing around with this report I did notice one strange thing about it...

If there is more than one problem code it will list the line with this information as many times as the problem code exists... For example:

------------------
Assembly Number = xxxx FPY = 60 failed = 4 passed = 6

1 unit failed due to problem code yyyy
2 units failed due to problem code xxxx

2 units failed due to problem code xxxx
--------------------

where problem code xxxx was found 2 times and the information regarding this problem code is shown 2 times...
and I have hide duplicates set to yes...

the data for this line is as follows:
=Count([ProblemCode1]) & " Units Failed Due to Problem Code: " & [ProblemCode1]


any ideas?

 

[PaulBricker]

Where do you have the expression you are using? If it's in the detail section, that might be the problem, but it's hard to say. It could be a join problem. Is this in the subreport? I'll need a few more details.

Paul

 

[irethedo]

Yes the line containing the problem code information is in the subreport in the Detail section.

The assembly information is in an assembly header section. There parent/ child links between the report and the sub report are: Assembly number, operation and problem code...

 

[PaulBricker]

The way the detail section formats is probably what is creating the problem. Because you have cleaned up the underlying query, you could try moving that calculated textbox to an Assembly Information Footer (I know that was a problem to start with but you have cleaned up the query) and see if that helps. If it doesn't, we might be able to use some code to take care if the problem but I would have to test that out first.
Let me know.


Paul