Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Forms _ User Defined Input Form

Posted by: LagoDavid Jul 11 2019, 11:45 PM

I am trying to make an application to hold lab data to replace what we now do clumsily in Excel.

Samples are routinely collected and analytical tests are run on the samples. The work is mostly repetitive; that is much of the time when a set of samples is collected the same tests are run on that set. So each set of samples typically gets the same tests on some routine basis (daily, weekly, monthly, etc). I want to allow the user to make "working templates" which pair up the sample name with the test name, and then be able to use that "working template" as the basis for a data input form with spreadsheet style rows and columns. Each cell of the spreadsheet would be the data record of "Result" for the intersection of the data pair SampleName and TestParameter.

I have been able to successfully create the forms which will allow the user to create the working templates (and template details) (i.e. which samples are collected and which tests are run on each sample). However, after hours of effort I cannot even come close to figuring out how to make data input forms based on the templates.

I know this may not be a clear explanation, so I created a mock database (attached) and the opening form is a mockup of what I am hoping to be able to have the user create.


Other ideas of efficient data input forms are solicited also. If I am headed in a bad direction please say so. This is just the manner in which we are used to working when using Excel as a flat file data collection tool.

 SampleDataCollectionDemo.zip ( 415.65K ): 11
 

Posted by: ADezii Jul 12 2019, 07:57 AM

It may help to Upload some Sample Data from the Excel File also.

Posted by: projecttoday Jul 13 2019, 05:27 AM

Does this database work now? Which table contains the actual measurements that are being collected? What distinguishes one user from another that necessitates creating a template for each user? Give an example.

Posted by: GroverParkGeorge Jul 13 2019, 07:00 AM

" I want to allow the user to make "working templates" which pair up the sample name with the test name, and then be able to use that "working template" as the basis for a data input form with spreadsheet style rows and columns. "

Unfortunately, a relational database application that requires CONSTANT creation of new forms probably reflects an inappropriate table design. That's especially true if the USER is expected to create those new forms.

We'll look at the design, but even without that, the description above suggests we're headed down the wrong path conceptually.

Sample data, as requested, would be helpful, too.

Posted by: LagoDavid Jul 13 2019, 08:19 AM

It is not the user that causes the need for varying input templates, it is the specific set of samples and analyses which are required for any sample collection set. We do not collect the exact same samples nor perform the exact same set of analyses every time.

Please take a look at the sample database and see the data collection Templates (header records) and Template Detail records for examples (open frmServiceTemplateList). I am fairly confident that my table design is good; at least not fatally flawed. I included a relationships window in the sample database, hoping to make this less time consuming for anyone attempting to help.

tblSamplePoints - to store the names of the various sample points
tblSamples - to store the exact date/time that the actual sample that is collected
tblSampleResults - to store the names of the analytical parameters performed on the sample and the results of the tests that were performed

tblAnalyticalParameters

tblDataCollectionTemplates (essentially a header record)
tblDataCollectionDetails (the "data pairs" of samplePoint and Analysis that are frequently used)

Without a template, in order to get data into the tblSampleResults, for every record of data the user would have to select the sample point (from maybe 50 samples), select the analytical parameter (from approx. 100), and then enter the result. Since many of the data pairs of Sample/analysis are repetitive, I am hoping to use templates to make data entry easier for the user. I have seen a couple of commercial LIMS databsases which use this concept, but I do not know how it is done.

Thank you for looking.


Posted by: GroverParkGeorge Jul 13 2019, 08:36 AM

I see. No two samples are analyzed the same way, ever.

That's a monster of a situation to have to model in a database, on the surface at least.

Perhaps, once we get a few sets of sample data, we can begin to help you identify patterns that can guide a usable table design.

Posted by: projecttoday Jul 13 2019, 08:52 AM

I think this is doable with a subform.

Posted by: ADezii Jul 13 2019, 09:29 AM

IMHO, we need results based on the Sample Data that you provided so we can see exactly how this process occurs.

Posted by: GroverParkGeorge Jul 13 2019, 10:27 AM

Yes, I agree. A main form/subform design should support it without adding new "templates".

Posted by: LagoDavid Jul 13 2019, 11:10 AM

Form based on what table and subform based on what table?

I am trying to avoid having the user have to select the sample and select the test analysis each time before he can input the test result. I am not grasping how that can be done with a form/subform.

Posted by: LagoDavid Jul 13 2019, 11:13 AM

I will work on getting some example data into the demo database.

Posted by: projecttoday Jul 13 2019, 12:47 PM

I took at look at this db again. I think you need to post the database that works.

Posted by: LagoDavid Jul 13 2019, 03:09 PM

Robert, I am not sure what you are seeing as a problem? The database works for me. I just did not include any data yet because I was still trying to work on the data entry process using the template concept, rather than the "normal" data entry process.

Here it is with sample data. I created a "normal" form/subform to enter the data (frmDataSets).

You can see that using the form, for each data set the user must select the sample points and then select the test parameters for each sample point before he can enter a test result. Since much of the data sets are repeated weekly or monthly, I am just hoping to improve the data entry process by creating and using some data entry templates.

I apologize if I am making this difficult. I tried to make this easier by including a demo but apparently I am still not being clear.

Please note that in my table names and form names I use the term "FIELD". This does not refer to a database field; rather to an analysis that is done in the "field" (outside location) rather than in a central lab.

Thank you again for looking.

 SampleDataCollectionDemo.zip ( 507.39K ): 3
 

Posted by: tina t Jul 13 2019, 03:17 PM

hello, David, i can't justify (to my boss) downloading your example db on my work PC, but i'd be really interested in seeing a screenshot of your Relationships window, if you want to post it. :)

hth
tina

Posted by: projecttoday Jul 13 2019, 03:24 PM

Maybe I should have said post a database that doesn't have anything in it that doesn't work.

There are nine forms in this database and five of them have "Template" in their name. Correct me if I'm wrong but I got the impression that your templates don't work. Clarify this, please.

QUOTE
You can see that using the form

Which form?

Posted by: LagoDavid Jul 13 2019, 04:08 PM

I apologize for the lack of clarity. "it all seems so clear to me..."

"You can see that using the form..." refers to the data input form frmDataSets. That form is for data input of sample test results; but that form is not based on any data input template. It uses the "normal long method of data input" where the user has to select the sample point and the test parameter before he can enter the test results.

Using a template for data input is what I cannot figure out how to do, and that is the only part that does not work.

I can create what I am calling "templates". You can see several of them with the frmServiceTemplatesList. My templates are just a pairing of sample points with the appropriate test parameters. I can make these "templates"; what I cannot figure out how to do is create a data input form that is based on the template, wherein the sample point and test parameter have been "pre-selected". The process of creating "templates" works fine. But what do I do with the template of field pairs after it is created?

Posted by: LagoDavid Jul 13 2019, 04:13 PM

Hi Crystal
See if this helps

 Relationships.pdf ( 42.24K ): 3
 

Posted by: projecttoday Jul 13 2019, 04:24 PM

Is it based on SamplePointID? If I'm getting it right then when the user makes a selection in SamplePointID you execute code that adds records - minus the measurements - to the samples table which appear in the subform to the right. User then proceeds to input the measurements themselves.

Your "template" then is a table containing the SamplePointID and its various measurements. This is used to add the new records.

Am I on the right track?

Posted by: ADezii Jul 13 2019, 04:31 PM

QUOTE
"You can see that using the form..." refers to the data input form frmDataSets.

Not seeing frmDataSets in the Sample DB.

Posted by: ADezii Jul 13 2019, 05:33 PM

Sorry, got it.

Posted by: LagoDavid Jul 13 2019, 06:41 PM

look at the newest upload
the first upload did not include any data nor any data input form

Posted by: projecttoday Jul 13 2019, 08:23 PM

I don't think my last post was entirely clear. So I'll try again.

Are the measurements which the user enters determined by the selection in the left-hand subform in the column called SamplePointID? And after that, the user has to go down the right-hand subform entering the appropriate measurments for that SamplePoint? The list of measurements varies depending on the SamplePoint. And this is where you want a change. You want the needed measurements to come up automatically once the samplepoint has been selected? Is this correct?

If it is then this is what I suggest: when the user makes a selection in SamplePointID, execute code that adds the records minus the actual measurements to the samples table which appears in the right-hand subform. Requery the right-hand subform and the new records appear. Of course, the actual measurements are missing. User then proceeds to input the measurements.

Your "template" then is a table containing the SamplePointID and its various measurements. This is used to add the new records.


Posted by: LagoDavid Jul 14 2019, 07:46 AM

That is not exactly what I am hoping to do, but the concept may be applicable to getting to where I want to go.

Thank you for your help.

You may see another message in the future about writing this code, but I have a book I will refer to first.

Thank you again.

Posted by: projecttoday Jul 14 2019, 07:50 AM

You're welcome.

Posted by: ADezii Jul 14 2019, 11:21 AM

QUOTE
what I cannot figure out how to do is create a data input form that is based on the template

I apologize since I am a little confused here, but look at the Revised DB and see if I am getting 'close'. It basically replaces lstTemplateDetails on frmServiceTemplatesList with a Pop-Up Form displaying the Template Details.

 SampleDataCollectionDemo_Revised.zip ( 193.78K ): 4
 

Posted by: LagoDavid Jul 14 2019, 06:49 PM

I appreciate your effort, I really do, but it is not what I am trying to get to. what I want to pop up is a data input form with all those same template rows and columns AND with a blank spot to enter the test results that belong in each row (in a crosstab style form that looks like the default opening form in my demo. I will keep working with what Robert said and combined with your form, it will get me mostly there.

Thank you.