Append table with another table and user input

Vbanoob98

New Member
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:
ID (autogenerated)SUBMonthTotalComments
1GOP1901500Payment
2GAP1901600Payment

<tbody>
</tbody>

And the second Table2 with the following columns:

AutoNumber(Autogenerated)IDCustomerIDAmountName

<tbody>
</tbody>


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:

AutoNumber(Autogenerated)IDCustomerIDAmountName
11123456500John
11123456600John

<tbody>
</tbody>

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:

xenou

MrExcel MVP, Moderator
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.
 

Vbanoob98

New Member
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:

Vbanoob98

New Member
When running the Sql object I get an error of "Couldn't not find file \\user\account\Data.mdb

Whats that about? :s
 

xenou

MrExcel MVP, Moderator
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.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top