How select pre-defined record or create custom one if necessary? (1 Viewer)

[gringope24]

Dear Folks,
in my DB i have tblProtocols (it stores informations/measurements collected by technicians), for each protocol there should be criteria to check if the measurements are OK or not. There are several types of criterias acc. to which we can evaluate it: 1. International Standards (predifined several records) / 2. Producer (predifined several records)/ 3. Custom.
If there will be Int. standards or Producer's it will be no issue, because when user choose one of this I can check what is the type of the device to check the Standard, when criteria type 1 are selected (or producer when criteria type 2 is choose) and create a query that returned me correct criteria.
Unfortunately, I have not figured out how handle issue when user choose a 3. Custom. Shall I create separate table (besided tblCriterias it would be also tblCustomCriterias) and when user choose 3. than a record in this table would be created? And the other thing... If I would like to present criterias in form, how to solve problem that depand if it is Standard/Producer's/Custom, there is different recordsource of form/subform.

Does anyone of you face such problem?

 

[GPGeorge]

By definition, what you are trying to implement is extremely hard to do in a relational database application. "Custom" means there are no other sets of criteria exactly the same. You don't mention whether this involves both KINDS of measurements and STANDARDS for those measurements, or simply different standards for a common set of measurements.

Relational database applications, on the other hand, are quite good at adding new data into previously designed and already built tables. Unless you can break your criteria down in such a way as to create tables of measurement types as well as standard for each of those types, you won't get far, I'm afraid.

So, to get further help, we'll probably need to see real examples (not made up ones) of the TYPES, or KINDS of measurements involved in the criteria and actual STANDARDS. Thanks.

Also, some samples of the actual tables that now exist along with representative data would be very useful to anyone trying to offer suggestions.

 

[LarryE]

You don't need a separate table for custom. Each criterion should have a criteria type in tblCriterias:

1. Create a field named CriteriaType in tblCriterias with a Data Type set to Numeric. Format it as Standard with 0 decimals.
2. Open your criteria input form in design mode and add the new field to the Record Source.
3. Add an Option Group control on your form and use the wizard to define which options are available. The Control Source will be the new CriteriaType field and the options will be:
   1. International
   2. Producer
   3. Custom

So for each criteria description entered, the user will also select a CriteriaType from the CriteriaType option group. Now you have a way to identify each criteria by type and can then include those types in queries.

 

[CJ_London]

There was a similar question to this a few months ago. But agree with GPG - need to see some representative data for custom protocol criteria.

A possible solution to the similar question was to use the eval function

see this link as a possible way forward

Best practices for behind-the-scenes calculations of values with easily-changeable variables.

Greetings, accessfolk! First post here, and I am an Access newbie (beyond, you know, the basic 'lol make table, lol make form, clicky clicky relationships'), so please tailor your responses accordingly and please don't feel offended if I say something that violates your sensitivities on how a...

www.accessforums.net

 

[gringope24]

So far, I have already only table with predefined criteria from standard and from producers. There are not going to be much data here and, as you see below:

For custom values I don't have real data now. My idea was that user can choose predefined criteria or type it by his own.

 

[CJ_London]

without knowing how you use your criteria or what other types of criteria might be involved, difficult to advise. If these are the only criteria columns and the only thing that changes is the values then you can use the eval function or perhaps a calculated field. If the number of columns can change then you need to change your design to store the data vertically

criteriaValuePK
criteriaTypeFK (international/producers/custom1/custom2)
manufacturerFK
criteriaDetailFK
criteriaValue (as text if types can vary, see next table)

then a separate table to store details about the criteria

criteriaDetailPK
criteriaName (per your column headings)
criteriaType (text/long/double/currency/etc)


you would then perhaps use a crosstab query to display the data horizontally as per your table and do the calculation there

 

[gringope24]

In my frmProtocol I have sfrmMeasurements. I was thinking to have also sfrmCriteria so that visual comparison of values would be possible (maybe also conditional formatting).
There would be also similiar presentation in the report.

 

[Pat Hartman]

I don't see anything in your criteria table that would let you join the the measurement table. How would you know what criteria to use?

 

[gringope24]

Here are relation between these 3 tables.

I was also thinking about creating tblDefinedCriterias and when user will open some popup frmCriteriaInput and choose from combo International/Manufacturer then controls in the form would filled by values from tblDefinedCriterias using Dlookup function in AfterUpdate Event .
If user will choose Custom in combo then he would enter the value by himself.
Of course it will cause denormalization of data but maybe it would not be critical.

EDIT:
The other problem I found that, whatever, criterias will be just pull from tblDefinedCriteria or entered by User, it means that new record will be created. In consequence 1 tblProtocols will have 1 record in tblCriteria. I expect that 1-to-1 relationship is not so common and efficient. Need your advice.

 

[gringope24]

Shall I provide some additional info or something is not clear?

 

[Pat Hartman]

tblMeasurements needs to be in a subform of the subform so it syncs with the subform. If you wanted to add custom criteria, you would need to base the criteria subform on a union query that unions tblMeasurements and tblCriterias. I would not make a separate table for the custom criteria. I would add a flag to the tblMeasurements so you can identify which is which.