UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> User Defined Input Form, Access 2016    
 
   
LagoDavid
post Jul 11 2019, 11:45 PM
Post#1



Posts: 521
Joined: 12-October 03
From: Texas


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.
Attached File(s)
Attached File  SampleDataCollectionDemo.zip ( 415.65K )Number of downloads: 10
 
Go to the top of the page
 
ADezii
post Jul 12 2019, 07:57 AM
Post#2



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


It may help to Upload some Sample Data from the Excel File also.
Go to the top of the page
 
projecttoday
post Jul 13 2019, 05:27 AM
Post#3


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


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.

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Jul 13 2019, 07:00 AM
Post#4


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


" 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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
LagoDavid
post Jul 13 2019, 08:19 AM
Post#5



Posts: 521
Joined: 12-October 03
From: Texas


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.

Go to the top of the page
 
GroverParkGeorge
post Jul 13 2019, 08:36 AM
Post#6


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Jul 13 2019, 08:52 AM
Post#7


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


I think this is doable with a subform.

--------------------
Robert Crouser
Go to the top of the page
 
ADezii
post Jul 13 2019, 09:29 AM
Post#8



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


IMHO, we need results based on the Sample Data that you provided so we can see exactly how this process occurs.
Go to the top of the page
 
GroverParkGeorge
post Jul 13 2019, 10:27 AM
Post#9


UA Admin
Posts: 35,290
Joined: 20-June 02
From: Newcastle, WA


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

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
LagoDavid
post Jul 13 2019, 11:10 AM
Post#10



Posts: 521
Joined: 12-October 03
From: Texas


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.
Go to the top of the page
 
LagoDavid
post Jul 13 2019, 11:13 AM
Post#11



Posts: 521
Joined: 12-October 03
From: Texas


I will work on getting some example data into the demo database.
Go to the top of the page
 
projecttoday
post Jul 13 2019, 12:47 PM
Post#12


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


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

--------------------
Robert Crouser
Go to the top of the page
 
LagoDavid
post Jul 13 2019, 03:09 PM
Post#13



Posts: 521
Joined: 12-October 03
From: Texas


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.
Attached File(s)
Attached File  SampleDataCollectionDemo.zip ( 507.39K )Number of downloads: 2
 
Go to the top of the page
 
tina t
post Jul 13 2019, 03:17 PM
Post#14



Posts: 5,982
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
projecttoday
post Jul 13 2019, 03:24 PM
Post#15


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


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?

--------------------
Robert Crouser
Go to the top of the page
 
LagoDavid
post Jul 13 2019, 04:08 PM
Post#16



Posts: 521
Joined: 12-October 03
From: Texas


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?
Go to the top of the page
 
LagoDavid
post Jul 13 2019, 04:13 PM
Post#17



Posts: 521
Joined: 12-October 03
From: Texas


Hi Crystal
See if this helps
Attached File(s)
Attached File  Relationships.pdf ( 42.24K )Number of downloads: 2
 
Go to the top of the page
 
projecttoday
post Jul 13 2019, 04:24 PM
Post#18


UtterAccess VIP
Posts: 10,774
Joined: 10-February 04
From: South Charleston, WV


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?

--------------------
Robert Crouser
Go to the top of the page
 
ADezii
post Jul 13 2019, 04:31 PM
Post#19



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


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

Not seeing frmDataSets in the Sample DB.
Go to the top of the page
 
ADezii
post Jul 13 2019, 05:33 PM
Post#20



Posts: 2,457
Joined: 4-February 07
From: USA, Florida, Delray Beach


Sorry, got it.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 05:56 AM