0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andy Andromeda
  • 55
  • 6
  • https://stackoverflow.com/questions/201282/how-can-i-get-table-names-from-an-ms-access-database then loop to splice together THE table names into the joins part of a SELECT * sql statement. which joins depends on how you choose to handle missing data. For example: SELECT * FROM 4/01/2021 INNER JOIN 5/01/2021 ON ... – mazoula Jan 11 '21 at 06:31
  • 2
    Really should be 1 table with another field for identifying each set of records, which can be the date created. That would be a normalized structure. Then a CROSSTAB query is one way to pivot data. Otherwise, joining tables requires a master dataset with all possible values of whatever is the common identifier need to link with. – June7 Jan 11 '21 at 07:34
  • Unfortunately, saved query objects cannot be dynamic for inclusion of tables/fields. In VBA, could use QueryDefs to modify a saved query or build an SQL statement to set RecordSource of report. No idea how would be done in VB.net, C# or whatever your frontend is coded with. – June7 Jan 11 '21 at 08:08

1 Answers1

0

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

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • 1
    cheers, i have just set out with what im trying to make. so im not too far into it. so i could change the structure of the tables and database. thank you for your ideas. – Andy Andromeda Jan 11 '21 at 09:56
  • i seem to have run into a new problem. i cant get a SQL query to work that includes a date. my date is in dd/mm/yyyy format. im trying to do `select * from Returnstoaction where purchase_date = ("10/01/2021")` – Andy Andromeda Jan 11 '21 at 11:27
  • Date expressions use _octothorpes_ and use the ISO sequenc for year-month-day: `select * from Returnstoaction where purchase_date = #2021/01/10#`. – Gustav Jan 11 '21 at 11:30
  • thanks, this helped me i finally worked got it to work the correct way. – Andy Andromeda Jan 11 '21 at 12:43