spectrolab, you need to follow the footsteps of Julius Caesar. Divide and conquer. Working backwards, you can export a table OR A QUERY to a spreadsheet from the Analyze it with Excel icon that is on the toolbar. So the next trick is to get the query that contains what you want.
This next part is where life gets trickier. You were correct to build a many to one table with the parts you wanted per sample. But now you want to denormalize the child table - and you can't do that with SQL. At least, not very easily.
I sometimes give oddball advice because I was never inside the box in order to have to think outside of it. Why did you bother to use Access for this beast when it seems that (from your description of the goal) Excel would have been a better place to start? Don't get me wrong, Access is great at what it does, but your description cries out for a spreadsheet. Which Access ain't.
Unless you have some other specific things that you want done via Access, I would not bother using it here. A tabular display of sample vs. concentrations of the components is a hard thing to do with Access.
Now, I can go WAY outside the box here and tell you to denormalize the table in a very highly specific way - but depending on the number of components you will have in your samples, it wouldn't take long to break Access on its limits. Here is my thought, but I'm also going to preface the explanation by saying that it works best ONLY if the possible components that can be in the same are strictly limited and do not change over time.
If you had a table that was
Sample#,PK, integer (or whatever your format requires)
ConcFe, single, concentration of iron
ConcSi, single, concentration of silicon
ConcCaO, single, concentration of calcium oxide
...
then you can do this very easily.
Yes, I know that it appears to violate normalization. Except maybe it doesn't. If the list of possible components is relatively small (<20 possible compounds or elements) and relatively invariant,then these values are just sample attributes in a sample table. This isn't totally denormalized because the sample number is still rightfully the prime key and the values of the individual fields depend on that prime key. So there is an ARGUMENT that this isn't totally denormalized.
If you shoot for the wide-open, any-compound-is-possible analysis, then this doesn't work because the range and variety of compounds will quickly swamp the ability of Access to do what you want.
If you don't like VBA and if there is no overriding reason to use Access here, then this isn't an Access problem, it is an Excel problem based on what it would take to solve it the easiest way.
at the end.
