Append table with another table and user input

Vbanoob98

Board Regular
Joined
Sep 13, 2019
Messages
128
Hello guys,

I have an issue which I assume is very simple yet I can't seem to figure it out. (Just started learning Access a few weeks ago)

I have a table called Table1 with the following columns:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID (autogenerated)[/TD]
[TD]SUB[/TD]
[TD]Month[/TD]
[TD]Total[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]GOP[/TD]
[TD]1901[/TD]
[TD]500[/TD]
[TD]Payment[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]GAP[/TD]
[TD]1901[/TD]
[TD]600[/TD]
[TD]Payment[/TD]
[/TR]
</tbody>[/TABLE]

And the second Table2 with the following columns:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]AutoNumber(Autogenerated)[/TD]
[TD]ID[/TD]
[TD]CustomerID[/TD]
[TD]Amount[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So what I want is to append the data from Table 1 to Table 2 but only specific columns :

ID---> ID
Total---> Amount

and the rest to be inputted by the user, so:

CustomerID---> Inputted by user
Name---> Inputted by user

The kick is that there will be many rows to be appended but the CustomerID and Name should be the same input for all rows.

So the end result would look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]AutoNumber(Autogenerated)[/TD]
[TD]ID[/TD]
[TD]CustomerID[/TD]
[TD]Amount[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]123456[/TD]
[TD]500[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]123456[/TD]
[TD]600[/TD]
[TD]John[/TD]
[/TR]
</tbody>[/TABLE]

And this process is to be repeated every month, so ideally there would be a way to only append for the selected month e.g. 1901. Or is there a way to append everything but ignore duplicates which would be easier maybe?

Thanks a lot!
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
The raw sql you need is this:

Code:
insert into Table2 (ID, CustomerID, Amount, [Name])
select 
	Table1.ID, 
	123456 as CustomerID, 
	Table1.Total as Amount, 
	'John' as Name
from Table1

The same code can be written for user input with parameters:
Code:
Parameters [Enter CustomerID] Long, [Enter Name] Text ( 255 );
insert into Table2 (ID, CustomerID, Amount, [Name])
select 
	Table1.ID, 
	[Enter CustomerID] as CustomerID, 
	Table1.Total as Amount, 
	[Enter Name] as [Name]
from Table1

It's probably horrible to let users run batch processes with inputs though - the eventuality of user error is unavoidable. At minimum you will probably need to use a form to guide the user during the process with textboxes to hold the input values.
 
Upvote 0
Thank you! I will test this tomorrow :)

Also how could I make it so that I can run the code based on a Month variable? Or do you have any pointer on where to look for the answer?
 
Last edited:
Upvote 0
When running the Sql object I get an error of "Couldn't not find file \\user\account\Data.mdb

Whats that about? :s
 
Upvote 0
to use a variable to filter results add a where clause:
Code:
Parameters [Enter CustomerID] Long, [Enter Name] Text ( 255 ), [Enter Month] Text ( 255 );
insert into Table2 (ID, CustomerID, Amount, [Name])
select 
	Table1.ID, 
	[Enter CustomerID] as CustomerID, 
	Table1.Total as Amount, 
	[Enter Name] as [Name]
from Table1
where Table1.[Month] = [Enter Month]

I don't know what you mean by a sql object. I assumed you are running the query as an Access query - that looks like you are using some other means to run queries.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top