Dynamic Table

[DiegoAdrianX]

I work as an assayer at a small laboratory. We analyze different elements in mineral samples. For example, on Sample 1 we will determine lead and zinc concentrations, while on Sample 2 we will determine lead and iron concentrations.

On Access, I have a table that has a field for the sample ID (short text), and a field for de n number of assays on that sample (multiple combo box).

I was wondering if there is a way to create a table/report from this first table (mentioned above), where the rows contain the sample information and the number of columns will change and depend of the different assays on those samples. Continuing with the example, the table would have two rows, as for Sample 1 and Sample 2, and three columns, for the elements of lead, zinc and iron. If this number of elements would ever change, so would the number of columns.



I been struggling with this for quite a while now, if someone could help me, I will much appreciate it.

[June7]

No, if I understand what you describe, that is not a viable approach. Fields are in table or they are not. If field is removed, data is removed.

Normalizing would mean a related dependent table where each element test result is a record.

Exactly what is "field for de n number of assays" and why is it a multi-value?

[ranman256]

The number of columns should not change.
you may need a sub table where the Topics and values change. Then you have infinite entries. (Columns)
like:

Key topic, value
22,length, 2.4
22,Width, 3.4
22,width, 1.1

[ArviLaanemets]

You need something like:
tblSamples: SampleID, SampleDate, SampleMineral, SampleOrign, SampleComment, ...
tblAnalyzeResults: AnalyzeResultID, SampleID, AnalyzeDate, ChemistID, AnalyzedComponent, Concentration, ...

You'll have a single main form where you register/select a sample, with a contionuous subform where all analyze results for active sample from main form are entered/displayed. Both forms are linked through SampleID. And you can get a report of any design you want, using a query based on those tables.

[orange]

Here's a link to Entity Attribute Value that may offer some insight. You can determine if it applies.