Query for Multi year Tables

[Danelloc]

I have a database training yearly training. The excel sheets are divided by the year. I want to add them to access so I can do queries over multiple years. Rather than place the data in one big table, I have created tables for each year. This way, I can update individual tables when I receive data about a specific year; rather than having to update the entire table of all the years each time.

I am trying to build a query that then pulls the data from all the tables into one source. I tried to create a Union query, but was unsuccessful.



Would an append query work?

[Welshgasman]

Wrong method already.
You are thinking with an Excel mindset. You need to forget that completely and start afresh.
You would have a Year table with a field for which year, possible month, so a date field more than likely.

Then it is very simple.

[Micron]

Splitting what should be in one field of one table into multiple tables is always a bad idea in spite of whatever reason you use to justify it. I suspect you won't be swayed, so one thing you can do is link the spreadsheets in Access as if they were tables. A union query would be the way to get like data from 2 or more tables.

[xps35]

Having a table for each year is bad design. The arguments you put forward are invalid.

If you still want to stick to this, a union query is the solution to present the tables as one table. The fact that you didn't succeed doesn't change that. So explain why it was not successful and we might be able to help.

[ArviLaanemets]

With this design, every time you have to add data for new year, you have to redesign your whole database (forms, queries, reports). Or you have to create in advance empty tables for all possible future years.

And your UNION query will be a huge one, and probably a slow one too.

Consider instead a single table. Entering new data or editing existing data must be done in forms, not in tables. Forms have a lot of features, which allow to control the data entry - e.g. in case you want to have to enter data for certain year, yo can set the filter for form to only data from this year to be accessible. And it is even possible to design it in a way, that any new data gets automatically assigned to this year (your data entry form is a subform of unbound form with unbound text box or combo box, to select the filtering year. Or in case you have dates in your table, then to select the filtering date - especially useful when there is a lot of entries with same date - and all new records you enter are assigned to selected date).

And in case when you get new data as table in format Access can read in or link to, then you can run a query to insert new data into Access table at one go. Much more difficult to do the same with multiple tables.

[Danelloc]

First off, thank you for providing a constructive, non hostel response as opposed to the other posters here.

On a previous version, I do have it all on one table and was thinking it would be easier to update if they were separate tables.

After reading your response, I agree that a single table and a single append query is the way to go. Thank you for helping me think it through.

[Danelloc]

With this design, every time you have to add data for new year, you have to redesign your whole database (forms, queries, reports). Or you have to create in advance empty tables for all possible future years.

And your UNION query will be a huge one, and probably a slow one too.

Consider instead a single table. Entering new data or editing existing data must be done in forms, not in tables. Forms have a lot of features, which allow to control the data entry - e.g. in case you want to have to enter data for certain year, yo can set the filter for form to only data from this year to be accessible. And it is even possible to design it in a way, that any new data gets automatically assigned to this year (your data entry form is a subform of unbound form with unbound text box or combo box, to select the filtering year. Or in case you have dates in your table, then to select the filtering date - especially useful when there is a lot of entries with same date - and all new records you enter are assigned to selected date).

And in case when you get new data as table in format Access can read in or link to, then you can run a query to insert new data into Access table at one go. Much more difficult to do the same with multiple tables.

First off, thank you for providing a constructive, non hostel response as opposed to the other posters here.

On a previous version, I do have it all on one table and was thinking it would be easier to update if they were separate tables.

After reading your response, I agree that a single table and a single append query is the way to go. Thank you for helping me think it through.