INSERT Query - Insert New Data Only

Jul 2, 2010

Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.

TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }

I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.

INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'

[code]...

This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.

View Replies


ADVERTISEMENT

Tables :: Use Insert Into Command To Insert Filepath Into Table That Adds Blank Label

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

Using RecordSet To Insert New Data Into A Query Based On Two Forms

Feb 25, 2007

This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.

I firstly created a query that selected the nessary fields I wish to update from the two tables.

And its apparent that I need to use RecordSet to insert the information from the form into the query.

I have found a few different ways of doing this - none of which work for me :( HELP!

Method 1
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest

Dim db As DAO.Database
Dim rs As DAO.Recordset


messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")

Me.txtserver = rs!Server
Me.cmblocation = rs!Location
Me.cmbtype = rs!BackupType
Me.cmbtype = rs!Type
Me.cmbsize = rs!Size(GB)
Me.cmbassign = rs!Group
Me.txtrequiredby = rs!Date_required_by
Me.txtrequestedby = rs!Requested_by
Me.txtrequest = rs!Date_Time_Request
Me.txtnotes = rs!Notes

'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""

'Close recordset and database
rs.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If

End Sub

[B]Method 2
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest

Dim db As DAO.Database
Dim sqlStatement As String
Dim saverecord As DAO.Recordset

messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then

sqlStatement = "SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;"

Set db = CurrentDb()
Set saverecord = db.OpenRecordset(sqlStatement)


saverecord.AddNew
saverecord(0) = txtserver
saverecord(1) = cmblocation
saverecord(2) = cmbtype
saverecord(3) = cmbtype
saverecord(4) = cmbsize
saverecord(5) = cmbassign
saverecord(6) = txtrequiredby
saverecord(7) = txtrequestedby
saverecord(8) = txtrequest
saverecord(9) = txtnotes
saverecord.Update 'Write new record to database

'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""

'Close recordset and database
saverecord.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If

End Sub

and I have even looked into an insert sql statement
[B]Method 3
Dim SQL_Text As String
SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &"
INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;"
Docmd.RunSQL (SQL_Text, false)

Method 1 seems to be popular but its returning the message
Run time error '3061'
Too few parameters. Expected 3.
:confused:

View 14 Replies View Related

Queries :: Append Query To Insert Data From Excel

Feb 5, 2014

I want to create a append query in access 2003 to insert data into an existing table from Excel workbook.My Table name is TokenDetail in Access 2003.

And Excel File is TokenCreation.i want to create query with msg box and requered file path for data becuase my excel files have various path and name.

View 8 Replies View Related

Get Data From Another Query In INSERT Query

Mar 29, 2007

Hi there

my current query

INSERT INTO [Transaction] ( booking_ref, transaction_type, transaction_description, transaction_amount, [date/time] )
VALUES (10, 'Booking', 'Hotel Room Booking Charge', 4.5, Now());

now instead of 4.5 for transaction amount, i want to get the data from another query. My other query gives off one row and 1 column which is called "price per night" The other query name is Booking Price per Night (it uses the same [Booking Ref] Parameter.

I hope someone can point me in the right direction

thanks in advance

View 1 Replies View Related

INSERT Data To MS Access Via SQL?

Dec 13, 2004

HI all-

I have a list of INSERT statements (SQL DML) and I wish to insert this data into an MS Access 2000 table

Unfortunately I cannot find a mechanism to accomplish this in Access. Does one exist? How can I import this data into access in a SQL format?

Thanks in advance!

View 6 Replies View Related

Field With No Data Insert 0's

Mar 27, 2006

When the IVA_MEMBER_ID field has no data I would like this query to return : 000000000,
is this possible and if so could you show me how? Thanks

SELECT DISTINCT
NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID,
NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEM_SSN,
NOLDBA_LOG_DISBURSEMENT_DETAIL.AMT_DISBURSE,
NOLDBA_LOG_DISBURSEMENT_DETAIL.CD_TYPE_DISBURSE,
NOLDBA_LOG_DISBURSEMENT_DETAIL.SEQ_ORDER,
NOLDBA_LOG_DISBURSEMENT_DETAIL.SEQ_OBLIGATION,
NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CASE INTO [A TBL]

FROM NOLDBA_LOG_DISBURSEMENT_DETAIL INNER JOIN
(NOLDBA_INT_CASE_MEMBER INNER JOIN
NOLDBA_INT_MEMBER_DEMOGRAPHIC ON
NOLDBA_INT_CASE_MEMBER.MEMBER_ID = NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID) ON NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CHECK_RECIPIENT = NOLDBA_INT_CASE_MEMBER.MEMBER_ID

WHERE (((NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEM_SSN)>"0") AND ((NOLDBA_INT_CASE_MEMBER.RELATION_CODE)="C") AND ((NOLDBA_LOG_DISBURSEMENT_DETAIL.CD_CHECK_RECIPIEN T)="1") AND ((NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE) Between [START DATE] And [ENTER END DATE]))

ORDER BY NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID;

View 3 Replies View Related

How Do I Insert Data From One Table To Another?

Mar 7, 2007

I want to set my insert such that

Insert into tableA (x,y,z) = (SELECT x,y,z FROM tableB)
WHERE NOT EXISTS (select x,y FROM tableA,tableB WHERE tableA.x = tableB.x AND tableA.y = tableB.y)

Basically I want to insert data in tableA from tableB if it does not exist in tableA yet.

Access says I'm not writing an updateable query.

View 1 Replies View Related

How To Insert Data Into External Db

Apr 28, 2005

Hi experts. I want to learn how i can insert data in to external db using vba.
For example , i want insert table names in to the external db.
(A command button in a form )I be happy if some one show me how. Thanks

View 2 Replies View Related

Can't Insert Data Into Textbox?????

Jun 15, 2005

why do i get all the weird problems?
does anyone have any idea why a textbox would act like it was locked, even though it's not?

anyway, the text box works fine, until i click my button which goes to a certain record based on my listbox. the code for the click event is as follows:

Code:Private Sub cmdFindProcess_Click()blnexist = True Dim rst As dao.Recordset Dim strSearchName As String Set rst = Me.RecordsetClone strSearchName = Str(Me!List26.Value) rst.FindFirst "ID = " & strSearchName Me.Bookmark = rst.Bookmark rst.Close If Me.List26.ItemsSelected.Count = 0 Then MsgBox "Please select your process", vbOKOnlyElseMe.formattedtimeelapsed.Visible = True Me.StartTime.Visible = True Me.EndTime.Visible = True Me.btnStartStop.Visible = True Me.btnStartStop.SetFocus Me.StartTimeLabel.Visible = True Me.EndTimeLabel.Visible = True Me.InProcess.Visible = False Me.Current.Visible = True Me.NewProcess.Visible = False Me.hidID.Value = Me.List26.Value Me.formattedtimeelapsed.Value = "00:00:00" Dim Rc As dao.RecordsetDim Db As DatabaseDim SQL As StringSQL = "SELECT * FROM StopwatchRecord Where StopwatchRecord.ID=" & Me.List26.Value & ";"Set Db = CurrentDb()Set Rc = Db.OpenRecordset(SQL)Rc.MoveFirstMe.hidID.Value = Rc.Fields("ID")TotalElapsedMilliSec = Rc.Fields("ElapsedTime")Me.ElapsedTime.Value = Rc.Fields("ElapsedTime")Me.Comments.Value = Rc.Fields("Comments")Me.StartTime.Value = Rc.Fields("StartTime") Me.txtProcessDisplay.Value = Me.List26.Column(3) Me.txtActNumberDisplay.Value = Me.List26.Column(1) Me.Comments.Enabled = False Me.txtAdditionalComments.Visible = True Me.txtAdditionalComments.Enabled = True Me.txtAdditionalComments.Locked = False End If End Sub
my guess is that the "bookmark" code is not allowing it. i'm not sure however, how i could find the record any other way, if that is the problem....any suggestions would be greatly appreciated.
thanks in advance,
*j

View 2 Replies View Related

Insert Data Into A Subform

Jul 29, 2015

I have a form with a sub form, on the main form I want the user to select a machine number and then when they have selected it I want to populate the required data into the sub form.

In the sub form I have a field called "Seal" and another called "Quantity" what I require is to get the results from either a query or table and list all the seals & quantities for that machine.

I have a table called "Machine" which has all the machine numbers and also fields called "Seal1", "Seal1Qty","Seal2, "Seal2Qty" all the way to Seal 10.

I do not know how the best way to get this data into the sub form...

View 5 Replies View Related

Queries :: Insert Into One To Many PK And FK Insert

Mar 25, 2015

I have a normalized DB with one to many relationships, using Primary and Foreign Keys.

I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.

How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.

I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details

I have 2 tables, tblName and tblPhone. 1 name can have many phones.

tblName has PKName. tblPhone has PKPhone, FKName.

I have a form where user enters a new Name and PhoneNumber.

Name gets inserted to tblName, assigned with PKName = 100

Phone should get inserted into tblPhone with PKName.

IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").

The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.

View 2 Replies View Related

Modules & VBA :: Take Data From One Table And Insert Into Another Whilst Doing Calculations On Data

May 2, 2014

I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.

However I can't get past this:

Code:
Private Sub Test_Click()
Dim strSQL As String

strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] String," & _
"[Red] String," & _
"[Amber] String," & _
"[Green] String)" & _
"FROM [035 - Meter Point HH Data];"
DoCmd.RunSQL strSQL

End Sub

It keeps saying "Run-time error '3292': Syntax error in field definition.

View 4 Replies View Related

Insert A Line Of Data In A Subform

Jul 11, 2007

Hi,
I have a form which records computer faults reported by users. On this form there is a subform for comment tracking eg. Phoned user, Date/Time. On the next line would be; Informed user to do this, Date/Time and so on. I have a button on my form which I would like to use to automatically put in a line of data. It is an aknowlegde fault button. What it hopefully would do is when you click the button it will automatically put in the date/time in the subform, Then the word "acknowledged" in the comment field, then prompt for the user name ( for the username field).
I am not sure on how to start the command. If i were to do it in the same form then I would just put something like;
me.Date.Value = Date()
me.Comment.value = "Acknowledged"
me.Username.value = "[Prompt]"

As you can see I have two problems though;
1. The data has to be entered in a subform
2. My Prompt I dont think will work.

Does anyone have any ideas?

Kind Regards
Keith

View 1 Replies View Related

Auto-Insert Data On Export?

Dec 1, 2007

when I am exporting in fixed width format (.txt file), is it possible to automatically include in the outputted text file:

a header of the current date and time, and
a footer that will say how many records are in a certain table?

edit: when i say header and footer, I mean the first and last entries in the text file

thanks
Venom

View 1 Replies View Related

Insert Data From Queries To A Table

Apr 29, 2008

Is it possible to select some data in queries in the same db to a table?

Many thanks!

View 3 Replies View Related

Insert Into Mdb From Selected Data From SQL Database

Sep 14, 2004

My application extracts data from a SQL server according to some filters and put it in a local database (MS Access), I actually do it in two steps, data extraction and loop tru the data sending it to MS Access with insert values instruction, is there a better way to do it (the routine is too slow for large databases, the string convertion make it slow I beliebe)?, I was thinking on something like "Select into", but I am not copying a database and I need to add 2 more columns to the database (add fields that you have at the moment to make the select but aren't on the database and were needed to make the selection).

Tanks

View 1 Replies View Related

Insert Data In Excell File

Jan 17, 2005

i would like to create an excell file (and choose the path) in ms access
and fill that excell file with data divided over multiple sheets

or even if possible take an excell template and fill it with data, then rename
and save it

any suggestions?

View 8 Replies View Related

General :: Insert Data Of One Table To Another?

Mar 10, 2014

Inserting data from History Table LIKE "P" Type into Preventive Maintenance Table and LIKE "R" Type into Repair Table.

I don't quiet get it because it says "PM Data Update" but nothing happened. Below is the code:

Code:
Private Sub cmdUpdate_Click()
Dim strSQL
Dim dbMNT As Database

[Code].....

View 6 Replies View Related

Forms :: Insert Data Into Another Form

Oct 3, 2013

I have a combo box Customer_Name on Order Form; and I want it open the Customer Form when an user insert a new customer to input all data. This seems simple at first, but problems are these:

1- whenever an user opens Customer Form on a new customer, the user would have to make the input once again (at least retype the customer's name on the field).

2- since the Customer_Name and other fields are required in the table, the Order_form would not allow the user to close it unless all field are filled properly.

Dilemma is you have to fill in the Customer_Name field before the table refresh all records and display it in the combo box on Order Form

View 13 Replies View Related

Table Lookup And Auto Insert Data

Nov 24, 2007

Hi All
I am new to this, hope you can help!!

I have 2 tables,
Customers and Routes
I want to auto insert data from Routes when I select the route Number from Customers,

the tables to auto update are
CruiseNumber:
CruiseName:
CruiseDate:

from
RouteNumber:
RouteName:
RouteDate:

I have read HELP in Access but I am a layman when it comes jargon. 'from the many to the few'??
Hope you can help....in 'Plain English'

View 6 Replies View Related

Insert Data In Two Related Tables Simultaneously

Dec 11, 2004

I want to insert a huge number of data (Customer and their adress) into 2 related tables in Access. Each table in access is related by some kind of ID (Autonumber-Primary key). The first table is CUSTOMER , the other one is CUSTOMER_ADRESS . Access relate each Customer to his Adress, so if I import data into one table and then import data into another table how would the database know that all the data pertains to the same person. How to insert the new list of customer and adress without using a form?
Thanks for your help.

View 6 Replies View Related

General :: Insert Data Mismatch Error

Jun 21, 2015

PHP Code:

Dim strSQL As String    'Add Absence Data to tblHour.
strSQL = "INSERT INTO tblHour (WorkDate,EmployeeID,Hours) "
strSQL = strSQL & "VALUES (#" & Me.AbsenceDteTo & "#, '" & Me.EmployeeID & "', '" & Me.txtAbsHrs & "')"
        CurrentDb.Execute strSQL, dbFailOnError

[Code] ......

View 3 Replies View Related

Reports :: How To Insert Data To Table From Report

Jun 9, 2014

I have a report, which is based on query that links various table.

There is also a form, which starts when the report opens, which has dual function: 1.) user selects criteria to filter out only particular customer but 2.) is for user to add some new data that are not available in query/linked tables.

I need to be able to create now a command button that would add all records from the report to for example 'Report Table'. My report has a tabular form but I have also lots of text-boxes in Report Header that would need to be added too.

View 4 Replies View Related

Forms :: How To Insert Data By Clicking Button

Feb 11, 2014

I am new with ms Access. I am using 2013 version and stuck with "pretty" easy task.

When I create the form, the values are inserted/updated constatly as I leave the input components. However I would like to insert/update the record only when I click the button. How to do that?

View 5 Replies View Related

Modules & VBA :: How To Insert Data Recordset Into Table

Jun 4, 2013

I`m currently having the problem to export data from an SQL server into a table. I managed to open a recordset but I`m incapable of adding the recordset to an existing table. I found similar threads but I am still not able to generate functioning code.

Code:
Function fDAOServerRecordset()
Dim db As DAO.Database
Dim dblcl As DAO.Database
Dim rssql As DAO.Recordset

[code]....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved