How Access update two table without field names?

[llgtjb001]

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, and this name will be change in future(2016). How can I set equal to two tables' value without point to name, such as column6? These two tables format are same, if one changes, I need to changed others. That's a lot of work.


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?
Is there some code to do this automatically?
Thank you

[ranman256]

Your table is designed wrong (for just that reason you gave)

Change the fields to:
[name], [year], [Amt]
bob, 2013, $2000
sam, 2015, $1000

so no matter what year,the date can be set. (you dont add columns, you add data)
if you DONT, do this, you are doomed to making work for yourself you dont have to.

[llgtjb001]

Your table is designed wrong (for just that reason you gave)

Change the fields to:
[name], [year], [Amt]
bob, 2013, $2000
sam, 2015, $1000

so no matter what year,the date can be set. (you dont add columns, you add data)
if you DONT, do this, you are doomed to making work for yourself you dont have to.

The problem is I import an excel file into the access, so I cannot change the format. If I change it, that's a lot of work and may lose data. N1 has more than 50k records, and B1 has only one thousand.

[ranman256]

you CAN change the format. The excel book is that way but you can transpose it into a table.

[June7]

I agree with ranman. Import the data to proper structure - your life will be simplified and sanity preserved. Otherwise, options:

1. manually change the query object design when needed

2. construct and run the UPDATE sql in VBA

Why do you need to copy data anyway?

[llgtjb001]

you CAN change the format. The excel book is that way but you can transpose it into a table.

i mean I can change the field name when I import it, but there are 20 total field names. It will waste a lot of time.
Or maybe write some codes?

[llgtjb001]

I agree with ranman. Import the data to proper structure - your life will be simplified and sanity preserved. Otherwise, options:

1. manually change the query object design when needed

2. construct and run the UPDATE sql in VBA

Why do you need to copy data anyway?

If I append the file to my proper structure table, it does not work.
I need to update B1 from whole database N1.
How transfer sql code to vb code?

[June7]

Have a form with text or combo box where you input/select the year. Then VBA like:

CurrentDb.Execute "UPDATE N1 INNER JOIN B1 ON N1.ID=B1.ID SET B1.[" & Me.tbxYear & " sale] = N1.[" & Me.tbxYear & " sale]"

This assumes the new field in destination table already exists. If you need to programmatically add new field, that is another issue.

[llgtjb001]

Have a form with text or combo box where you input/select the year. Then VBA like:

CurrentDb.Execute "UPDATE N1 INNER JOIN B1 ON N1.ID=B1.ID SET B1.[" & Me.tbxYear & " sale] = N1.[" & Me.tbxYear & " sale]"

This assumes the new field in destination table already exists. If you need to programmatically add new field, that is another issue.

I already created a combo box that put my watchlist products, such as A1-1,A1-2. When I select the A1-1 and click report, it will automatically show me on 2013-2015. As I said, if I put 2016-2019, the field name changed, it will not work. Is it possible to use range to update it, such as range(column6) in vb code?column6 named as 2013 sale. Thx.

[June7]

There is no such thing as Range in Access.

Are your combobox list items like:

2013-2015
2014-2016
2015-2017
2016-2018

You want to update 3 fields? The VBA code could be like:

Dim strStart As String
strStart = Val(Me.combobox)
CurrentDb.Execute "UPDATE N1 INNER JOIN B1 ON N1.ID=B1.ID SET B1.[" & strStart & " sale] = N1.[" & strStart & " sale], B1.[" & strStart + 1 & " sale] = N1.[" & strStart + 1 & " sale], B1.[" & strStart + 2 & " sale] = N1.[" & strStart + 2 & " sale]"

[llgtjb001]

There is no such thing as Range in Access.

Are your combobox list items like:

2013-2015
2014-2016
2015-2017
2016-2018

You want to update 3 fields? The VBA code could be like:

Dim strStart As String
strStart = Val(Me.combobox)
CurrentDb.Execute "UPDATE N1 INNER JOIN B1 ON N1.ID=B1.ID SET B1.[" & strStart & " sale] = N1.[" & strStart & " sale], B1.[" & strStart + 1 & " sale] = N1.[" & strStart + 1 & " sale], B1.[" & strStart + 2 & " sale] = N1.[" & strStart + 2 & " sale]"

UPDATE N1 INNER JOIN B1 ON N1.NDC=B1.NDC SET B1.[MAT Jan 2013_Sales $] = N1.[MAT Jan 2013_Sales $], B1.[MAT Jan 2013_Sales Market Share] = N1.[MAT Jan 2013_Sales Market Share], B1.[MAT Jan 2013_NSP Ext Units] = N1.[MAT Jan 2013_NSP Ext Units], B1.[MAT Jan 2013_NSP Ext Units Market Share] = N1.[MAT Jan 2013_NSP Ext Units Market Share], B1.[MAT Jan 2014_Sales $] = N1.[MAT Jan 2014_Sales $], B1.[MAT Jan 2014_Sales Market Share] = N1.[MAT Jan 2014_Sales Market Share], B1.[MAT Jan 2014_Sales Growth] = N1.[MAT Jan 2014_Sales Growth], B1.[MAT Jan 2014_NSP Ext Units] = N1.[MAT Jan 2014_NSP Ext Units], B1.[MAT Jan 2014_NSP Ext Units Market Share] = N1.[MAT Jan 2014_NSP Ext Units Market Share], B1.[MAT Jan 2014_NSP Ext Units Growth] = N1.[MAT Jan 2014_NSP Ext Units Growth], B1.[MAT Jan 2015_Sales $] = N1.[MAT Jan 2015_Sales $], B1.[MAT Jan 2015_Sales Market Share] = N1.[MAT Jan 2015_Sales Market Share], B1.[MAT Jan 2015_Sales Growth] = N1.[MAT Jan 2015_Sales Growth], B1.[MAT Jan 2015_NSP Ext Units] = N1.[MAT Jan 2015_NSP Ext Units], B1.[MAT Jan 2015_NSP Ext Units Market Share] = N1.[MAT Jan 2015_NSP Ext Units Market Share], B1.[MAT Jan 2015_NSP Ext Units Growth] = N1.[MAT Jan 2015_NSP Ext Units Growth];

This is my sql code. It works fine. But I just want to know N1.[MAT Jan 2013_Sales $] as column6, and it may changed in future with 2016. If it changes, this sql will not work. So how can I figure it out? Also, please help me to transfer it to vb code. Thx.

[llgtjb001]

There is no such thing as Range in Access.

Are your combobox list items like:

2013-2015
2014-2016
2015-2017
2016-2018

You want to update 3 fields? The VBA code could be like:

Dim strStart As String
strStart = Val(Me.combobox)
CurrentDb.Execute "UPDATE N1 INNER JOIN B1 ON N1.ID=B1.ID SET B1.[" & strStart & " sale] = N1.[" & strStart & " sale], B1.[" & strStart + 1 & " sale] = N1.[" & strStart + 1 & " sale], B1.[" & strStart + 2 & " sale] = N1.[" & strStart + 2 & " sale]"

my combo box only list the product name because I only want to see the product information with 2013-2015.

[June7]

You need some control to input the year parameter. Then reference that control in the VBA to construct appropriate SQL action statement. I have provided sample code. Now adapt it to your db.

Advise no spaces or special characters/punctuation (underscore is exception) in names.

[llgtjb001]

You need some control to input the year parameter. Then reference that control in the VBA to construct appropriate SQL action statement. I have provided sample code. Now adapt it to your db.

Advise no spaces or special characters/punctuation (underscore is exception) in names.

First, thanks a lot. BTW, I use the listbox instead of the combox. Is it matter?

 Dim strStart As String    strStart = Val(Me.List4)
    CurrentDb.Execute "UPDATE N1 INNER JOIN B1 ON N1.NDC=B1.NCD SET B1.[" & strStart & " sale] = N1.[" & strStart & " sale], B1.[" & strStart + 1 & " sale] = N1.[" & strStart + 1 & " sale], B1.[" & strStart + 2 & " sale] = N1.[" & strStart + 2 & " sale]"

when I run it, why it said "Me" is invalid? I am sorry because I just start to learn the access and vb code. What stand for "&strStart&" sale?

[June7]

The & is concatenation operator. strStart is a variable. This is how strings are built using literal text and variables.

Exactly where did you put the code - is it behind a form or in a general module? Should be behind form that has the controls. Listbox or combobox or textbox - doesn't matter.

You need an understanding of basic programming concepts if you are going to pursue db customization with VBA.

http://holowczak.com/programming-con...l-programmers/

Here is a VBA tutorial. http://www.access-vba.net/introducti...ccess-vba.html