Dynamic Reporting - drive a report from existing data

[TPII]

First, I am a user that is forced to do development work. I am reasonably familiar with coding VBA into access, but by no means am I a programmer in the true sense of the word.

I have a few issues in a project I am attempting to fix. I have a database with various tables, forms, reports, and queries. One table, SPECS, contains product specifications for my company. so for example, column names like WeightMax, WeightMin, ADMax, ADMin, Productcode. Each product code has its own specs, but not all products have all the same values specified (one product has a weight max, one has an ADMin, etc.) All possible specification properties are columns in the table SPECS and each property has a min and max field so I can test the new production data against it and see if the test value is in or our of spec.

when I make and test a new batch of product, I want to have a dynamic form for entering the test data of new production that only shows the specifications on it that are appropriate for the given PRODUCTCODE in the SPECS table. So I envision a form with a field where I can enter the PRODUCTCODE and have the form look up the values of the specification from the table SPECS and enters the Name of the property, the min and max values, then has a field for the actual value (defined by a lot#). so I have a Table called LOTDATA for current test data and the SPECS table having the specifications.

I also need this to occur when I report the data for a given batch on a report. how do I code the data fields that have no data or specs to not appear on the report (and when I say not appear, I mean take up no room on the form, not just become invisible.)

Please ask any questions and I can provide a sample DB if that would help understand my ramblings.

TPII

 

[dhookom]

Most systems I have worked with or created like this are more normalized. The Weight, AD, etc are field values not field names. Almost every value you might enter into a field like your current ADMin or WeightMax should create a new record in a related table.

If a product has 7 SPECS, there would be 7 records not 7 fields in one record.

Duane
Hook'D on Access
MS Access MVP

 

[TPII]

thanks for the post, it gives me something to think about. I never thought of it that way.

so if I had 7 rows of data, each identifying one property for one product and say 9 rows of data for a second product, is there anyway that I could have the test sheet show each of those properties in a column format for data entry? ie, the layout of the form or report still needs to change dynamically with the changes in each spec for each product.

It seems like you would need to identify an array of sorts to step through....or a loop function of some sort..

check if property one exists, if yes, print/show here....if property two exists.....if not, endif/endloop

does that make sense?

 

[dhookom]

The application would be built something like At Your Survey. I would expect that a product would have a related list of properties that are used for that product. An append query would be used based on the appropriate product/properties list to populate a "results" table for the user to enter values.

Duane
Hook'D on Access
MS Access MVP