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.
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).