ms access - SQL Select statement from mutiple tables to fill datagridview

Question

I have multiple tables, the tables themselves are named after the date they were created on; so for example 4/01/2021, 5/01/2021.. etc

The tables contain all the same columns.

But I'd like to create a SQL statement that allows me to return all the tables that were created between two dates and fill a Datagridview with all the records in those tables.

Ideally I want a "Created Last Week", "Created This week", "Created This Month" options. I can work out th syntax for the start and end dates. But I'm not sure what the correct way is to return the tables that fall between the dates.

I have looked at a few examples but none seem to work for me or be exactly what I'm after. Not sure if I can use sys.tables or if I need to use inner joins/left join etc to get this to work.

My tables are in a Acccess.MDB file.

Answer 1

You will need a union query:

Use a union query to combine multiple queries into a single result

However, as the tables included will vary, you must create the SQL of the query dynamically and then call the query to fill your datagridview.

Note: This is a terrible setup. You should, at the soonest and as suggested by @June7, change your schema to have one table only with a field holding your dates (your current table names).