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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table Design For Highlighting 2 Closest Values, Access 2016    
 
   
penfold098
post May 29 2019, 06:14 PM
Post#1



Posts: 148
Joined: 5-March 14



Simplified scenario: My client takes three different types of measurements on their samples. Four different technicians repeat the 3 measurements. The two "closest" values for each measurement on a given sample are averaged and recorded for quality control records.

In the database, the client wants to store the 4 individual measurements, as well as the two closest values. The challenge is that the two closest values for each measurement may not always come from the same two technicians. The attachment shows fictional data, with the two closest values within a given measurement and sample highlighted.

At first, I considered two separate tables: one to store the individual readings and another to store the two closest values. But that seems like it would require too much maintenance to coordinate the two tables if any records are updated in the individual-readings table.

I am now considering the following table design:
SampleReadingID (PK -- autonumber)
SampleID (FK)
AnalysisRepetition
TechnicianInitials
MeasurementID -- (ID for MeasureA, MeasureB, MeasureC)
MeasurementValue
QCflag -- (-1 = one of the two closest values used for QC; 0 = not used for QC)

Am I on the right track? Internet searches for similar scenarios came up empty.

Any help you can offer would be greatly appreciated.

Thank you in advance.

Attached File(s)
Attached File  SampleTable.JPG ( 35.81K )Number of downloads: 1
 
Go to the top of the page
 
June7
post May 29 2019, 06:21 PM
Post#2



Posts: 731
Joined: 25-January 16



Data structure is not fully normalized, however, most anything can be done with enough code.

Would be nice to post data as text table instead of image. Or attach a spreadsheet, anything to encourage me to further explore this without having to type data.


This post has been edited by June7: May 29 2019, 06:23 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post May 29 2019, 11:10 PM
Post#3



Posts: 731
Joined: 25-January 16



Consider this test I did with array. The output values would be 1.3 and 1.2.
CODE
Sub test()
Dim aryN As Variant, x As Double, i As Integer, n1 As Double, n2 As Double
aryN = Split("1.55,1.3,1.2,1.0", ",")
n1 = aryN(0)
n2 = aryN(1)
x = aryN(0) - aryN(1)
For i = 0 To UBound(aryN) - 1
    If aryN(i) - aryN(i + 1) <= x Then
        n1 = aryN(i)
        n2 = aryN(i + 1)
        x = aryN(i) - aryN(i + 1)
    End If
Next
Debug.Print n1 & " & " & n2
End Sub

Adapting to your requirement might involve a UNION query to rearrange the 3 fields into a single field, open a recordset of that dataset in descending order, loop through recordset.

This post has been edited by June7: May 29 2019, 11:11 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
penfold098
post May 30 2019, 08:07 AM
Post#4



Posts: 148
Joined: 5-March 14



Cpearson: Thank you for your replies and for the code.
I posted the screenshot merely to help clarify my scenario; I did not expect anyone to provide code. My apologies. I will post a text table next time. doh.gif

You mentioned that my proposed table design was not fully normalized. What would you suggest to improve it?

Thanks again. thanks.gif
Go to the top of the page
 
June7
post May 30 2019, 12:41 PM
Post#5



Posts: 731
Joined: 25-January 16



NOTE: I am not CPearson, I simply refer to his website in my signature.

I am not saying you should redesign structure. It is a balancing act between normalization and ease of data entry/output. "Normalize until hurts, denormalize until it works." I have a laboratory database that also stores data in structure similar to yours, probably even less normalized. For the most part, it is the simplest and most convenient approach to meet our needs. I do have to resort to UNION queries to obtain some desired output.

So build UNION query object and save it. There is no builder or wizard for UNION, must type or copy/paste in SQLView.

SELECT SampleID, AnalysisRep, Tech, "A" AS Src, MeasureA AS Data FROM table
UNION SELECT SampleID, AnalysisRep, Tech, "B", MeasureB FROM table
UNION SELECT SampleID, AnalysisRep, Tech, "C", MeasureC FROM table;

Code logic for the difference calc in recordset loop would be more difficult than the array code I already provided. Simplest may be to open recordset and populate array object with data. Example of opening a recordset and populating array:
CODE
Dim rs AS DAO.Recordset, aryN AS Variant, x As Double, i As Integer, n1 As Double, n2 As Double
Set rs = CurrentDb.OpenRecordset("SELECT * FROM queryname WHERE SampleID=" & Me.tbxSampleID & " ORDER BY Data DESC")
Redim aryN(rs.RecordCount)
For i = 0 To UBound(aryN) - 1
    aryN(i) = rs!Data
    rs.MoveNext
Next
'continue with code to calculate with array

The procedure can be a Function or a Sub. As a Function, a value could be returned to calling procedure or textbox expression. Either can have values passed in as arguments.

What this does not consider is multiple pairs with the same difference. Is there a preference for which pair to return - highest, lowest, mid?

Why are two values in the image left aligned?

This post has been edited by June7: May 30 2019, 12:58 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
penfold098
post May 30 2019, 03:05 PM
Post#6



Posts: 148
Joined: 5-March 14



June7: My apologies for not addressing you properly. doh.gif

The odd left-justification of the 2 random values must be some hidden feature of Office. iconfused.gif I didn't have time to fix it before posting.

I appreciate the suggestions and new code. Now that I think about it, I have to clean up and reformat the data into long form with C scripts before importing into Access, so I may have it compute the 2 closest values too.

I'll look at my table design again as well.

Thanks again for your help.
Go to the top of the page
 
June7
post May 30 2019, 05:09 PM
Post#7



Posts: 731
Joined: 25-January 16



I may have misunderstand your data. Just hit me you say 'three different types of measurements' - how are they different? You want to return 3 pairs, 1 for each measurement? If so, my code could be adjusted to run for each column and UNION query is not needed.

So maybe your data is normalized.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
penfold098
post May 30 2019, 08:24 PM
Post#8



Posts: 148
Joined: 5-March 14



Yes, they are 3 independent measurements, so they can not be pooled. I must store the 3 pairs.

I think I am returning to my idea for 2 tables: one for the "raw" measurements (all 4 readings) and one for the 2 closest values. To get the average, I can run an aggregate query on the "closest values" table. I plan to determine the closest values pre-import with other code and save the results to a separate spreadsheet. Thus, I can import the raw data into one table and the "closest values" directly into another.

It may not be the perfect solution, but I believe it will work.

Thanks for the discussion.
Go to the top of the page
 
MadPiet
post May 30 2019, 09:37 PM
Post#9



Posts: 3,207
Joined: 27-February 09



never mind... thought I had it for a minute. =(
This post has been edited by MadPiet: May 30 2019, 09:42 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st August 2019 - 03:00 PM