How can I merge the columns from two tables into a new table. Also would like this automated.

[David_]

I have a request that I posted earlier today in Stack Overflow, and it is still plaguing me. I hope someone here can assist. Cheers in advance!

I receive assay values from multiple laboratories.
I import them all into a mass Access table called "sample". within this "sample" are dozens of headers that allow for the collation of information that is newly imported, and also significant legacy values. For example:

Si_%	Si_pct	Si_ppm	Si_ppb	Au_%	Au_pct	Au_ppm	Au_ppb
7	63	0.063	0.5	500	0.5

(with values beneath the headers. Some are null spaces due to different requirements for previous or new laboratory assays)



I wish to clean up the data with a new table that automatically updates imported values from the bulk "sample" table, into separate tables that streamline the headers.

I want the new tables to be called "samples_%", "samples_ppm", samples_ppb" etc. With the index ID also coming across.

I then wish to automate this process in a macro after each import from the laboratory .csv / .xlsx such that when the "sample" has been updated, running the macro will automatically update these new tables also. Can anyone help?

I have tried to generate queries that would assist but I fee I am heading down the wrong track (see below fail).






EDIT - I have been informed within the aforementioned stack-overflow exchange that the use of symbols can cause me some grief. As such I am happy to have the new tables titles "samples_pct", "Samples_ppm", etc

[June7]

Are both of those ID fields autonumber type? Makes no sense to link 2 autonumber fields.

Your data structure is more like a spreadsheet (short and wide) instead of a relational database (long and narrow). If you maintain the tests as sets of fields, really no advantage to splitting into separate tables, unless you are hitting 255 fields.

Normalized structure would be more like:

SampleID	SampleDate	ProjectID_FK
1	1/1/2022	4
2	2/1/2022	4

SampleID_FK	Test	Pt	Pct	Ppm	Ppb
1	Si	7	63	0.063	0.5
1	Au	500	0.5
2	Si	5
2	Au	400

[David_]

Are both of those ID fields autonumber type? Makes no sense to link 2 autonumber fields.

Yes, the ID's are generated automatically upon importing the new laboratory results.
Really? I was under the impression (when I was working on that query) that I needed to indicate an index for both tables and to specify the values that matched... in retrospect what you say does make sense.