Is there a way to 'normalise' my data as it currently is?

We have an Access database that we use for test sample submissions. We have 18 different method we can run, and each method typically has a different number of test runs associated with them (although this is submitter dependant). Given this is the case, our current database only allows for 1 method entry per record so that the method can easily be associated with the required runs; the issue with this way is that any 1 sample can require any amount of methods, which in turn requires more record entries (with our current setup).

I've been looking into a way that I can make it have multiple method selections per record, I know this would be 'denormalised' data but its for the ease of the submitter/requester. I then want to use a query or other means to 'normalise' the data for the testing teams side (i.e. separate the records out by method selected).

The back end table that I have come up with looks a bit like this (it is wider):

I would like it to end up looking a but like this:

SQL code for this query is:

SELECT [Sample SubmissionSingleBatch].AutoID, 
[Sample SubmissionSingleBatch].[Lab Batch ID], 
[Sample SubmissionSingleBatch].[IRIS Batch ID], 
[Sample SubmissionSingleBatch].Requester, 
[Sample SubmissionSingleBatch].Samples, 
GetRunsWanted([Method].[Value],[CFPPLinearRuns],[CFPPRuns],
[CFPP12Runs],[CloudMiniRuns],[CloudRuns],[Conf_CFPPRuns],
[Conf_CloudMiniRuns],[Conf_CloudRuns],[Conf_HFRRRuns],
[Conf_PourMiniRuns],[Conf_PourAutoRuns],[DistRuns],[HFRRRuns],
[PourMiniRuns],[PourAutoRuns],[Pour_MANRuns],[RancimatRuns],
[SFPPRuns]) AS Runs, 
[Sample SubmissionSingleBatch].Method.Value AS Method,
 GetPriorityWanted([Method].[Value],[CFPPLinearPriority],
[CFPPPriority],[CFPP12Priority],[CloudMiniPriority],[CloudPriority],
[Conf_CFPPPriority],[Conf_CloudMiniPriority],[Conf_CloudPriority],
[Conf_HFRRPriority],[Conf_PourMiniPriority],[Conf_PourAutoPriority],
[DistPriority],[HFRRPriority],[PourMiniPriority],[PourAutoPriority],
[Pour_MANPriority],[RancimatPriority],[SFPPPriority]) AS Priority, 
[Sample SubmissionSingleBatch].[Instrument Specific], 
[Sample SubmissionSingleBatch].[Drop-Off date], 
[Sample SubmissionSingleBatch].Comments, 
[Sample SubmissionSingleBatch].[Samples in Class 1 FC?], 
[Sample SubmissionSingleBatch].[Flash Point of sample °C],
 GetReceivedWanted([Method].[Value],[CFPPLinearReceived],
[CFPPReceived],[CFPP12Received],[CloudMiniReceived],[CloudReceived],
[Conf_CFPPReceived],[Conf_CloudMiniReceived],[Conf_CloudReceived],
[Conf_HFRRReceived],[Conf_PourMiniReceived],[Conf_PourAutoReceived],
[DistReceived],[HFRRReceived],[PourMiniReceived],[PourAutoReceived],
[Pour_MANReceived],[RancimatReceived],[SFPPReceived]) AS Received, 
GetStartedWanted([Method].[Value],[CFPPLinearStarted],[CFPPStarted],
[CFPP12Started],[CloudMiniStarted],[CloudStarted],[Conf_CFPPStarted],
[Conf_CloudMiniStarted],[Conf_CloudStarted],[Conf_HFRRStarted],
[Conf_PourMiniStarted],[Conf_PourAutoStarted],[DistStarted],
[HFRRStarted],[PourMiniStarted],[PourAutoStarted],[Pour_MANStarted],
[RancimatStarted],[SFPPStarted]) AS Started,
 GetFinishedWanted([Method].[Value],[CFPPLinearFinished],
[CFPPFinished],[CFPP12Finished],[CloudMiniFinished],[CloudFinished],
[Conf_CFPPFinished],[Conf_CloudMiniFinished],[Conf_CloudFinished],
[Conf_HFRRFinished],[Conf_PourMiniFinished],[Conf_PourAutoFinished],
[DistFinished],[HFRRFinished],[PourMiniFinished],[PourAutoFinished],
[Pour_MANFinished],[RancimatFinished],[SFPPFinished]) AS Finished, 
[Sample SubmissionSingleBatch].[Retain?]
FROM 
[Sample SubmissionSingleBatch];

This second image is exactly what i want, the issue being this is a query in which I have a custom VBA function running (code provided below) and in this output the runs values cannot be edited. I would need them to be editable in the event an input error occurs, a run value needs changing etc.

I understand that I can just edit the data in the initial table, but I'm sure it's obvious that the second image is far more user friendly looking.

Is there a way to get what I need, or am I stretching Access beyond its capability/limits?

Function code:

Function GetRunsWanted(MethodValue As String, CFPPLinearRuns As Variant, 
     CFPPRuns As Variant, CFPP12Runs As Variant, CloudMiniRuns As Variant, 
     CloudRuns As Variant, Conf_CFPPRuns As Variant, 
     Conf_CloudMiniRuns As Variant, Conf_CloudRuns As Variant, 
     Conf_HFRRRuns As Variant, Conf_PourMiniRuns As Variant,
     Conf_PourAutoRuns As Variant, DistRuns As Variant, 
     HFRRRuns As Variant, PourMiniRuns As Variant, 
     PourAutoRuns As Variant, Pour_MANRuns As Variant, 
     RancimatRuns As Variant, SFPPRuns As Variant) As Variant
    Select Case MethodValue
        Case "CFPP LINEAR/EN16329"
            GetRunsWanted = CFPPLinearRuns
        Case "CFPP/LQP055"
            GetRunsWanted = CFPPRuns
        Case "CFPP12"
            GetRunsWanted = CFPP12Runs
        Case "CLOUD MINI/ASTM D7689"
            GetRunsWanted = CloudMiniRuns
        Case "CLOUD/ASTM D5771"
            GetRunsWanted = CloudRuns
        Case "Conf_CFPP/LQP055"
            GetRunsWanted = Conf_CFPPRuns
        Case "Conf_CLOUD MINI/ASTM D7689"
            GetRunsWanted = Conf_CloudMiniRuns
        Case "Conf_CLOUD/ASTM D5771"
            GetRunsWanted = Conf_CloudRuns
        Case "Conf_HFRR/ISO12156"
            GetRunsWanted = Conf_HFRRRuns
        Case "Conf_POUR MPP/ASTM D7346"
            GetRunsWanted = Conf_PourMiniRuns
        Case "Conf_POUR_AUTO/ASTM D5950"
            GetRunsWanted = Conf_PourAutoRuns
        Case "DIST_D86"
            GetRunsWanted = DistRuns
        Case "HFRR/ISO12156"
            GetRunsWanted = HFRRRuns
        Case "POUR MPP/ASTM D7346"
            GetRunsWanted = PourMiniRuns
        Case "POUR_AUTO/ASTM D5950"
            GetRunsWanted = PourAutoRuns
        Case "POUR_MAN/ASTM D97"
            GetRunsWanted = Pour_MANRuns
        Case "RANCIMAT/LQP092"
            GetRunsWanted = RancimatRuns
        Case "SFPP"
            GetRunsWanted = SFPPRuns
        Case Else
            GetRunsWanted = Null
    End Select
End Function

To normalize the data, you need 3 tables.

1. A main table, let's call it Test.
2. A Method look-up table listing all possible methods.
3. A Run table as a detail table to Test.

Table Test
----------
TestId (PK)  -- your AutoID, but with a better name
Lab Batch ID
IRIS Batch ID
Requester
etc. ...

Table Method
------------
MethodId (PK)
Name

Table Run
---------
TestId (PK, FK)
MethodId (PK, FK)
Runs

You will have these relations:

The Run table acts as a link table between Test and Method.

It is a good idea to choose "Cascade Delete Related Records" for the table relations between Test and Run, but not between Method and Run. This allows for automatic deletion of Runs when you delete Tests. But probably you don't want to delete runs, if you delete a method. Generally, it is rarely a good idea to cascade deletes between a lookup tables and normal tables. Lookup tables are tables that contain mainly constant data. They are a kind of dictionary.

The expected result can be queried like this:

SELECT T.*, R.Runs, M.Name
FROM 
  Test AS T
  LEFT JOIN (
    Run AS R
    LEFT JOIN Method AS M
      ON R.MethodId = M.MethodId
  ) ON T.TestId = R.TestId
ORDER BY T.TestId, M.Name;

I do not know where Lab Batch ID, IRIS Batch ID and Requester come from, but in a fully normalized DB, those would possibly require their own tables as well.