Query from multiple tables (1 Viewer)

[x18access]

Hi,

i have an access database to monitor investments.
What i am currently trying to achieve is to generate a graph which would show a stocks changing price over time.
Currently, i have made a button that generates a new table each day named 'FTSE100-DATE' Where DATE is the date of the previous day. The current stock prices then become the prices in the main FTSE100 table.

Therefore, when i run the query, it needs to collect the price information over more than one table, as well as with a new table each day.
How would i do this?
I think part of it is using a union statement in SQL, but any and all advice welcome XD.
Regards
I would prefer to code this in VBA if its possible. Just let me know

 

[Uncle Gizmo]

>>>button that generates a new table each day<<<

Why a new table? Why not append to an existing table?

 

[x18access]

because it allows me to easier ( i feel) look at information from THAT day specifically. Also, this is how my computing teacher- who i no longer have access too recommended doing it.

 

[Uncle Gizmo]

>>>the query, it needs to collect the price information over more than one table, as well as with a new table each day<<<

Do all of these tables have the same structure?

 

[x18access]

that is right!
Yes they do all have the same structure exactly the same

 

[Uncle Gizmo]

>>>Therefore, when i run the query, it needs to collect the price information over more than one table<<<

The solution is to append into one table, then you query the single table.