Changing Report format based on a field value?.

[Kozbot]

My goal is to generate a certificate of analysis from a quality control database. There are different products each requiring a different tests (meaning different fields are entered) in the database. My report will be based on a query for a specific lot number for a product which will average the quality control values entered for that lot number.

I want the report to only show the averaged fields that are relevant for that product. For example, product PE-500 requires tests A,B,C,D, and E and I want those averaged values to be displayed with the report is on PE-500. However if I select another product, PS-500, which only requires tests A,B, and C. The report should display these values, preferably in a neat format (like the values required for other products didn't exist)

Thank you!

 

[Kozbot]

If your table were properly normalized, this wouldn't be a problem since the variables would be rows rather than columns. With a "flat" schema, you might be happier with separate reports and so you run report1 for type1 and report2 for type2 data.

I believe my tables are normalized as I understand the term.

My tables are like are Products, Lot Numbers, and QC Data. Every product has multiple production runs (which means different lot numbers) and the QC Data gets (up to dozens of entries over time of production) entered for each different lot.

I am running reports for every different product we have, which is around 250. Each of those 250 products have the same core group of tests required with some requiring a little less and some a little more. The reports are going to average the values for each different test over the course of the production run for a product, and the format of the report needs to slightly change based on which product is getting reported.

Basically I'm wondering if its possible to change the positions of the field boxes on a report if "Product X" is in the Product field. I know its possible to disable the visibility of the field box if certain conditions are met. I want to know if I can alter the format of a report entirely

 

[pr2-eugin]

I agree with Pat, I think there might be something wrong.. BUT, I am not sure, as I have not seen any sample data.. So, is it possible for you to share the Relationships diagram and some sample Data?

I think you have the QCData table data as..

dataID    lotID    testA    testB    testC    testD    testE
1        PE-500    50    50    50    30    70
2        PS-500    50    20    30

On contrary your data should have been stored as,

dataID    lotID    testType    testScore
1        PE-500    testA    50
2        PE-500    testB    50
3        PE-500    testC    50
4        PE-500    testD    30
5        PE-500    testE    70
6        PS-500    testA    50
7        PS-500    testB    20
8        PS-500    testC    30

So if you want average it would be simple as..

SELECT lotID, Avg(testScore)
FROM QCData
GROUP BY lotID

 

[Kozbot]

My QC Data table

EntryID   Product    LotNo    Time  BoxNo  TestA   TestB   TestC  TestD
1            PE-500   1212       8:00   1         50       75       80
2            PE-500   1212       8:30   2          55      80       85

the QC Data table is the same for all the products however the tests required varies slightly for each product. for example another product PE-900 does not require C but requires D. For PE-900 entries Test C is just left null.

EntryID   Product    LotNo    Time  BoxNo  TestA   TestB   TestC  TestD
1            PE-900   1213     8:00    1         50      75                  30
2            PE-900   1213      8:30   2         55      80                  25

The QC Table relates to the lot numbers table where every lot number is unique (primary key) and the lot numbers table relates to the product table with each product having multiple lot numbers.

The report is going to average the values entered for a particular lot number(it is a certificate of analysis) and display them like this

Product: PE-500
LotNo:1212
Test A Avg:xx
Test B Avg:xx
Test C Avg:xx

however when the report is on PE-900 the report needs to reformat to this

Product: PE-900
LotNo:1213
Test A Avg:xx
Test B Avg:xx
Test D Avg:xx

because we don't want to display null fields in the report as well as not letting certain customers know those tests are performed on other products.

I know you can make controls invisible based on if the Product field is xx. But can you change the positioning of the record box on the report, or design different report formats and have different ones automatically selected based on the product type

 

[pr2-eugin]

So technically it is a design issue.. Your table is not properly Normalized.. If you re-read my post #4 it might help you see what I mean..

 

[Kozbot]

So technically it is a design issue.. Your table is not properly Normalized.. If you re-read my post #4 it might help you see what I mean..

I don't see how I can do this with time series data. Every record stored is a from a sample taken at a specific time (with different tests being performed at different intervals). Also some tests are quantitative, some qualitative, some numerical, some text. How would I account for that by putting the tests values in one column? Should I have created different tables for each product?

Getting the average values I can do by a simple query. My question is about changing the format of the report.

 

[Solo712]

My goal is to generate a certificate of analysis from a quality control database. There are different products each requiring a different tests (meaning different fields are entered) in the database. My report will be based on a query for a specific lot number for a product which will average the quality control values entered for that lot number.

I want the report to only show the averaged fields that are relevant for that product. For example, product PE-500 requires tests A,B,C,D, and E and I want those averaged values to be displayed with the report is on PE-500. However if I select another product, PS-500, which only requires tests A,B, and C. The report should display these values, preferably in a neat format (like the values required for other products didn't exist)

Thank you!

You might find this post useful. It makes controls visible on a report based on one of more criteria. It will not reformat the rendered report section but it will suppress unneeded controls in it.

Best,
Jiri

 

[Kozbot]

Here is an example of the handwritten QC Form that I am trying to emulate in my QC Data table with Access.

http://imgur.com/pK8V43W