Update Tables/Queries/Reports with year change

[Redchrome]

I have a database for uniform sizing entry. The employer buys the uniforms, per contract annually, but needs to ensure correct sizing. The database for the first year has 2 tables, a long list of queries, and a handful or reports. I'd like to do a few things automatically (or on admin demand) every year. I am not sure if it is even possible, as I am self-taught in Access, and using these forums whenever I hit a wall. Any info in the right direction would help. Thanks in advance!



As an example below this would be what I want at the end of this year.

1) Copy the structure of "2022UniformOrderTable" and add "2023UniformOrderTable"

2) Automatically run the "EmployeeAppendQuery" to add names and employee ID fields to the new "2023UniformOrderTable"

3) Change the record source in ~20 Queries from the "2022UniformOrderTable" to "2023UniformOrderTable" (These queries pull a count of each size/color combination for each item from the UniformOrderTable)

4) Change the record source in the "UniformOrderForm" and UniformOrderSubform" to "2023UniformOrderTable"


EDIT: If this Sub-forum is the wrong one, let me know and I can repost.

[CJ_London]

you don't have separate tables for separate years. You have one table and include a 'year' field to identify the year - note that 'year' is a reserved word and should not be used for a field name

then you don't need to make any changes at all once you have modified your queries to filter on the year field.

I can't see a situation where your main form and subform would have the same recordsource - except perhaps for a split form.

[Redchrome]

Sorry misspoke on the form. Form points to the EmployeeTable Subform Points to UniformTable.

Filtering is fine for me, but I am trying to futureproof this for people not generally computer literate, as I am 47 weeks from retirement. So just changing the source to only pull current stuff makes it easy.

But thanks for the suggestion, I will try them in the sandbox and see if its feasable.