If anyone could help me I would greatly appreciate it. I am trying to update numerous tables for a more effective search. My company has file numbers list as such 001 -1000. The issue I am having is that a new table was started each year (so I have 18 years of 001 etc..) and I want to simply add the year before each number to make searching easier so the new file number would look like 2018-001. For the life of my I cannot figure this out. Sorry I am fairly new to access as well.
You need to combine your tables into a single working table. Here's one approach.
Let's arbitrarily call the new table 'tblTarget' and the field to be updated 'TargetID' for this discussion. Since the field in question has a dash in the middle, it MUST be a text field. You also say you have a new table each year, which means names like 'tblSource2017' and 'tblSource2016' (or however you differentiated them).
You can join these tables together into a single searchable table with one query per source table that would be easily editable to pick up one table at a time.
Define the fields of tblTarget to be consistent with the fields of tblSourcennnn. Just pick one as the starting point. Do this step FIRST.
Now consider this SQL action query:
Code:
INSERT INTO tblTarget (TargetID, other fields)
SELECT "[COLOR="Red"]nnnn[/COLOR]-" & TargetID, other fields FROM tblSource[COLOR="red"]nnnn[/COLOR] ;
This would combine your tables one at a time into a master table. Once you were convinced that you got everything, you would then delete the source tables.
You would of course have to find any forms or queries that used the individual source tables and redirect their attention to tblTarget. This will not be an instantaneous fix but considering that you would have needed one set of forms & queries & reports for each of the yearly source tables, this would tremendously reduce the overhead structure required to support your operations.
The tedious part of this is the manual update required - but it is a one-off project. Do it once and start using the new structures. You'll never need to do it again. The queries are a simple edit. The parts in red in that sample query just need updating once per source table and then you are done with it.
I should also point out that technically, having the multiple tables is an insidious form of data denormalization that would require a lot of overhead for each year that you had to create a new table and a new set of supporting infrastructure. This "extra infrastructure" requirement is a tell-tale sign that your design has a flaw.
Using the single table such as I suggested obviously only requires you to have ONE set of infrastructure queries and such no matter how many years you have stored in the one table underneath everything. And you only have to update that prefix once per year. Better still, if it really is just the current year, look up the DatePart function as a way to get the current year using the Windows internal date. DatePart("yyyy", Now() ) is one of many ways to get that date, you wouldn't even need to edit the constant for the year because Access can find it for you.
Thank you so much for the reply,
I am having trouble with the SQL statement taking all the parameters I only get 2014- in the first field. Here is my SQL
Do NOT make year a prefix. Make it a separate field. Mushing data violates first normal form and is never a good idea. You are forcing yourself into using the LIKE operator which usually cannot utilize indexes and so forces a full table scan. The larger your row count, the slower a full table scan will be.
Doc was being helpful to get you to the solution you asked for but I'm sure he would agree that a separate column rather than a prefix is the best option.
