Insert Records From One Table To Another
Apr 10, 2007
hi, i want to convert some below mentioned format into msaccess tables.
the exact scenario is that i have some text files from old applicaiton (pascal based) which is no longer working due to hardware compatibility problem...
which has generated some text files under below mentioned format...
[CLIENT]
L1_CUSTOMER_REFERENCE=AbreyCatherine
L1_TITLE=Ms
L1_SURNAME=Abrey
L1_FORENAME=Catherine
L1_DOB=1 Jul 1957
L2_DOB=9 Jul 1954
L1_ADD1=165 Downhills Way
L1_ADD2=Tottenham
L1_ADD3=London
L1_PCODE=N17 6AH
Q_L1_SEX=F
L1_CLCODE=CA1
L1_MSTATUS=M
L1_RELIGION=Christ.
L1_HASPARTNER=Y
L1_PARTNERNAME=Gian Paolo Caddeo
L1_PHONEH=0181 889 3870
L1_SALUTATION=Catherine
L1_OCC=Management Consultant
L1_EMP_STAT=S
L1_HEALTH=G
L1_SMOKE=Y
L1_NATIONALITY=British
L1_NRA=60
L2_OCC=Heating Engineer
L2_EMP_STAT=S
L2_HEALTH=G
L2_SMOKE=N
L2_NATIONALITY=Italian
L2_NRA=65
FactFindDate=30 Oct 98
TermsOfBusDate=30 Oct 98
LastVisitDate=18 Oct 95
LastLetterDate=14 Dec 99
Consultant=KP
AdminRef=AT
BestCallTime=Evenings
ClientType=UKCL
L1_WillDate=1993
L2_WillDate=1993
MailAllowed=-1
LastMailDone=0
L1_NINumber=YZ070258B
SeparateTax=
ContactFrequency=26
ManualSelect=
ClientLastUpdated=29 Jun100
[CLIENT]
each and every record starts from [CLIENT] and First part is the field and the part " = " is the value for the respective field.
now, i need help in converting that information into msaccess tables and its respective values...
like ...1) first part will become the field of table and
2) 2nd part after " = " will be the value for the respective field of the respective table.
i hope that this has given clear picture. where i want help from an expert...so, kindly help me. That will be great indeed to me.. thanking yours...om
View Replies
ADVERTISEMENT
Sep 8, 2014
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
View 14 Replies
View Related
Mar 22, 2005
Hi
I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”
View 4 Replies
View Related
Dec 9, 2014
I am reading through a table looking for duplicate values in the FullName text field. I want to store in a new table the duplicate records I find, storing just the MemberNumber and the FullName. When the VBA code runs and finds duplicates, the SQL statement to insert a new record into the Duplicates table asks for the value of LastMemberNumber and LastFullName when it already has the values and has displayed them in the message boxes! What am I doing wrong?
The code is:
Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim dbs As Database
Dim LastMemberNumber As Integer
Dim LastFullName As String
[code]....
View 7 Replies
View Related
Jan 9, 2007
Newbie question: I have a database where we track dispatched with two tables, one called Slots, the other called Dispatches.
In the slots table there are 2 fields, one called Week (which hold the Monday date for each week in the year), the other called Available (in this field we want to manually enter a number that will tell the system how many dispatches we can do that week). We want to automatically take the number from the Slots.Available and enter X number of blank records in the Dispatch table with the monday date.
How can this be done?
Then we will create a form that can be filled in with the balance of the information.
Thanks
View 3 Replies
View Related
Aug 1, 2005
Is anybody can help me to solve problem with inserting empty record between two records in unordered (non-indexed) table - like in Excel work sheet.
Regards,
Gennady
View 4 Replies
View Related
Aug 11, 2013
I have two tables, one is GENETIC and the other one is BoneSampleDNAprofile. Both tables have the field CY-Code. I want to create small form with 2 text box fields and one command button on the form to be able to insert CY-Code from the table GENETIC to designated DNA_LAB_Code in the table BoneSampleDNAprofile.
In that small form I would type DNA_LAB_Code and CY-Code where after pressing command button will insert CY-Code for the designated DNA_LAB_Code. This is continuous process after our Laboratory receive results from DNA laboratory. I have attached two .png files which are example of two tables.
View 2 Replies
View Related
Apr 15, 2014
I'm trying to copy records from another base into existing table in current base by:
Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")
But it doesn't run. Says insert into syntax error.
View 6 Replies
View Related
Mar 5, 2008
I have this query below:
INSERT INTO TEST_DOC
SELECT *
FROM MPI_ADDSS_IFF;
I currently have 10 tables in my database (All with the same colunm names and formats) however i want to click a button that will put all the tables into TEST_DOC, instead of doing it one by one. To do this this i would like some code either in SQL or VBA that will do this.
Any ideas or help?
Kind Regards
Hewstone999
View 1 Replies
View Related
Aug 28, 2014
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
View 1 Replies
View Related
Jul 20, 2005
Hello
I have created a table of Some machines specifications.
Item Name Type location
1. Sieve156 Sieve 3rd floor
2. Rollermill1 Mill 1st floor
....
...
...
When inserting for example a new sieve machine I want database
add automatically this machine between items 1. and 2. and assign the item
number 3 to rollermill1. but this new record is added to the last row and its
item number will be 3 but it should be 2.
What shall I do for this problem? (in excel it is easily possible to add a row
between two rows.. is it also possible in access?)
thank you
View 6 Replies
View Related
Sep 6, 2004
hello,
I am trying to get a form to insert info into a access database. I am using dreamweaver 2004, i created the form with 4 text fields and a submit button. Ive added a ODBC connection and dreamweaver can qurery my table.
I added an insert record server behaviour, and i figured dreamweaver does all the work for you but when i saved and uploaded the page to my test server it would not submit and redirect to my chosen page, it only refresh the form.
Has anyone out there done this before and can notice a step that im missing.
Do i need to setup a recordset? because ive tried that as well and no success
help is much appreciated.
View 2 Replies
View Related
Oct 11, 2005
I am working on an electronic catalog for my company. I am populating the database and I'm trying to speed the process. This is what I want to do. This database deals with cars and trucks, I would like to design a form where I can enter:
1989-1995 Chevy Malibu, etc.
Once the form is full and I save the records it will create 7 individual records, one for each year.
1989 Chevy Malibu
1990 Chevy Malibu and so on.
Any ideas?
View 4 Replies
View Related
Feb 8, 2008
Hi everybody....
I have a vba question....
I have a form that states how many entries a certain table can have related to that form.
For example:
Form1 - based directly on the table for diagnostic purposes
Cust ID - text - primary key
NumofEntries - number - integer
I want it to create X amount of records in form2 based upon the NumofEntries from form 1 based on table1
For example, I run form 1, and it asks for the custnum, I enter it, and then it asks for the number of entries allowed for the customer, and then I enter it. This number can be different each time. I want the 2nd form just to show that many fields for entry either by a running total (subtraction) in form view or by a for next loop to create X amount of records in the table.
Form1:
CustNum = 1011
NumofEntries = 3
CustNum = 2022
NumofEntries = 4
CustNum = 3033
NumofEntries = 2
Table2
Custnum = 1011, entrynum =1, entry data
Custnum = 1011, entrynum =2, entry data
Custnum = 1011, entrynum =3, entry data
Custnum = 2022, entrynum =1, entry data
Custnum = 2022, entrynum =2, entry data
Custnum = 2022, entrynum =3, entry data
Custnum = 2022, entrynum =4, entry data
Custnum = 3033, entrynum =1, entry data
Custnum = 3033, entrynum =2, entry data
the key is to have the entrynum to start at 1 each time, the rest I can handle.... I am at a loss right now, as I am down to one brain cell, and it's misfiring.....
Granted, tomorrow, when I wake up, I will prolly have a solution, but as always, I value all of your input and design suggestions. Yes, I know already that it's a one to many relationship from table 1 to table 2, I just want some alternate ways to do this. Thanks in advance for any insight you wish to give.
View 3 Replies
View Related
Sep 6, 2006
Hi All,
I hope somebody can help me on this.
I still use Access 97.
I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.
I want to add a new registration to all 4 tables from one query. How?
I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.
Help! If what I want is not directly possible, then any work-round would be appreciated.
Thanks in anticipation
Richard
View 2 Replies
View Related
Apr 26, 2006
Hi,
I'm new to Access and I've run into a little problem. I've created a table with a primary key (autonumber) which will contain 1520 records. At present I only have data for the last 8 records. Ideally I would like to create 1512 blank records, with only the autonumber entered e.g. 0001, 0002, 0003 etc.
Unfortunately I cant add the data that I have got to 0001 - 0008 as it relates specifically to the primary ID.
Thanks.
View 10 Replies
View Related
Feb 24, 2008
Hi, i quite new to programming.
I want to insert some values into the database.
This is my sql statement written in the asp page
Code:SQL= "INSERT INTO Login ([User], Pass,Info,GroupID,UserRootFolder,Email,IsDisabled, IsGroupAdmin,LanguageFile,AccountExpires) VALUES ('"& str_name & "','" & str_password & "','" & str_aboutUser & "'," 5 , &"''"&", '"& str_email & "', " 0 , 0 , english.dat, NULL)
The values 5, 0 , 0 and english.dat are the values i want to imput into the database. They are not variables. The rest will be variables.
This is the error i get
Code:Error Type:Microsoft VBScript compilation (0x800A0401)Expected end of statement/temasek/register_action.asp, line 12, column 200SQL= "INSERT INTO Login ([User], Pass,Info,GroupID,UserRootFolder,Email,IsDisabled, IsGroupAdmin,LanguageFile,AccountExpires) VALUES ('"& str_name & "','" & str_password & "','" & str_aboutUser & "'," 5 , &"''"&", '"& str_email & "', " 0 , 0 , english.dat, NULL)-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^
How do i write my sql statement
Thanks
View 3 Replies
View Related
Jul 4, 2005
Hi all,
I have a table which contains business details (name, address etc) and also a field for clients. I then have a table which contains client details. Is there anyway I can click a button on a form containing business details which brings up a form containing the clients which would allow me to click the clients I want to be inserted onto the clients field on the business details table?
Does anybody have an example of this.
Thanks for your help!
View 1 Replies
View Related
May 25, 2007
Hi,
I have a lot data to append to ODBC linked table in MS Access. I want to know that which way is faster to append the records.
if I append the data into ODBC linked table,
1) create the one query (append) to insert the records into ODBC linked table
2) use the VBA code (DAO/ADO) to insert the records into ODBC linked table
which way is rather faster?
View 1 Replies
View Related
Feb 3, 2015
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
View 5 Replies
View Related
Sep 3, 2005
I have two tables tbl1 and tbl2
tbl1 has 10 fields named tbl1.id tbl1.field2 tbl1.field3 tbl1.field4
tbl2 has only three fields tbl2.field1 tbl2.field2 tbl2.field3
Now i need to insert values into tbl1::
tbl1.field1 tbl1.field2 tbl1.field3
from
tbl2.field1 tbl2.field2 tbl2.field3
respectively, but i need to make sure if tbl2.field3 value is already there in tbl1.field3 then we don't need import those records. so we only need records if value of tbl2.field3 is not already there in tbl1.field3.
Please let me know What statement do I need to write so i can import all data from tbl2 into tbl1 by comparing as above.
View 2 Replies
View Related
Mar 27, 2013
I have a form with a subform. I want to use the main form to insert new data and the subform to show all records that are there. One could say that the after inserting a new record with the fields in the form and save it, it should appear in the subform datasheet view.
Please see attached the sample database..
View 3 Replies
View Related
Jul 11, 2013
I have a command button on a form which loops through pages on a tab control and constructs an SQL string to insert a new record into a corresponding table (each tab has it's own separate table)
The tables are linked via a common account ID (an autonumber on a separate 'Core' table, the latest record for which is generated earlier in the code; this section creates a linked record in each of the other tables)
I've used a naming convention in each page such that each control name and it's corresponding field name in the destination table are the same (apart from the first 3 characters which I use to identify the control type, i.e. "chk", "txt" etc.)
For one of my pages, the insertion of the new record keeps failing. I've gotten it to work once or twice but only by randomly changing some of the values on the form (checking / unchecking boxes or keying data into random textboxes etc.) But I can't identify why it works sometimes and not others?
The other 3 tabs/pages insert records with no issue, every time.
I've tried debugging but I don't get any error message when I execute the constructed SQL; there are no apparent control violations and all of the relevant fields are correctly named & referenced. The only 'required' field in each table is the common Account ID, which is present, correct & not duplicated, so omitting null / empty fields or passing empty strings / values for the remaining fields shouldn't be an issue (?)
I can only assume it's a problem with a value being passed somewhere but what's stumping me is that on the other pages, it inserts the records exactly as expected (whether data has been provided or not)
Stepping through the VBA, it looks like a record should be inserted - but when I check the table, there's nothing there?
Code:
Dim pge As Page
Dim ctl As Control
Dim strSQL As String
Dim strSQLFields As String
Dim strSQLValues As String
[Code] .....
View 14 Replies
View Related
Oct 23, 2014
I am building an access database for my college project and I essentially have a quotation form that when I click a button 'Convert to Invoice' it creates a new record in the invoice table and then creates new records in the invoice details table which match the quotation details table. This is working as it should but for only the first 2 customers in my customer table?
On the quote form I have a combo box which is linked to the customer table and updates the quote table based on the selection. If I select customer 1 or 2 and click 'convert to invoice' it works and opens an invoice form based on the inserted data however if I select any other customer it returns an error that the record wasn't added to the table due to key violations?
As far as I can tell I am not trying to update the primary keys in the Invoice Table or the Invoice Details Tables.
View 1 Replies
View Related
Jun 4, 2013
I'm trying to duplicate the records in a subform to a new form but keep getting a too few parameters error.
Code:
strSql = "INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) " & _
"SELECT " & lngID & " As NewOrderID, ProductID, Quantity, DiscountPercentage " & _
"FROM [OrderDetailT] WHERE OrderNumber = " & Me.OrderNumber & ";"
The debug.print comes out as below:
INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) SELECT 49 As NewOrderID, ProductID, Quantity, DiscountPercentage FROM [OrderDetailT] WHERE OrderT!OrderNumber = 11;
View 4 Replies
View Related
Jul 10, 2014
I am trying to update and insert records with SQL statements. Below my code:
Select Case FirstGRV
Case "Y"
sql = "UPDATE Tbl_SellingPrices " & _
"SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = date(), SellingPriceDateTo = #" & Me!Text3 & "#" & _
"WHERE SellingPriceStockCode = Forms!Frm_GRV!GRVStockCode and SellingPriceDateFrom =#01-01-1900#;"
[Code] ....
My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.
View 14 Replies
View Related
