How Access update two table without field names?

tjb001

New member
Local time
Today, 07:08
Joined
Aug 30, 2015
Messages
9
I have two tableS: N1 and B1. I can update B1 within whole database of N1. The problem is N1's field6 as name as 2013 sales. This name will be change in future. How can I set equal to two tables' value without point to name, such as column6?
UPDATE N1 INNER JOIN B1 ON N1.ID=B1.ID SET B1.[2013 sale] = N1.[2013 sale]
Can I change "2013 sale " to field6 or column6?
Thank you
 
You don't, you properly structure your database instead. You shouldn't be storing relevant data in names, you store that data in fields.

If you had 100 sales people, you wouldn't have 100 fields to designate what person each sales amount was for (BobSales2013, LarrySales2013, DavidSales2013, etc.). Instead you would add a field for the sales person and store it there. Same principle with year.

This is wrong:

SalesTable
BobSales2013, LarrySales2013, DavidSales2013
15, 23, 99

This is right:

SalesTable
SalesPerson, SalesYear, SalesAmount
Bob, 2013, 15
Larry, 2013, 23
David, 2013, 99

You will never have to change field names again, you simply add new rows to your data. In 2014, you add records with 2014 in the SalesYear. If David leaves the company, you just don't put a record in for him in the future. If Sally becomes a salesperson in 2014, you simply add a record instead of adding a field for her.
 
OK, I already think this idea. But I think N1 only have 2013-2015 now. If N1 updates to 2016-2020, I have to add more field names to equal them, and keep the 2013-2015 because B1's format is same as old N1 with 2013-2015. If I do not keep old, I have to change the format with B1. That is a lot of work.
 
Another sign of a poor structure is action queries (UPDATE, APPEND, DELETE). Why do you need to move data between tables? Usually the same purpose can be achieved with SELECT queries.
 
Because N1 has more than 50000+ record, B1 is my watchlist that I want to update. I only need to know the B1 instead of whole database.
I am not sure how to use select queries to do this.
I import an excel file into access, so the format of N1 cannot be change, such as first row must be ID,...2013 sale, 2014 sale,etc.
 

Users who are viewing this thread

Back
Top Bottom