I have an Access database with many small tables. I would like to combine those tables into one large table. The tables all have different fields. I don't need to just view the data together with a query...I need to actually collect the data into one table.
Is there a way to combine Access tables into 1 and have it create the fields based on the original tables? For example, if the source field name doesn't exist in the destination table, it will create it. If the source field name already exists (there may be duplicates), it just appends to that field in the destination table.
For bonus points, would it be possible to insert a field that contains the name of the original table that the data came from?
See the attached picture for a very simple example.